Setting AWR Snapshots: 30-Minute Intervals,30-Day Retention

Step 1: Get the size of current moving window size.

SQL> select moving_window_size from dba_hist_baseline where baseline_type='MOVING_WINDOW';

MOVING_WINDOW_SIZE

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

10

Step 2: Get current interval and retention interval

SQL> select extract(day from snap_interval)*24*60+ extract(hour from snap_interval)*60+ extract(minute from snap_interval)"Snapshot Interval",extract(day from retention)*24*60+extract(hour from retention)*60+extract(minute from retention)"Retention Interval" from dba_hist_wr_control;

Snapshot Interval Retention Interval

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

       30        14400

Step 3: Execute below command to change moving window size.

SQL> exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);

PL/SQL procedure successfully completed.

Note: If you tried to modify retention period directly without changing above window settings then you will receive below error message.

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval =>30,retention=>10080);

BEGIN dbms_workload_repository.modify_snapshot_settings(interval =>30,retention=>10080); END;

*

ERROR at line 1:

ORA-13541: system moving window baseline size (864000) 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

Step 4: Execute below command to change snapshot and retention interval.

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>10080);

PL/SQL procedure successfully completed.

Step 5: Check the current moving window size and retention after the implementation.

SQL> select moving_window_size from dba_hist_baseline where baseline_type='MOVING_WINDOW';

MOVING_WINDOW_SIZE

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

7

SQL> select extract(day from snap_interval)*24*60+ extract(hour from snap_interval)*60+ extract(minute from snap_interval)"Snapshot Interval",extract(day from retention)*24*60+extract(hour from retention)*60+extract(minute from retention)"Retention Interval" from dba_hist_wr_control;

Snapshot Interval Retention Interval

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

       30        10080

Thanks for reading this post ! Please comment if you like this post !