We can change the snap_interval and retention period for the automatic awr snapshot collection, using modify_snapshot_settings function.
The default settings for ‘interval’ and ‘retention’ are 60 minutes and 8 days .
The dba_hist_wr_control table shows the AWR snapshot settings, namely the snapshot interval, the retention period and the top-x-sql collection threshold:
SQL> desc dba_hist_wr_control
Name Null? Type
------------------------------- -------- ----------------------------
DBID NOT NULL NUMBER
SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
RETENTION NOT NULL INTERVAL DAY(5) TO SECOND(1)
TOPNSQL VARCHAR2(10)
select snap_interval, retention from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
+00000 01:00:00.0 +00008 00:00:00.0
AWR Snapshot Reports
Oracle provides reports that you can run to analyze the data in the AWR.
There are two reports: awrrpt.sql and awrrpti.sql, which are available in the directory $ORACLE_HOME/rdbms/admin.
Modify the snapshot setting:( snap_interval 30 min and retention 30 days(60*24*30)
