Performance Tuning

Automatic Workload Repository (AWR) reports

User Rating:

/ 0

Details: Parent Category: Articles; Created on Thursday, 18 March 2010 21:55; Last Updated on Monday, 12 March 2012 13:54; Published on Thursday, 18 March 2010 21:55; Written by Guy Lambregts; Hits: 4688

AWR Reports

1. Who of you is sometimes to lazy to install statspack ?

2. Who of you would like a modern built in statspack ? with additional features ? with additional statistics ? and additional metrics monitored ?, with automatic snapshotting pre configured ? and easy to use ?

3. Who of you is willing to pay an additional licence fee for the Database Diagnostics Pack for this modern built in statspack ?

Still there ?

There is good news assuming you are on 10G and assuming you were also to lazy to modify the initialization parameter statistics_level. Since 10G R1 there is a built in "super statspack". (statspack is still available). In stead of statspack reports we speak about Automatic Workload Reports - AWR - reports , in stead of statspack snapshots we speak about AWR snapshots. By default AWR s snapshot' s are taken every hour. Statistical information has been gathered and written to disk, towards the new sysaux tablespace by the new backgroundprocess MMON. ( MMON = Manageability Monitor ).

Please note you can also use

1. ADDM Reports . analysis of the statistical data in the AWR repository with recommendations

2. ASH Reports. The active session history is useful for analysis of recent database performance events.

We can still take a snapshot manually

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

As well we can change the retention period and the snapshot interval. In the example below we change the retention period to 5 days ( 5 x 24 x 60 ) and the snapshot interval to 30 minutes.

SQL> exec dbms_workload_repository.modify_snapshot_settings (retention => 7200,interval => 30);

PL/SQL procedure successfully completed.

So we should not install anything anymore neither we should schedule jobs. I will show you what there is left to do in order to get the reports.

cd $ORACLE_HOME/rdbms/admin

export ORACLE_SID=MYDB1

sqlplus system/secret_password

SQL> start awrrpt.sql;

Current Instance
Enter value for report_type;"> html

Type Specified:

Instances in this Workload Repository schema
Enter value for num_days;"> 1

Listing the last day's Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level

MYDB1 MY_DB 6380 10 Oct 2005 00:00 1
6381 10 Oct 2005 01:00 1
6382 10 Oct 2005 02:00 1
6383 10 Oct 2005 03:01 1
6384 10 Oct 2005 04:00 1
6385 10 Oct 2005 05:00 1
6386 10 Oct 2005 06:00 1
6387 10 Oct 2005 07:00 1
6388 10 Oct 2005 08:00 1
6389 10 Oct 2005 09:00 1
6390 10 Oct 2005 10:00 1
6391 10 Oct 2005 11:00 1
6392 10 Oct 2005 12:00 1
6393 10 Oct 2005 13:00 1
6394 10 Oct 2005 14:00 1
6395 10 Oct 2005 15:00 1
6396 10 Oct 2005 16:00 1

Specify the Begin and End Snapshot Ids
Enter value for begin_snap;"> 6394
Begin Snapshot Id specified: 6394

Enter value for end_snap: 6396
End Snapshot Id specified: 6396

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_6394_6396.html. To use this name,
press to continue, otherwise enter an alternative.