oracle moving window size与 AWR retention period关系

转自： http://tomszrp.itpub.net/post/11835/494147

晚上在做11gR1的一个awrrpt报告时,顺便想调整一下AWR snapshot的保留时间,结果遇到了ORA-13541这样的错误.下面是这个问题的发生和解决过程.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production

TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL>

检查当前系统的保留时间为8天,1小时采样一次.

SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------- ---------------------- ----------
4096851118 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

我想调整为7天，每隔30分钟采样一次.执行如下脚本

SQL> begin
2 dbms_workload_repository.modify_snapshot_settings (
3 interval => 30,
4 retention => 10080,
5 topnsql => 100
6 );
7 end;
8 /
begin
*
第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 89
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 137
ORA-06512: 在 line 2

结果遇到了这个错误.查询ORA-13541的错误信息如下

ORA-13541: system moving window baseline size (string) greater than retention (string)
Cause: The system moving window baseline size must be less than the retention setting. The specified window size
or retention violate this.
Action: Check the moving window baseline size or retention.

在看看上面的个数字基线大小 (691200) 和保留时间 (604800)

基线大小691200
SQL> select 691200/60/60/24 from dual;

691200/60/60/24
---------------
8
是8天的时间

保留时间604800
SQL> select 604800/60/24/60 from dual;

604800/60/24/60
---------------
7

是7天,正是我要设置的时间

检查当前的移动窗口基线大小
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------ ------------- ------------------
4096851118 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8

正是上面的8天对应的数字(8*60*60*24=691200).

知道了问题的原因,修改起来就容易了.调用如下过程修改移动窗口baseline size为7天
SQL> exec dbms_workload_repository.modify_baseline_window_size(7);

PL/SQL 过程已成功完成。

然后继续上面的修改操作

SQL> begin
2 dbms_workload_repository.modify_snapshot_settings (
3 interval => 30,
4 retention => 7*24*60,
5 topnsql => 100
6 );
7 end;
8 /

PL/SQL 过程已成功完成。

SQL>
OK,修改成功了,再检查一下看看结果是不是正确

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------- --------------------- ----------
4096851118 +00000 00:30:00.0 +00007 00:00:00.0 100

SQL>
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ----------------------- ------------- ------------------
4096851118 SYSTEM_MOVING_WINDOW MOVING_WINDOW 7

SQL>

好了,到这里问题就解决了!

The window size must be set to a value that is equal to or less than the value of the AWR retention setting.