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

AWR Comparison Report

The awrddrpt.sql SQL script generates a report that compares performance attributes and configuration settings between two selected time periods on the local database instance. 

The report compares top wait events, SQL performance, parameter changes etc between two snapshot intervals.

Sample Test Case : Change SGA of PDB from 6 GB to 3 GB and verify it in AWR comparison report

=> For the purpose of testing, I am changing the snapshot interval to 15 minutes

SQL> col SNAP_INTERVAL for a20
SQL> col retention for a20
SQL> select snap_interval, retention from dba_hist_wr_control;

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

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 15,retention => 11520);

PL/SQL procedure successfully completed.

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL        RETENTION
-------------------- --------------------
+00000 00:15:00.0    +00008 00:00:00.0
SQL>

=> Change SGA of PDB

SQL> alter session set container=SITPDB;

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 11G
sga_min_size                         big integer 0
sga_target                           big integer 6G
unified_audit_sga_queue_size         integer     1048576

SQL> alter system set sga_target=3g;
System altered.

SQL>

=> Wait till snapshot generation and run awr comparison report 

SQL> alter session set container=SITPDB;

SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql

Review the init.ora parameters section in the report, to verify the sga change 

=> Useful scripts:

awrddrpi.sql => for specific instance

awrgdrpt.sql => for all instances in RAC database

awrgdrpi.sql => for a set of instances in RAC database