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:

 SQL>@ORACLE_HOME/rdbms/admin/awrrpt.sql

SQL>@ORACLE_HOME/rdbms/admin/awrrpti.sql

 

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.

 

SQL> ALTER SYSTEM SET STATISTICS_LEVEL='TYPICAL' SCOPE=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

 

                                                        Snap

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

ORDER BY 1;

 

Forcely Snapshot can be taken:

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

 

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;

 

Or

 

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

SQL> EXECUTE

           DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(1,4);

Comments