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.
Post a Comment
Post a Comment