AWR

Basically, AWR (Automatic Workload Repository) is an Oracle built-in tool that collects performance related statistics and derives performance metrics from them to track a potential problem. Unlike Statspack, snapshots are collected automatically every hour by a new background process called MMON and its slave processes. Be default, the collected data is automatically purged after 7 days. Both the snapshot frequency and retention time can be modified by the user. To see the present settings, you could use:

set lines 200
col SNAP_INTERVAL format a30;
col RETENTION format a30;
select * from dba_hist_wr_control;

SNAP_INTERVAL RETENTION
------------------------------ ------------------------------
+00000 01:00:00.0 +00007 00:00:00.0
+00000 01:00:00.0 +00007 00:00:00.0

1. To change snapshots’ interval & retention parameter setting:

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 60); -- Minutes (= 1 hour). Current value retained if NULL.
END;
/

2. To check Snapshot ID information can be queried from the DBA_HIST_SNAPSHOT view:

select * from DBA_HIST_SNAPSHOT order by BEGIN_INTERVAL_TIME;

3. Existing snapshots can be removed by:

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 6491,
high_snap_id => 6499);
END;
/

4. Extra snapshots can be taken:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

5. Workload Repository Reports

Oracle provide 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:

@$ORACLE_HOME/rdbms/admin/awrrpt.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 opend in a browser or text editor accordingly.