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分钟),以免生成过多的快照,占用空间。