AWR Snapshot Retention Period Configuration

 

This article explores the process of modifying the AWR snapshot retention period in Oracle.

Regarding AWR Reports

The Oracle MMON background process gathers AWR statistics and creates snapshots according to the specified retention period, which may exceed the threshold metric. Enabling this task involves adjusting the STATISTICS_LEVEL parameter value to TYPICAL, facilitating automatic snapshot creation. Conversely, disabling it requires setting the STATISTICS_LEVEL parameter value to BASIC, ceasing automatic snapshot generation.

 

SQL> show parameter statistics_level

NAME TYPE VALUE
———————————— ———– ——————————
client_statistics_level string TYPICAL
statistics_level string TYPICAL

SQL> show parameter control_management_pack_access

NAME TYPE VALUE
———————————— ———– ——————————
control_management_pack_access string DIAGNOSTIC+TUNING

 

Modifying Snapshot Settings in AWR

To adjust the automatic AWR snapshot collection, we utilize the modify_snapshot_settings function to alter the snap_interval and retention period values.

Default Snapshot Settings:

By default, the interval and retention values are set to 60 minutes and 8 days, respectively.

 

SQL> col snap_interval for a25

SQL> col retention for a25

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL RETENTION
———————————- ———————————————–
+00000 01:00:00.0 +00008 00:00:00.0

 

Modify the Snapshot Setting:

Change the snap_interval value to 30 min and retention value to 30 days(60*24*30).

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

PL/SQL procedure successfully completed.

 

Check and verify the new Snapshot Settings:

 

SQL> select snap_interval, retention from dba_hist_wr_control; 

SNAP_INTERVAL RETENTION
———————————— —————————————
+00000 00:30:00.0 +00030 00:00:00.0