Purge AWR snapshots in Oracle Database

Purge AWR snapshots in Oracle Database

Display retention period:

select dbms_stats.get_stats_history_retention from dual;

SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;

More Details via below query:

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.

Check the list of AWR Snapshots present in DB

set line 999 pages 999

col begin_interval_time for a25

col end_interval_time for a25

SELECT snap_id, begin_interval_time, end_interval_time

FROM sys.wrm$_snapshot ORDER BY snap_id;

Manually purge after x days and before

exec dbms_stats.purge_stats(SYSDATE-30);

Drop AWR snapshots in range

EXECUTE dbms_workload_repository.drop_snapshot_range(low_snap_id =>22465 , high_snap_id =>22470);

For information Generate a report of awrinfo.sql located in the $ORACLE_HOMEW/rdbms/admin folder.

If you like please follow and comment