三、实验

1.检查AWR报告生成时间间隔

SYS@OCM11G >col SNAP_INTERVAL for a30

SYS@OCM11G >col RETENTION for a30

SYS@OCM11G >set lines 120

SYS@OCM11G >select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

---------- ------------------------------ ------------------------------ ----------

3465366841 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

2.修改AWR报告生成时间间隔为10分钟（最小10分钟，最大1年）

SYS@OCM11G >exec dbms_workload_repository.modify_snapshot_settings(interval => 10);

PL/SQL procedure successfully completed.

3.检查修改后的配置参数

SYS@OCM11G >select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

---------- ------------------------------ ------------------------------ ----------

3465366841 +00000 00:10:00.0 +00008 00:00:00.0 DEFAULT

4.等待10分钟后，验证生成新的快照文件

SYS@OCM11G >select SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from DBA_HIST_SNAPSHOT order by BEGIN_INTERVAL_TIME;

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME

---------- ---------------------------------------- ------------------------------

152 23-MAY-16 06.00.58.515 PM 26-MAY-16 04.00.10.358 PM

153 26-MAY-16 04.00.10.358 PM 26-MAY-16 05.00.12.350 PM

154 26-MAY-16 05.00.12.350 PM 26-MAY-16 06.00.14.315 PM

155 26-MAY-16 06.00.14.315 PM 01-JUN-16 11.30.27.958 AM

156 01-JUN-16 11.30.27.958 AM 01-JUN-16 04.23.33.145 PM

157 01-JUN-16 04.23.33.145 PM 01-JUN-16 05.00.34.463 PM

158 01-JUN-16 05.00.34.463 PM 01-JUN-16 06.00.36.501 PM

159 01-JUN-16 06.00.36.501 PM 01-JUN-16 06.10.36.899 PM

160 01-JUN-16 06.10.36.899 PM 01-JUN-16 06.20.37.172 PM

9 rows selected.

四、总结

此次用到dbms_workload_repository包中的modify_snapshot_settings存储过程，注意在修改成较短时间后，如果不需要的话，建议改回默认的1小时（60分钟），以免生成过多的快照，占用空间

ITPub博客

awr报告间隔时间配置