How to Modify AWR Snapshot Interval Setting

How to Modify AWR Snapshot Interval Setting

We can change the snap_interval and retention period for the automatic awr snapshot collection, using modify_snapshot_settings function.

The default settings for ‘interval’ and ‘retention’ are 60 minutes and 8 days .

DEFAULT SETTING:

select snap_interval, retention from dba_hist_wr_control;

 SNAP_INTERVAL                                                               RETENTION

--------------------------------------------------------------------------- --------------------

+00000 01:00:00.0                                                           +00008 00:00:00.0

Modify the snapshot setting:( snap_interval 30 min and retention 30 days(60*24*30)

The values for both ‘interval’ and ‘retention’ are expressed in minutes.

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 43200);

PL/SQL procedure successfully completed.

Verify the new setting:

SQL> select snap_interval, retention from dba_hist_wr_control;

 SNAP_INTERVAL                                                               RETENTION

--------------------------------------------------------------------------- -----------------------------------------

+00000 00:30:00.0                                                           +00030 00:00:00.0