AWR/ ASH Reports

Automatic Workload Repository (AWR) 

In 10g Oracle has introduced new statistical report of database called the Automatic Workload Repository (AWR) report. Its just the higher version of the Statspack report.

The AWR takes a snap shot of the database in specified intervals (default is one hour) and stores in Sysaux tablespace.

The snap shots are taken automatically if the statistics_level parameter is set to typical/all. If it set to basic then statistics details are not gathered.

The AWR contains the performance statistics and workload information on the database. The information is gatherd every one hour and preserved for seven days by default. At any time we can change the Relation and Interval time. 

To enable AWR set statistic_level = typical / all .
To disable AWR set statistic_level - basic;

Select * from V$statistics_level;

The statistics are collected and stored in memory in SGA. The in memory statistics collection area is a circular buffer, where the old data is overwritten after flushing to disk.

The AWR statistics snap shot are owned by Sys schema. Than MMAN (Manageability monitor) process is responsible for filtering and transferring the memory statistics to the disk every hour. When the buffer is full, the MMNL (Manageability Monitory Light) process is responsible to flush the information to the repository.

Active Session History (ASH)

The ASH contains recent information on active sessions sampled every second. The AWR are taken every one hour and its one hour old will not help in diagnosing issues that are current on the database. Typically to resolve issues urgenly on the database, details information pertaining o last 5 to 10 mins is critical. Because recording session activity is expensive, ASH samples V$SESSION every second and records the event for which he session are waiting.

You can query ASH information through V$active_session_history. The view contains one row for each active session per sample and returns the latest session's sample rows first. It include 

* sql identifier of sql statement.
* object no., file no., and block no.
* wait event identifier & parameters.
* user identifier, Session identifier and Serial number.
* client identifier and name of the operating system program.

For eg. To diagnoise the performance problem of sid 12.

Query

select session_state, event, current_obj# from V$active_session_history where session_id = 12;

Generating AWR Reports.

a) To view the Sanp shots number and it time.

>selec * from dba_hist_snapshot;

b) To generate AWR report using sql script.

>@?/rdbms/admin/awrrpt.sql;

c) To view the Current Setting.

SQL> select * from dba_hist_wr_control;

DB Id SNAP_INTERVAL RETENTION TOPNSQL
----------- ----------------- ----------------- --------
2728202513 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT

Here the snap_interval shows that it has been set to take the snap for every one hour and Retention period has been set to 7 days.

To Change the Setting.

SQL> begin
2 dbms_workload_repository.modify_snapshot_settings
3 (
4 RETENTION => 7200,
5 INTERVAL => 30
6 );
7 end;
8 /

SQL> select * from dba_hist_wr_control;

DB Id SNAP_INTERVAL RETENTION TOPNSQL
----------- ----------------- ------------------ ----------
2728202513 +00000 00:30:00.0 +00005 00:00:00.0 DEFAULT

Now the Interval has been changed to 30 minutes and the Retention period has been changed from 7 days to 5 days (7200 is the No. of minutes).

To Create a Snap Manually.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

To Drop snapshots.

SQL> exec dbms_workload_repository.DROP_SNAPSHOT_RANGE (31,38);

PL/SQL procedure successfully completed.

To Generate AWR reports using dbms_workload_repository package.

SQL> select snap_id , dbid from dba_hist_snapshot order by snap_id;

SNAP_ID DB Id
---------- -----------
21 2728202513
22 2728202513
23 2728202513
24 2728202513
25 2728202513
26 2728202513
27 2728202513
28 2728202513
29 2728202513
30 2728202513

HTML Report

SQL> spool awrreport.html;

SQL> select output from table
(
dbms_workload_repository.AWR_REPORT_HTML
(2728202513,1,21,30)
);

SQL> spool off;

AWR Text file Report

SQL> spool awrtext.txt

SQL> select ouput from table
(
dbms_workload_repository.AWR_REPORT_TEXT
(2728202513,1,21,30)
);

SQL> spool off;

Generating ASH Report.

Usning Sql script file.

>@?/rdbms/admin/ashrpt.sql

Using Package.


> select output from table 
(
dbms_workload_repository.ash_report_html
(
2728202513,1,to_date('20-11-YYYY 10:00:00','DD-MM-YYYY HH24:MI:SS'),
to_date('20-11-YYYY 10:10:00','DD-MM-YYYY HH24:MI:SS')
);

> select output from table 
(
dbms_workload_repository.ash_report_text
(
2728202513,1,to_date('20-11-YYYY 10:00:00','DD-MM-YYYY HH24:MI:SS'),
to_date('20-11-YYYY 10:10:00','DD-MM-YYYY HH24:MI:SS')
);


====================================================================================