How Does Oracle 11g modify AWR retention period less than 8 days?

How Does Oracle 11g modify AWR retention period less than 8 days?

Oracle Database 11g has a system-defined Moving Window Baseline by default. This Baseline corresponds to all AWR data during the AWR retention period.
Only one Moving Window Baseline exists.
The system defines the Moving Window Baseline size as the current AWR retention period, which is eight days by default.
 
To increase the Moving Window Baseline, you must first increase the AWR retention period accordingly.
The AWR retention period and the size of the system-defined Moving Window Baseline are two independent parameters.
However, the AWR retention period must be greater than or equal to the System-defined Moving Window Baseline.

Generate and view Oracle AWR reports

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Steps for generating an Oracle AWR report

----------- View the retention time of awr, 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

--------- Change awr retention period to 7 days
 
SQL> exec dbms_workload_repository.modify_snapshot_settings (interval = & gt; 60, retention = & gt; 10080 );
BEGIN dbms_workload_repository.modify_snapshot_settings (interval = & gt; 60, retention = & gt; 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 the reason, it is very convenient to solve the problem. 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: The window size must be set to a reserved value smaller than or equal to that set by AWR.
 
Now you can modify the retention time of the awr snapshot:

SQL> exec dbms_workload_repository.modify_snapshot_settings (interval = & gt; 60, retention = & gt; 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