ORACLE 11G 怎样改动 awr 的保留期限小于8天
Oracle Database 11g 默认具备一个系统定义的Moving Window Baseline,该基线相应于 AWR 保留期中的全部 AWR 数据。
仅可存在一个Moving Window Baseline。 系统定义的Moving Window Baseline认大小为当前的AWR保留期,即默觉得八天。假设要增大Moving Window Baseline,首先须要对应增大AWR保留期。
AWR保留期和系统定义的Moving Window Baseline的大小是两个独立的參数。 可是AWR保留期必须大于或等于系统定义的Moving Window Baseline的大小。-----------查看awr 的保留时间,快照间隔:
SQL> select DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION from wrm$_wr_control;
DBID SNAP_INTERVAL SNAPINT_NUM RETENTION
---------- -------------------------------------------- ----------- 4215476630 +00000 01:00:00.0 3600 +00030 00:00:00.0SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------- 4215476630 +00000 01:00:00.0 +00030 00:00:00.0 DEFAULTSQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
--------------------------- 31---------直接改动awr 保留期限为7天
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>60, retention=>10080);
BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>60, retention=>10080); END;*
ERROR at line 1: ORA-13541: system moving window baseline size (691200) greater than retention (604800) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222 ORA-06512: at line 110080=7*24*60*60
691200=8*24*60*60 8天 ------------查看当前Moving Window Baseline的值 SQL> col BASELINE_NAME for a30 SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline; DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE ---------- ------------------------------ ------------- ------------------ 4215476630 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8既然知道了原因,解决就非常方便了,Modifying the Window Size of the Default Moving Window Baseline
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE ( window_size => 5, dbid => 4215476630); END; /当中要注意:window size必须设置为一个小于或等于AWR设置的保留值。
这时就能够改动awr 快照的保留时间了:
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>60, retention=>10080);
PL/SQL procedure successfully completed.
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------- 4215476630 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT