Modifying AWR Automatic Snapshot Settings

Sorry i’ve not posted for a while, my only excuse is that we have moved house so had no internet for two weeks. Anyway, back to business with another quick one which is again based on the fairly hefty subject of the Automatic Workload Repository, also known as AWR.

There are a number of AWR views (all starting DBA_HIST_) which I will go into in other posts but the one we are interested in to display the AWR settings is DBA_HIST_WR_CONTROL. This view only has 4 columns which are:

SQL> desc dba_hist_wr_control
 Name                            Null?    Type
 ------------------------------- -------- ----------------------------
 DBID                            NOT NULL NUMBER
 SNAP_INTERVAL                   NOT NULL INTERVAL DAY(5) TO SECOND(1)
 RETENTION                       NOT NULL INTERVAL DAY(5) TO SECOND(1)
 TOPNSQL                                  VARCHAR2(10)

If we select from it we can see the defaults

SQL> set linesize 100
SQL> col snap_interval format a20
SQL> col retention format a20
SQL> col topnsql format a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- --------------------
3566081556 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT

So, the defaults are automatic snapshots are taken every 1 hour and snapshots are kept for 7 days.

So, what if we want to change these? Oracle have supplied a package DBMS_WORKLOAD_REPOSITORY which is used to take manual snapshots, create baselines and the important one for us, change snapshot settings. So, for example, if we wanted to change the settings so we took snapshots every 10 minutes and kept these snapshots for 10 days we would issue the following sql:

 execute dbms_workload_repository.modify_snapshot_settings
        ( interval => 10,
          retention => 14400);

All the values are in minutes, so for the retention 14400 is 10 days. So, if we now look at dba_hist_wr_control we can see our change has been reflected

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- --------------------
3566081556 +00000 00:10:00.0    +00010 00:00:00.0    DEFAULT

It is always worth reviewing the size of your SYSAUX tablespace before you make any changes. Oracle have provided a handy script for this, which is called utlsyxsz.sql (held in $ORACLE_HOME/rdbms/admin) which will estimate the required size for the SYSAUX tablespace based on the retention period, snapshot interval you are going to use and a few other variables like number of active sessions and number of tables.