从10g开始，MMON进程每隔一小时就会收集一次AWR数据。下面将介绍如何修改AWR收集频率及保留时间。默认情况下10g保留7天，11g保留8天。通过DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS来修改。http://www.cndba.cn/Expect-le/article/1961

1  实验

1.1   查看当前参数值

可以看到，保留8天，收集频率为1小时http://www.cndba.cn/Expect-le/article/1961

http://www.cndba.cn/Expect-le/article/1961

SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL> select
snap_interval,
retention
from
dba_hist_wr_control;  2    3    4    5  
 
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
+00000 01:00:00.0
+00008 00:00:00.0

http://www.cndba.cn/Expect-le/article/1961

1.2   修改参数值

修改为2小时和保留14天http://www.cndba.cn/Expect-le/article/1961

http://www.cndba.cn/Expect-le/article/1961

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 120,retention => 20160);
PL/SQL procedure successfully completed.

1.3   再次查看结果

SQL> select
snap_interval,
retention
from
dba_hist_wr_control;  2    3    4    5  
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
+00000 02:00:00.0
+00014 00:00:00.0

注意：如果interval 设置为0，那么快照的收集间隔是110年。

http://www.cndba.cn/Expect-le/article/1961http://www.cndba.cn/Expect-le/article/1961

设置方式是execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 0);，仅供参考，别执行。http://www.cndba.cn/Expect-le/article/1961

版权声明：本文为博主原创文章，未经博主允许不得转载。

AWR