ORACLE 11G Modify AWR retention period is less than 8 days

Oracle Database 11g defaults to a system-defined moving Window Baseline that corresponds to all AWR data for the AWR retention period.

Only one moving Window Baseline can exist.

The system-defined moving Window baseline is the current awr retention period, which defaults to eight days.

If you want to increase the moving Window Baseline, you first need to increase the AWR retention period accordingly.

The size of the AWR retention period and system-defined moving Window baseline is two independent parameters.

However, the AWR retention period must be greater than or equal to the size of the system-defined moving Window baseline.

-----------View awr retention time, snapshot interval:

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.0

Sql> select * from Dba_hist_wr_control;

DBID Snap_interval RETENTION Topnsql

---------- --------------------------------------------------------------------------- -------------------------- ------------------------------------------------- ----------

4215476630 +00000 01:00:00.0 +00030 00:00:00.0 DEFAULT

Sql> select dbms_stats.get_stats_history_retention from dual;

Get_stats_history_retention

---------------------------

31

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

---------directly modify AWR retention period of 7 days

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 1

10080=7*24*60*60

691200=8*24*60*60 8 Days

------------View the value of the current 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

Now that you know why, the solution is very convenient, modifying the window Size of the Default moving window Baseline

BEGIN

Dbms_workload_repository. Modify_baseline_window_size (

Window_size => 5,

dbid => 4215476630);

End;

/

Note that window size must be set to a reserved value less than or equal to the AWR setting.

You can then modify the retention time for the AWR snapshot:

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