Modify AWR interval and retention periods in 12C

=> Run the below query to identify default retention period and snapshot interval. By default snapshots are created every 1 hour with a retention period of 8 days

Note : Run below statements by connecting to root container as SYS user incase of Multi-Tenant Database

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>


SQL> col user for a10
SQL> select name,user from v$database;

NAME      USER
--------- ----------
DB1010    SYS

SQL>

col SNAP_INTERVAL for a20
col retention for a20
select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL        RETENTION
-------------------- --------------------
+00000 01:00:00.0    +00008 00:00:00.0

=> Use the MODIFY_SNAPSHOT_SETTINGS procedure of the DBMS_WORKLOAD_REPOSITORY package to modify the retention period and interval settings.

It is a good idea to modify the default settings for your database, as 8 days of retention is not enough when performing database tuning activities on your database. It is a good idea to store a minimum of 60 days of snapshots.

Here I am changing the interval to 30 minutes and retention to 60 days, both the parameters are passed in minutes.

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 86400);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL        RETENTION
-------------------- --------------------
+00000 00:30:00.0    +00060 00:00:00.0

SQL>

=> To store snapshots for an unlimited amount of time, you can specify a zero value for retention parameter, Oracle keeps the snapshot information for 40150 days or 110 years

SQL> execute dbms_workload_repository.modify_snapshot_settings(retention => 0);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL        RETENTION
-------------------- --------------------
+00000 00:30:00.0    +40150 00:00:00.0

=> To change back to default values.

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 60,retention => 11520);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL        RETENTION
-------------------- --------------------
+00000 01:00:00.0    +00008 00:00:00.0