Modifying AWR Statistics Interval and Retention Periods

Modifying AWR Statistics Interval and Retention Periods

By using the DBMS_WORKLOAD_REPOSITORY PL/SQL package, you can modify the default snapshot settings for your database.

However, before that first validate your current retention and interval settings

SQL> column awr_snapshot_retention_period format a30

SQL> SELECT EXTRACT(day from retention) || ':' ||
  2         EXTRACT(hour from retention) || ':' ||
  3         EXTRACT (minute from retention)  awr_snapshot_retention_period,
  4         EXTRACT (day from snap_interval) *24*60+
  5         EXTRACT (hour from snap_interval) *60+
  6         EXTRACT (minute from snap_interval) awr_snapshot_interval
  7  FROM dba_hist_wr_control;

AWR_SNAPSHOT_RETENTION_PERIOD       AWR_SNAPSHOT_INTERVAL
----------------------------------- ---------------------
8:0:0                                                                                     60

Elapsed: 00:00:00.00
SQL>

based on above result. the retention period is set to 8 days, zero hours and zero minutes and interval is  set to 60 minutes meaning the updated stats will be gathered after every one hour.

So how to modify this setting ?

To modify the retention period and interval settings, use  MODIFY_SNAPSHOT_SETTINGS procedure of the DBMS_WORKLOAD_REPOSITORY package. 

For example, To modify the retention period to 30 days instead of 8 (specified by number of minutes) and interval to 30 minutes instead of 60, do the following

SQL> begin

  2  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>43200, interval=>30);

  3  end;

  4  /

PL/SQL procedure successfully completed.

to verify the changes, run the dba_hist_wr_control sql script again as show below

SQL> SELECT EXTRACT(day from retention) || ':' ||

  2         EXTRACT(hour from retention) || ':' ||

  3         EXTRACT (minute from retention)  awr_snapshot_retention_period,

  4         EXTRACT (day from snap_interval) *24*60+

  5         EXTRACT (hour from snap_interval) *60+

  6         EXTRACT (minute from snap_interval) awr_snapshot_interval

  7  FROM dba_hist_wr_control;

AWR_SNAPSHOT_RETENTION_PERIOD            AWR_SNAPSHOT_INTERVAL

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

30:0:0                                                                             30