The AWR report is used in performance optimization to assess how well the database performs between intervals.
You must define the time intervals for capturing AWR pictures in order to examine the problem at certain intervals.
The default interval is one hour. You may update it or capture a screenshot manually.
Retention is defined as the length of time that the Oracle Database keeps the AWR snapshot for future reference in order to verify the performance at a previous time. You may also compare AWR data to previous times, which can aid in performance optimization.
The default retention period is 8 days.
Table of Contents
To Check AWR Retention Period in Oracle Database, Use Following Command:
SQL> SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;
This means AWR snapshots are taken every hour and they are retained in the database for 08 days.
I propose a database interval of 30 minutes and a retention period of more than 60 days to evaluate the database to its previous condition.
Because whenever there is a performance issue in an Oracle database, you ought to compare the performance of TOP SQLs with the previous day or month.
Maintain AWR snapshots for more than two or three months if you already have enough disc space.
How to Modify Retention Period and Interval in AWR Report:
Let’s Modify retention period to 10 days and interval to 30 min.
NOTE: Both retention and interval utilise a number in minutes.
10 Days = 10*24*60 = 14400 minutes
Check the new retention and interval time.
Snapshots are normally made automatically; however, you may make them manually if necessary, using the following script.
You may manually drop snaps as shown below.
Learn more:
? Oracle 11g RMAN Duplicate Database Without Target Connection – Click here to know about it
