How to Change Oracle AWR Snapshot Interval to 30 Minutes using dbms_workload_repository.modify_snapshot_settings

calendar Sep 25, 2025 / · 4 min read · Oracle Database AWR Performance Tuning Database Administration DBMS Workload Repository Snapshot Management Oracle Performance Database Monitoring dbms_workload_repository  ·
Share on: linkedin copy

How to Change Oracle AWR Snapshot Interval to 30 Minutes using dbms_workload_repository.modify_snapshot_settings

Purpose

The Oracle Database Automatic Workload Repository (AWR) collects and stores performance statistics to help database administrators monitor and analyze database performance over time. The code modifies the snapshot collection interval from the default 60 minutes to 30 minutes, allowing for more frequent and detailed performance data capture. This adjustment provides better visibility into database behavior and helps identify short-lived performance issues that might be missed with longer intervals.

Breakdown of the Code

Query: Change the snapshot interval to 30 minutes

1exec dbms_workload_repository.modify_snapshot_settings (interval => 30)

Component Explanation:

exec - This is the shorthand command in SQL*Plus or SQLcl to execute a PL/SQL procedure without needing to write a full BEGIN/END block.

dbms_workload_repository - This is Oracle's built-in PL/SQL package that manages all AWR functionality, including snapshot creation, modification, and deletion.

modify_snapshot_settings - This procedure controls snapshot generation settings including the interval between snapshots, retention period, and number of SQL statements captured.

interval => 30 - This parameter sets the snapshot collection frequency to 30 minutes (the value is specified in minutes). The interval must be between 10 minutes and 1 year.

How It Works

When this command executes, Oracle Database updates the AWR configuration stored in the DBA_HIST_WR_CONTROL table. The new interval setting takes effect immediately, and the database will automatically capture performance snapshots every 30 minutes instead of the default 60 minutes. Each snapshot captures system statistics, wait events, SQL performance data, and other critical metrics used for performance analysis.

Key Points

Default Settings - Oracle AWR uses a default snapshot interval of 60 minutes and retains snapshots for 8 days.

Interval Range - The snapshot interval can be set between 10 minutes and 1 year, with values specified in minutes.

Verification - After modifying settings, administrators can verify changes by querying the DBA_HIST_WR_CONTROL view to see the current SNAP_INTERVAL and RETENTION values.

Permission Requirements - Executing this procedure requires SYSDBA privileges or appropriate permissions on the DBMS_WORKLOAD_REPOSITORY package.

Immediate Effect - Changes take effect immediately without requiring database restart or session reconnection.

Insights and Best Practices

Choosing the Right Interval

Setting the snapshot interval to 30 minutes strikes a good balance between data granularity and system overhead. While more frequent snapshots provide better detail for performance analysis, they also consume more storage space and add slight performance overhead during snapshot collection.

For detailed performance investigation, Oracle experts recommend using a 15-minute interval with a 31-day retention period. However, a 30-minute interval is commonly used in production environments as it provides sufficient detail without excessive overhead. The retention period should cover at least one complete business cycle to enable meaningful historical comparisons.

Storage Considerations

Shorter snapshot intervals generate more data, which requires more storage space in the SYSAUX tablespace where AWR data is stored. Administrators should monitor tablespace usage and adjust retention periods accordingly to prevent space issues.

Complete Configuration Example

To modify both interval and retention together, the command would be:

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

This sets a 30-minute interval and 30-day retention period (30 days × 24 hours × 60 minutes = 43,200 minutes).[^1]

Integration with ADDM

The Automatic Database Diagnostic Monitor (ADDM) analyzes the data between consecutive snapshots to identify performance problems. More frequent snapshots enable ADDM to detect short-lived performance issues that might be averaged out over longer intervals.

Verification Steps

After executing the modification command, verify the new settings with this query:

1SELECT snap_interval, retention FROM dba_hist_wr_control;

The output will display the interval as +00000 00:30:00.0 for a 30-minute setting.

Additional Parameters

The modify_snapshot_settings procedure accepts several other parameters beyond interval:

retention - Specifies how long to keep snapshots in minutes (must be between 1 day and 100 years).

topnsql - Controls the number of top SQL statements captured per snapshot (can be set to DEFAULT, MAXIMUM, or a specific number between 30 and 50,000).

dbid - Specifies the database identifier if modifying settings for a specific database in a consolidated environment.

tablespace_name - Allows specification of a custom tablespace for AWR data storage instead of the default SYSAUX tablespace.

References

Posts in this series