Thursday, February 22, 2007

AWR in a nuttshell

The AWR automatically creates snapshots of the database at certain moments in time and keep these in the AWR repository for a certain retention-period. To view the current settings of AWR, the following query should be executed:

SQL> Select * from DBA_HIST_WR_CONTROL;

DBID SNAP_INTERVAL RETENTION
---------- --------------------------- ---------------------------------------
652048328 +00000 00:15:00.0 +00007 00:00:00.0


This means that a snapshot is taken every 15 minutes and will be kept for 7 days. To change these settings, the procedure DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings can be used:

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 10080, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 15); -- Minutes. Current value retained if NULL.
END;
/


To view the available snapshots in the AWR repository:

select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME from dba_hist_snapshot;

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- -------------------------------------- ------------------------------
3558 18-FEB-07 06.00.18.000 AM 18-FEB-07 06.10.35.925 AM
3590 19-FEB-07 08.00.25.204 PM 19-FEB-07 09.00.37.533 PM
3607 20-FEB-07 08.00.26.773 PM 20-FEB-07 09.00.36.841 PM
3507 15-FEB-07 06.00.16.000 AM 15-FEB-07 06.10.34.753 AM
3525 16-FEB-07 06.10.33.378 AM 16-FEB-07 07.00.44.113 AM
3526 16-FEB-07 07.00.44.113 AM 16-FEB-07 08.00.57.048 AM
3527 16-FEB-07 08.00.57.048 AM 16-FEB-07 09.00.12.244 AM
3528 16-FEB-07 09.00.12.244 AM 16-FEB-07 10.00.23.455 AM
3529 16-FEB-07 10.00.23.455 AM 16-FEB-07 11.00.42.583 AM
3530 16-FEB-07 11.00.42.583 AM 16-FEB-07 12.00.53.854 PM
3531 16-FEB-07 12.00.53.854 PM 16-FEB-07 01.01.05.171 PM


Deletion of snapshot is done automatically by setting the retention-time. To manually delete snapshot-ranges, the following command can be used:

Exec DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (low_snap_id => 3506,
high_snap_id => 3608);


To create a manually snapshot:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot

To create a report on comparison of two snapshots:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

No comments: