How to Check Oracle AWR Snapshot Interval and Retention Settings using table dba_hist_wr_control
How to Check Oracle AWR Snapshot Interval and Retention Settings using table dba_hist_wr_control
Purpose
The Oracle Database Automatic Workload Repository (AWR) is a critical component for performance monitoring and database tuning that automatically collects and stores performance statistics at regular intervals. This SQL script allows database administrators to quickly view the current AWR snapshot interval (how often snapshots are taken) and retention period (how long snapshots are kept) by querying the DBA_HIST_WR_CONTROL view. Understanding these settings is essential for effective performance analysis, as they determine the granularity and historical depth of performance data available for troubleshooting and optimization tasks.
Query
1col snap_interval format a30
2col retention format a30
3select snap_interval
4, retention
5from dba_hist_wr_control
6/
Breakdown of Code
Column Formatting Commands
The first two lines format the output columns to ensure proper display of interval and retention values. The col snap_interval format a30 command sets the SNAP_INTERVAL column width to 30 characters, while col retention format a30 does the same for the RETENTION column, preventing data truncation in the output.
SELECT Statement
The SELECT statement retrieves two critical fields from the DBA_HIST_WR_CONTROL view: snap_interval and retention. The SNAP_INTERVAL column shows how frequently Oracle automatically takes AWR snapshots (default is 60 minutes), while the RETENTION column displays how long these snapshots are preserved before automatic purging (default is 8 days).
Source View
The query targets DBA_HIST_WR_CONTROL, which is a data dictionary view that stores control information for the Workload Repository including database ID, snapshot settings, and Top SQL configuration. This view is essential for monitoring and managing AWR behavior across the database instance.
Key Points
Default Configuration Values: Oracle Database sets the default AWR snapshot interval to 60 minutes and retention period to 8 days, which means snapshots are automatically collected every hour and stored for over a week. These defaults work well for most databases but can be customized based on specific monitoring requirements.
Data Type Format: The SNAP_INTERVAL and RETENTION columns use the INTERVAL DAY TO SECOND data type, displaying values in the format +DDDDD HH:MI:SS.F where D represents days, H hours, M minutes, and S seconds. For example, +00000 01:00:00.0 indicates 1 hour interval, while +00008 00:00:00.0 represents 8 days retention.
Performance Impact: The snapshot interval directly affects the granularity of performance data available for analysis, with shorter intervals providing more detailed historical information but consuming more storage space. Organizations experiencing performance issues during specific time windows may benefit from reducing the interval to capture more frequent snapshots during critical periods.
Modification Capability: Database administrators can modify these settings using the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure, where both interval and retention values are specified in minutes. Common modifications include reducing the interval to 30 minutes for detailed monitoring or extending retention to 30 days for longer historical analysis.
Insights
Storage Planning: Understanding AWR retention settings is crucial for capacity planning, as each database typically generates 4-10 MB of AWR data per day depending on workload complexity and snapshot frequency. Extended retention periods or shorter intervals significantly increase storage requirements in the SYSAUX tablespace where AWR data resides.
Troubleshooting Windows: The combination of interval and retention determines the troubleshooting window available to database administrators, with default settings providing approximately 192 snapshot intervals over 8 days. For environments requiring longer historical analysis or compliance requirements, increasing retention to 30 or 60 days ensures sufficient data availability for trend analysis and capacity planning.
Multi-Tenant Considerations: In Oracle multitenant environments with Container Databases (CDB) and Pluggable Databases (PDB), the DBA_HIST_WR_CONTROL view includes CON_ID and CON_DBID columns to identify which container the AWR data pertains to. This allows administrators to manage AWR settings independently for each pluggable database when needed.
Performance Report Generation: AWR snapshot data forms the foundation for Automatic Database Diagnostic Monitor (ADDM) reports and AWR performance reports, making proper configuration essential for effective database tuning. Without adequate retention, historical performance comparisons become impossible, limiting the ability to identify performance degradation trends over time.
Explanation
This simple yet powerful query provides database administrators with immediate visibility into AWR configuration, which is the first step in any performance tuning engagement. The DBA_HIST_WR_CONTROL view contains a single row (or multiple rows in multitenant environments) that governs AWR behavior across the entire database instance. By formatting the columns to 30 characters, the script ensures that the interval day-to-second values display completely without wrapping or truncation.
When executed, the query typically returns output showing values like "+00000 01:00:00.0" for snap_interval (indicating hourly snapshots) and "+00008 00:00:00.0" for retention (indicating 8-day retention). Database administrators use this information to assess whether current settings align with monitoring requirements and storage capacity constraints. For production databases experiencing intermittent performance issues, reducing the snapshot interval from 60 to 30 or even 15 minutes can capture transient problems that might be missed with hourly snapshots.
The trailing forward slash (/) executes the SQL statement in SQL*Plus or SQLcl environments, making this script immediately executable without requiring additional commands. This query should be run with sufficient privileges to access data dictionary views, typically requiring SELECT privileges on DBA_HIST_WR_CONTROL or the SELECT_CATALOG_ROLE role. Organizations implementing AWR-based monitoring strategies should regularly review these settings as part of database health checks to ensure performance data collection aligns with business requirements and operational needs.
References
Oracle DBA_HIST_WR_CONTROL Official Documentation - Official Oracle documentation describing the DBA_HIST_WR_CONTROL data dictionary view structure, columns, and data types
Oracle 18c DBA_HIST_WR_CONTROL Reference - Oracle Database 18c reference guide for the Workload Repository control view with container database considerations
How to Change AWR Snapshot Interval Guide - Practical guide explaining default AWR settings and step-by-step instructions for modifying snapshot intervals and retention periods
Modify AWR Snapshot Settings Tutorial - Comprehensive tutorial on checking and modifying AWR snapshot configuration with examples and verification steps
Change AWR Retention Period and Interval - Detailed walkthrough of changing AWR settings including calculation examples for converting days to minutes
Oracle Automatic Workload Repository Setup - Complete AWR setup guide covering snapshot management, report generation, and configuration best practices
Oracle Database 12c DBA_HIST_WR_CONTROL - Oracle 12c reference documentation for the DBA_HIST_WR_CONTROL view with column descriptions and usage notes
AWR Snapshot Interval and Retention Settings - Practical examples of querying AWR settings with SQL scripts to extract and calculate interval and retention values
Change Snapshot Interval Time and Retention - Guide explaining AWR retention concepts and providing scripts to modify snapshot timing and storage duration
Automatic Workload Repository Overview - Comprehensive article covering AWR architecture, configuration, and management in Oracle Database