Change AWR snapshot Retention period and Interval in Oracle: In this article we will discuss about how to Change AWR snapshot Retention period and Interval in Oracle.
About AWR Report:
Oracle MMON background process collect the AWR statistics and generate the snapshot based on retention period, which violates the threshold metric. You can enable this job by changing the parameter STATISTICS_LEVEL value to TYPICAL, then snapshots will be taken automatically. Also, You can disable this job by changing the parameter STATISTICS_LEVEL value to BASIC. Then snapshots will not be taken automatically.
SQL>
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_statistics_level string TYPICAL
statistics_level string TYPICAL
SQL>
SQL> show parameter control_management_pack_access
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
How to modify Snapshot settings in AWR:
We can change the snap_interval value and retention period value for the automatic awr snapshot collection, using modify_snapshot_settings function.
Default Snapshot Settings:
The default settings value for ‘interval’ and ‘retention’ are 60 minutes and 8 days.
SQL>
SQL> set lines 400 pages 400
SQL> col snap_interval for a50
SQL> col retention for a50
SQL> select snap_interval, retention from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
-------------------------------------------------- --------------------------------------------------
+00000 01:00:00.0 +00008 00:00:00.0
SQL>
Modify the Snapshot Setting:
Change the snap_interval value to 30 min and retention value to 30 days(60*24*30).
The values for both 'interval' and 'retention' are expressed in minutes.
SQL>
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 43200);
PL/SQL procedure successfully completed.
SQL>
Check and verify the new Snapshot Settings:
SQL>
SQL> select snap_interval, retention from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
-------------------------------------------------- --------------------------------------------------
+00000 00:30:00.0 +00030 00:00:00.0
SQL>
How to install, configure, and use Statspack in Oracle.