Modify AWR interval and retention periods in 12C

By Gowthami | apps-dba.com | Oracle Database Administration Series

AWR (Automatic Workload Repository) is Oracle's built-in performance data collection framework. By default, AWR snapshots are taken every 60 minutes and retained for 8 days. For detailed performance analysis or compliance requirements, DBAs often need to adjust these settings. This post covers how to modify AWR snapshot interval and retention in Oracle 12c.

What You Will Learn: How to view current AWR settings, modify the snapshot interval and retention period using DBMS_WORKLOAD_REPOSITORY, and verify the changes in Oracle Database 12c.

Default AWR Settings

Parameter	Default Value	Description
Snapshot Interval	60 minutes	How often AWR takes a snapshot
Retention Period	8 days (11520 minutes)	How long snapshots are kept
Top SQL	Top 30	Number of top SQL statements captured

Check Current AWR Settings

Query the DBA_HIST_WR_CONTROL view to see current configuration:

SQL> SELECT dbid,
       snap_interval,
       retention,
       topnsql
FROM dba_hist_wr_control;

DBID        SNAP_INTERVAL      RETENTION           TOPNSQL
----------- ---------------    ----------------    -------
1234567890  +00000 01:00:00.0  +00008 00:00:00.0   DEFAULT

Modify AWR Interval and Retention

Use the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure. All time values are in minutes.

Syntax:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention  => <retention_in_minutes>,
    interval   => <interval_in_minutes>,
    topnsql    => <number_of_top_sql>,
    dbid       => <dbid>
  );
END;
/

Example 1: Change Interval to 30 Minutes, Retain 14 Days

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 20160,  -- 14 days (14 * 24 * 60)
    interval  => 30      -- 30 minutes
  );
END;
/

Example 2: Disable AWR Snapshots

Setting interval to 0 disables automatic snapshot collection:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    interval => 0
  );
END;
/

Example 3: Re-enable with Custom Settings

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 43200,  -- 30 days
    interval  => 60      -- 60 minutes (default)
  );
END;
/

Verify the Changes

SQL> SELECT snap_interval, retention
FROM dba_hist_wr_control;

SNAP_INTERVAL        RETENTION
-------------------  -------------------
+00000 00:30:00.0    +00014 00:00:00.0

AWR Retention Quick Reference

Retention Period	Minutes Value
7 days (default)	10080
14 days	20160
30 days	43200
60 days	86400
90 days	129600

Important Considerations

• Increasing retention increases SYSAUX tablespace usage—monitor space carefully
• Shorter intervals (e.g., 15 minutes) are useful during performance troubleshooting but increase storage usage
• AWR is a licensed feature—requires Oracle Diagnostics Pack
• In a CDB (12c+), AWR settings apply per PDB when PDB-level AWR is enabled

Master Oracle Exadata

This post is part of our Oracle Database Administration series. Get our comprehensive Exadata guide with AWR analysis techniques, performance tuning, and real-world case studies.

Get the Exadata PDF Guide