例如改为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
网友评论