Automatic Workload Repository(AWR)

AWR is a new feature in 10g which is used for monitoring analyzing the performance of the database and solutions recommendation for tuning process for DBA’s. 

AWR is automatically installed and running with 10g. The new MMON process is responsible for collecting data and populating the AWR. 

Oracle provides two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:




The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opened in a browser or text editor accordingly.

 To activate AWR it is necessary to set the stasitics_lever parameter in PFILE/SPFILE.



  • BASIC: Disable AWR.
  • TYPICAL: Activates the standard level of statistics collection.
  • ALL: Captures all the statistics.

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awrrpt.sql

Current Instance


   DB Id    DB Name      Inst Num Instance

----------- ------------ -------- ------------

  846741622 DBA20               1 dba20


Specify the Report Type


Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: html

Type Specified:  html


Instances in this Workload Repository schema



   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 846741622         1 DBA20        dba20        NAZMUL


Using  846741622 for database Id

Using          1 for instance number


Specify the number of days of snapshots to choose from


Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.


Enter value for num_days: 2


Listing the last 2 days of Completed Snapshots



Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

dba20        DBA20                1 01 Jun 2010 23:00      1

                                  2 02 Jun 2010 00:00      1


                                  3 02 Jun 2010 11:05      1

                                  4 02 Jun 2010 12:00      1


Specify the Begin and End Snapshot Ids


Enter value for begin_snap: 3

Begin Snapshot Id specified: 3


Enter value for end_snap: 4

End   Snapshot Id specified: 4


Specify the Report Name


The default report file name is awrrpt_1_3_4.html.  To use this name,

press <return> to continue, otherwise enter an alternative.


Enter value for report_name:test.html

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view:

SQL> SELECT snap_id, to_char(begin_interval_time,'dd/MON/yy hh24:mi') Begin_Interval,

to_char(end_interval_time,'dd/MON/yy hh24:mi') End_Interval

FROM dba_hist_snapshot



Forcely Snapshot can be taken:



View The Snapshot Settings:

SQL> col snap_interval format a20

SQL> col retention format a20

SQL> col topnsql format a20

SQL> select * from dba_hist_wr_control;




SQL> select

           extract( day from snap_interval) *24*60+

           extract( hour from snap_interval) *60+

           extract( minute from snap_interval ) "Snapshot Interval",

           extract( day from retention) *24*60+

           extract( hour from retention) *60+

           extract( minute from retention ) "Retention Interval"

           from dba_hist_wr_control;


Change The Snapshoot Settings:

SQL> Execute dbms_workload_repository.modify_snapshot_settings(interval => 60,retention => 43200);


Note: 43200 (minute) = 30days


Enable/Disable automatic snapshots

exec dbms_scheduler.enable('GATHER_STATS_JOB')

exec dbms_scheduler.disable('GATHER_STATS_JOB')


Delete Snapshot