AWR - Set AWR Retention period from 1hr to 30 minutes

 Enabling and Disabling AWR Snapshots Capture

enabled when STATISTICS_LEVEL = TYPICAL or ALL

disabled when STATISTICS_LEVEL = BASIC 

Initial settings: RETENTION = 8 days, INTERVAL = 60 minutes and 31 days 

• Execute command to Check for snapshot  intervals period: 

SQL> SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;

SNAP_INTERVAL

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

RETENTION

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

+00000 01:00:00.0

+00008 00:00:00.0

+40150 00:01:00.0

+00008 00:00:00.0

• Execute statement to set AWR STATISTICS_LEVEL to  ALL to change interval time.

C:\Users\varun>sqlplus sys/system123@pdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 11 16:48:31 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show parameter STATISTICS_LEVEL

NAME                                 TYPE        VALUE

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

client_statistics_level              string      TYPICAL

statistics_level                     string      TYPICAL

SQL> ALTER SYSTEM SET STATISTICS_LEVEL=ALL SCOPE=BOTH;

SQL> col SNAP_INTERVAL format a20

SQL> col RETENTION format a20

SQL> col TOPNSQL format a10

SQL> SELECT SNAP_INTERVAL, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL;

SNAP_INTERVAL        RETENTION            TOPNSQL

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

+40150 00:01:00.0    +00008 00:00:00.0    DEFAULT

+00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

• Change snapshot interval from 1 hour to 30 minute

SQL> BEGIN

  2  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 30);

  3  END;

  4  /

PL/SQL procedure successfully completed.

• Statement to check Snapshot interval period

SQL> col SNAP_INTERVAL format a20

SQL> col RETENTION format a20

SQL> col TOPNSQL format a10

SQL> SELECT SNAP_INTERVAL, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL;

SNAP_INTERVAL        RETENTION            TOPNSQL

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

+00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

+00000 00:30:00.0    +00008 00:00:00.0    DEFAULT