How to Change AWR Automatic Snapshot Retention Period

December 16, 2018

Oracle MMON background process collect the AWR statistics and generate the snapshot based on retention which violates the threshold metric. You can enable this job by changing the parameter STATISTICS_LEVEL to TYPICAL then snapshots will be taken automatically. You can disable this job by changing the parameter STATISTICS_LEVEL to BASIC then snapshots will not be taken automatically.

How to Gather Statistics on Large Partitioned Tables in Oracle

In this example the automatic snapshot retention period is set to 8 days (11520 minutes) and the interval between each snapshot is 60 minutes.

Steps to Modify AWR Snapshot Interval

STEP 1: Check the Database name and DBID.

SQL> select name,dbid from v$database;

NAME            DBID
--------- ----------
ORADB   3844563802

STEP 2: Check the existing snapshot_interval time

SQL> 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;

SNAPSHOT_INTERVAL RETENTION_INTERVAL
----------------- ------------------
               60              11520

STEP 3: Modify the existing snapshot interval

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>43200, interval=>15, dbid=> 3844563802);

PL/SQL procedure successfully completed.

STEP 4: Check the modified snapshot interval

SQL> 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;
  2    3
SNAPSHOT_INTERVAL RETENTION_INTERVAL
----------------- ------------------
               15              43200

In the above example we have modified the existing AWR automatic snapshot interval from 60 minutes to 15 minutes.

Must Read: