How To Change AWR Retention and Interval Values

dbtut

6 years ago

If Tuning and Diagnostic Packs is installed, automatic snapshots can be created with AWR and we can get information about the database performance among the related snapshots. Tuning and Diagnostic Packs require a separate license from the database.

What is interval and retention?

interval means to the interval of automatic snapshot, retention means to how long the snapshot information is stored.

– retention: If set to 0, it is stored indefinitely. In minutes. The required value for 30 days will be 30 * 24 * 60 = 64800.
– interval: If set to 0, AWR is turned off. It takes the value in minutes.

You can find the current values with the following query.

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;

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;

Change retention and interval values

You can change the existing values with the dbms_workload_repository.modify_snapshot_settings procedure as follows.

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>21600, interval=>30);

1	SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>21600, interval=>30);