How To Modify AWR Snapshot Setting ??

by 
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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)
1
execute dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 43200);
Verify the new setting:
1
2
3
4
5
SQL> select snap_interval, retention from dba_hist_wr_control;
 
SNAP_INTERVAL RETENTION
--------------------------------------------------------------------------- -----------------------------------------
+00000 00:30:00.0 +00030 00:00:00.0