Changing AWR Snapshot Retention and Interval

By default AWR snapshot interval is set to 60 minutes and retention of snapshots is set to 8 days. For better and precise investigation of problems, I recommend to use an interval of 15 minutes and retention of 35 days. If database is huge in size and operations, also RAC, your SYSAUX tablspace would certainly grow in size with these settings. I have observed size of SYSAUX tablespace for a 3 nodes RAC database, around 14G with aforementioned settings.

Following is the method to find out current settings and modify the existing settings.

SQL> set lines 200 SQL> col SNAP_INTERVAL format a20 SQL> col RETENTION format a20 SQL> select * from dba_hist_wr_control;  DBID              SNAP_INTERVAL        RETENTION            TOPNSQL ---------- -------------------- -------------------- ---------- ---------------------------- 3275747663    +00000 01:00:00.0          +00008 00:00:00.0    DEFAULT --Here interval is set to 1 hour and retention is set to 8 days. --You can also create a snapshot manually by executing following procedure SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. --Now let’s change the retention and interval settings. Interval will be set to 15 minutes and retention to 35 days (35x24x60 = 50400) SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => 50400) PL/SQL procedure successfully completed. SQL> select * from dba_hist_wr_control;  DBID              SNAP_INTERVAL        RETENTION            TOPNSQL ---------- -------------------- -------------------- ------------------------------------- 3275747663    +00000 00:15:00.0         +00035 00:00:00.0    DEFAULT