AWR : How to purge old Snapshots?

Posted By Sagar Patil


Current Settings for AWR Prameters hosted at : dba_hist_wr_control

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;

AWR Snapshot Purging Policy

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
(retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
topnsql IN NUMBER DEFAULT NULL);
retention = snapshot interval (1 hr ) X 24 X X 7 days

To retain 1 weeks data set retention to 10080
- Retain data for 21 days ( 3 weeks )
exec dbms_workload_repository.modify_snapshot_settings(retention => 30240, interval => 60, topnsql => ‘DEFAULT’);
- Retain data for 28 days (4 weeks)
exec dbms_workload_repository.modify_snapshot_settings(retention => 40320, interval => 60, topnsql => ‘DEFAULT’);

By using the MODIFY_SNAPSHOT_SETTINGS procedure, you can control:
- How much AWR information is retained, by changing the RETENTION minutes parameter.
The default is seven days (10,080 minutes); the minimum is one day.
- How often the system automatically generates snapshots, by changing the INTERVAL
- The number of Top SQL statements for which to capture performance data.

Drop AWR snapshots in range
EXEC dbms_workload_repository.drop_snapshot_range (low_snap_id=>1107, high_snap_id=>1108);

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu