Changed AWR Interval and retention in Oracle
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 AWR Interval and Retention
set line 200
col SNAP_INTERVAL for a20
col RETENTION for a20
SELECT SNAP_INTERVAL, RETENTION,con_id FROM DBA_HIST_WR_CONTROL;
SNAP_INTERVAL RETENTION -------------------- -------------------- +00000 01:00:00.0 +00008 00:00:00.0
Change the AWR Interval
Change the Interval to 30 minutes and retention to 30 days ( 30*24*60 = 43200 minutes)
exec dbms_workload_repository.modify_snapshot_settings(interval => 30, retention => 43200) ;
Verified after change
select * from dba_hist_wr_control;