Remote IT Support!! Please contact us

For Any Remote Support,Implementation/Upgrade Projects,Queries,Collaborations please mail us at support@funoracleapps.com

Purge AWR snapshots in Oracle Database

No comments

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

No comments :

Post a Comment