AWR 报告修改moving window 出错分析

背景：测试库 11G r2 +IBM AIX 6.0

在运行AWR 收集数据库的 snapshot 的频率和保留策略是出现如下错误。

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>1*24*60);

BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>1*24*60); END;

* ERROR at line 1:

ORA-13541: system moving window baseline size (172800) greater than retention(86400)

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

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

ORA-06512: at line 1

参靠红色字体发现系统的窗口基线值172800 比保留值大86400 而出错。

通过下列命令找出窗口基线值

select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;

MOVING_WINDOW   查出来是两天 2D=2*24*60*60=172800S 1D= 86400

这说明我们目前修改的保留日期，比窗口基线值还要小窗口基线值应该是保留期的最小值。

试着把保留期限设置为超过2D的去验证。

exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>3*24*60);

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>3*24*60);

PL/SQL procedure successfully completed.

执行成功。这说明本次修改是正确的。进一步佐证了上面的猜测。

------------------------下面给出moving_window的概念-----------------------------------------------

A moving window baseline corresponds to all AWR data that exists within the AWR retention period. This is useful when using adaptive thresholds because the database can use AWR data in the entire AWR retention period to compute metric threshold values.

Oracle Database automatically maintains a system-defined moving window baseline. The default window size for the system-defined moving window baseline is the current AWR retention period, which by default is 8 days. If you are planning to use adaptive thresholds, consider using a larger moving window—such as 30 days—to accurately compute threshold values. You can resize the moving window baseline by changing the number of days in the moving window to a value that is equal to or less than the number of days in the AWR retention period. Therefore, to increase the size of a moving window, you must first increase the AWR retention period accordingly.

--------------------------------------------修改moving_window-----------------------------------------------------------

exec dbms_workload_repository.modify_baseline_window_size(*);   这里是改变基线的值。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26764973/viewspace-755732/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26764973/viewspace-755732/