修改AWR报告收集频率和保留时间

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

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

1

实验

1.1

查看当前参数值

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

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

https://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

1.2

修改参数值

修改为2小时和保留14天https://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

再次查看结果

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

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年。https://www.cndba.cn/Expect-le/article/1961

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

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

AWR