How to Change Oracle AWR Retention Period to 14 Days using dbms_workload_repository

calendar Sep 26, 2025 / · 4 min read · Oracle Database AWR Performance Tuning Database Administration PL/SQL Workload Repository Snapshot Management Oracle DBA dbms_workload_repository dba_hist_wr_control dbms_workload_repository  ·
Share on: linkedin copy

How to Change Oracle AWR Retention Period to 14 Days using dbms_workload_repository.modify_snapshot_settings

Purpose

The Oracle code changes the Automatic Workload Repository (AWR) retention period from the default 8 days to 14 days (two weeks). This allows database administrators to store performance snapshots for a longer period, enabling better historical analysis and trend identification across extended time frames.

Query and Code Breakdown

User Query: Change the retention period to two weeks (14 days)

Code:

1exec dbms_workload_repository.modify_snapshot_settings (retention => 14*24*60)

Component Explanation

The code uses the MODIFY_SNAPSHOT_SETTINGS procedure from the DBMS_WORKLOAD_REPOSITORY package to control AWR snapshot behavior. The retention parameter specifies how long snapshots remain stored in the Workload Repository before automatic purging.

Calculation Breakdown:

  • 14 days × 24 hours per day × 60 minutes per hour = 20,160 minutes[

The retention value must be specified in minutes and can range from 1 day to 100 years. The specified value of 20,160 minutes equals exactly two weeks of data retention.

How It Works

Default Settings

Oracle Database configures AWR with default settings of 60-minute snapshot intervals and 8-day retention periods. These snapshots capture performance statistics automatically through the MMON background process. The 8-day default often proves insufficient for comprehensive performance analysis and troubleshooting.

Retention Modification Process

When the procedure executes, it updates the AWR control settings in the DBA_HIST_WR_CONTROL table. The new retention value takes effect immediately and applies to all future snapshots. Existing snapshots remain until they exceed the new retention threshold.

Storage Considerations

Increasing retention to 14 days requires more space in the SYSAUX tablespace where AWR data resides. Database administrators should monitor SYSAUX growth using the V$SYSAUX_OCCUPANTS view to ensure adequate storage capacity.

Key Points

  • The retention parameter accepts values in minutes only
  • Oracle recommends extending retention to at least one month for production databases
  • The default 8-day retention may not cover complete business cycles
  • Retention must be greater than or equal to any defined baseline window size
  • Setting retention to 0 means snapshots are kept forever
  • Changes apply to the local database instance where the procedure runs

Verification Steps

After modifying the retention period, administrators can verify the change using this query:

1SELECT snap_interval, retention 
2FROM dba_hist_wr_control;

The output displays retention in INTERVAL DAY TO SECOND format showing +00014 00:00:00.0 for 14 days. This confirms the successful modification of retention settings.

Best Practices

Retention Period Guidelines

Oracle recommends adjusting AWR retention to at least one business cycle to enable meaningful performance comparisons. For most organizations, 30 to 45 days provides optimal balance between historical data availability and storage requirements. Extended retention enables identification of seasonal patterns and long-term performance trends.

Additional Parameters

The modify_snapshot_settings procedure also controls snapshot interval and top N SQL capture settings. Database administrators commonly adjust the interval to 15 or 30 minutes for detailed performance monitoring. The complete syntax supports optional parameters including dbid for specific database targeting and tablespace_name for custom storage locations.

Performance Impact

Increasing retention has minimal impact on database performance since snapshot collection occurs automatically at defined intervals. The primary consideration involves SYSAUX tablespace capacity planning. Organizations should implement regular monitoring to prevent tablespace exhaustion.

Common Use Cases

Database administrators modify AWR retention for several scenarios including capacity planning analysis, comparing performance across quarterly periods, maintaining compliance audit trails, and troubleshooting intermittent issues that span multiple weeks. Development environments may use shorter retention periods while production systems benefit from extended retention.

To check current settings before modification:

1SELECT snap_interval, retention FROM dba_hist_wr_control;

To create manual snapshots:

1EXEC dbms_workload_repository.create_snapshot;

To modify both interval and retention simultaneously:

1EXEC dbms_workload_repository.modify_snapshot_settings(interval => 30, retention => 43200);

References

Posts in this series