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
