背景：測試庫 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(*); 這裡是改變基線的值。

AWR 報告修改moving window 出錯分析

相關文章