调整AWR快照的保存时间

例如改为7天(7*24*60),每小时收集一次，默认情况下当前系统的保留时间为8天,1小时采样一次。

1、查看当前的快照保留时间和采集周期：

SQL> select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID

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

1511936494 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 0

注：

SNAP_INTERVAL=+00000 01:00:00.0 ：表示采样间隔是1小时

RETENTION=+00008 00:00:00.0 ：表示采样数据保留期限是8天

2、查看系统的当前的MOVING_WINDOW_SIZE：

SQL> select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline

DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE

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

1511936494 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8

3、尝试调整AWR的快照的收集时间60分钟一次和保留时间7天：

SQL> begin

2 dbms_workload_repository.modify_snapshot_settings(

3 interval => 60,

4 retention => 10080,

5 topnsql => 50

6 );

7 end;

8 /

begin

*

ERROR at line 1:

ORA-13541: system moving window baseline size (691200) greater than retention (604800)

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 200

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 248

ORA-06512: at line 2

4、先调整小于当前保留的时间窗口：

SQL> exec dbms_workload_repository.modify_baseline_window_size(7);

PL/SQL procedure successfully completed.

5、再次调整AWR的快照的收集时间60分钟一次和保留时间7天：

SQL> begin

2 dbms_workload_repository.modify_snapshot_settings(

3 interval => 60,

4 retention => 10080,

5 topnsql => 50

6 );

7 end;

8 /

PL/SQL procedure successfully completed.

6、确认AWR的快照的保留时间：

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID

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

1511936494 +00000 01:00:00.0 +00007 00:00:00.0 50 0