Changed AWR Interval and retention in Oracle

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;

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 )

Google photo

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

Twitter picture

You are commenting using your Twitter 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.