Change the AWR Snapshot interval and retention time in Oracle

Change the snapshot interval time and retention for AWR reports

AWR report is used in performance tuning to check the database performing in-between intervals.
You need to define the time periods for taking AWR snapshots to check the problem in particular intervals.
Default interval is 1 hours. you can change it manually or take snapshot manually also.

Retention is defines as how long the Oracle Database keep the AWR snapshot for future reference to check the performance at past time. You can also compare with AWR reports with past time which will help in performance tuning.
Default retention is 8 days.

Check the current AWR interval time and retention period

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

Modify retention period to 7 days and interval to 30 min
NOTE : Retention and interval both use value in minutes .
8 Days = 8*24*60 = 11520 minutes
7 DAYS = 7*24*60= 10080 minutes
6 DAYS = 6*24*60 = 8640 minutes

EXEC dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 10080);

Verify the Changed retention and interval time

SQL> col snap_interval for a20
SQL> col retention for a20
SQL> select snap_interval, retention from dba_hist_wr_control;

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.