AWR

AWR, or Automatic Workload Repository, is a built-in performance monitoring and reporting feature of Oracle Database. It collects and stores a historical record of database workload and performance statistics to support tuning, troubleshooting, and capacity planning. The data gathered by AWR serves as the basis for performance analyses and reports.

AWR operates by creating snapshots at regular intervals. By default, Oracle takes hourly snapshots, though the interval and the retention period can be configured using the DBMS_WORKLOAD_REPOSITORY package. Each snapshot captures a wide range of metrics, including wait events, CPU utilization, I/O statistics, session activity, and miscellaneous system statistics. AWR also includes Active Session History (ASH) sampling, which provides finer-grained insight into active sessions over time. The resulting data is stored in the SYSAUX tablespace in a set of WRH$_* and DBA_HIST_ views, enabling both historical queries and report generation.

Reports and diagnostics are central to AWR. Oracle provides scripts to generate AWR reports that summarize performance for a specified time window. AWR data is also used by the Automatic Database Diagnostic Monitor (ADDM), which analyzes the statistics to identify bottlenecks and suggest remedies. Features such as AWR baselines allow performance comparisons against defined reference periods, aiding trend analysis and capacity planning.

Administrative and licensing notes: AWR data collection, reports, and ADDM analysis are typically part of Oracle’s Diagnostics Pack and require appropriate licensing. Administrators manage AWR settings to balance detail, storage, and performance considerations, adjusting snapshot frequency and data retention as needed.

In short, AWR is Oracle’s mechanism for collecting long-term performance data, producing actionable reports, and supporting proactive database tuning and diagnostics.