Modifying the Statistics Interval and Retention Periods

Problem
You need to set an interval or retention period for your AWR snapshots to values other than the default.

Solution
By using the DBMS_WORKLOAD_REPOSITORY PL/SQL package, you can modify the default snapshot settings for your database. In order to first validate your current retention and interval settings for your AWR snapshots, run the following query:

SQL> column awr_snapshot_retention_period format a40
SQL> SELECT EXTRACT(day from retention) || ‘:’ ||
EXTRACT(hour from retention) || ‘:’ ||
EXTRACT (minute from retention) awr_snapshot_retention_period,
EXTRACT (day from snap_interval) *24*60+
EXTRACT (hour from snap_interval) *60+
EXTRACT (minute from snap_interval) awr_snapshot_interval
FROM dba_hist_wr_control;

AWR_SNAPSHOT_RETENTION_PERIOD AWR_SNAPSHOT_INTERVAL

---

8:13:45 60

The retention period output just shown is in day:hour:minute format. So, our current retention period is 8 days, 13 hours, and 45 minutes. The interval, or how often the AWR snapshots will be gathered, is 60 minutes in the foregoing example. To then modify the retention period and interval settings, you can use the MODIFY_SNAPSHOT_SETTINGS procedure of the DBMS_WORKLOAD_REPOSITORY package. To change these settings for your database, issue a command such as the following example, which modifies the retention period to 30 days (specified by number of minutes), and the snapshot interval at which snapshots are taken to 30 minutes. Of course, you can choose to simply set one parameter or the other, and do not have to change both settings. The following example shows both parameters simply for demonstration purposes:

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>43200, interval=>30);
PL/SQL procedure successfully completed.

You can then simply rerun the query from the DBA_HIST_WR_CONTROL data dictionary view in order to validate that your change is now in effect:

SQL> /
AWR_SNAPSHOT_RETENTION_PERIOD AWR_SNAPSHOT_INTERVAL

---

30:0:0 30

How It Works
It is generally a good idea to modify the default settings for your database, as eight days of retention is often not enough when diagnosing possible database issues or performing database tuning activities on your database. If you have been notified of a problem for a monthly process, for example, the last time frame that denoted an ordinary and successful execution of the process would no longer be available, unless snapshots were stored for the given interval. Because of this, it is a good idea to store a minimum of 45 days of snapshots, if at all possible, or even longer if storage is not an issue on your database. If you want your snapshots to be stored for an unlimited amount of time, you can specify a zero value, which tells Oracle to keep the snapshot information indefinitely (actually, for 40,150 days, or 110 years). See the following example:

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>0);
PL/SQL procedure successfully completed.

SQL> /
AWR_SNAPSHOT_RETENTION_PERIOD AWR_SNAPSHOT_INTERVAL

---

40150:0:0

The default snapshot interval of one hour is usually granular enough for most databases, as when there are more frequent or closer to real-time needs, you can use the Active Session History (ASH) information. By increasing the default snapshot interval to greater than one hour, it can actually make it more difficult to diagnose performance issues, as statistics for the increased window may make it harder to distinguish and identify performance issues for a given time period.