Modify AWR sampling interval and historical snapshot retention time

1.AWR default sampling interval and historical snapshot retention time

The default is 7 days in 10g version and 8 days by default in 11g version. The collection interval is 1 hour. This information can be obtained from the DBA_HIST_WR_CONTROL view.

Zx@ORCL > col SNAP_INTERVAL for a20zx@ORCL > col RETENTION for a20zx@ORCL > select * fromdba_hist_wr_control DBID SNAP_INTERVAL RETENTION TOPNSQL--1444351641 + 00000 01 DEFAULT 00lv 00.0 + 00008 00lv 00lv 00.0

SNAP_INTERVAL=+00000 01VOG 00VOO indicates that the sampling interval is 1 hour.

RETENTION=+00008 00.0 indicates that the retention period of sampled data is 8 days.

More references to the DBA_HIST_WR_CONTROL view can be found in the official Oracle documentation

Http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_4072.htm#REFRN23462

2. Modify the default sampling interval and historical snapshot retention time of AWR

Using MODIFY_SNAPSHOT_SETTINGS stored procedures in the DBMS_WORKLOAD_REPOSITORY package

Zx@ORCL > desc DBMS_WORKLOAD_REPOSITORY

……

PROCEDURE MODIFY_SNAPSHOT_SETTINGS Argument Name Type In/Out Default?-RETENTION NUMBER IN DEFAULT INTERVAL NUMBER IN DEFAULT TOPNSQL VARCHAR2 IN DBID NUMBER IN DEFAULT

For example, if we need to adjust the collection interval to 15 minutes and retain the sampling data for 31 days, we can make the following adjustments

Zx@ORCL > begin 2 dbms_workload_repository.modify_snapshot_settings (3 interval = > 15, 4 retention = > 31 * 24 * 60); 5 end; 6 /

PL/SQL procedure successfully completed.

Verify the modification result

Zx@ORCL > select * fromdba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL- 1444351641 + 00000 00 DEFAULT 1540 DEFAULT + 00031 00 00 DEFAULT

For more information on this stored procedure, please refer to the Oracle official documentation.

Http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_workload_repos.htm#ARPLS69140