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

1 实验

1.1 查看当前参数值

可以看到，保留8天，收集频率为1小时

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天

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年。
execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 0);