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);