What is Oracle AWR? From Performance Analysis Basics to Report Generation and Utilization

During the operation of an Oracle Database, have you ever encountered performance issues such as “the system response suddenly became slow” or “CPU utilization remains high”?

In such situations, the most reliable official tool is AWR (Automatic Workload Repository). AWR is a powerful feature that automatically collects and saves database performance information periodically, which can be utilized for identifying bottlenecks and tuning. This article provides an easy-to-understand explanation for beginner to intermediate Oracle engineers, covering fundamental knowledge of AWR, verifying settings, report generation procedures, and analysis points in practical operations.

1. Conclusion / Shortest Steps (Quick List for AWR Report Generation)
2. Background and Fundamentals: AWR Mechanism and Retention Conditions
3. Procedure / Implementation: Enabling AWR and Verifying Settings
4. Execution Examples: Snapshot Verification and AWR Report Generation
1. 1. Identify the Target Snapshot IDs
2. 2. Execute the AWR Report Generation Script
5. Operations and Practice: Key Report Sections and Comparative Analysis
1. List of Critical Sections in AWR Reports
2. Effective Analysis Technique: Comparing Normal vs. Abnormal States
6. Troubleshooting
7. Licensing and Important Considerations
8. FAQ: Frequently Asked Questions
9. Summary: Key Points Checklist

1. Conclusion / Shortest Steps (Quick List for AWR Report Generation)

The shortest procedure to generate an AWR report and initiate analysis is as follows:

Verify Privileges: Connect to SQL*Plus using a user that possesses SYSDBA administrative privileges (such as SYS).
Identify Snapshot IDs: Determine the “Begin ID” and “End ID” that cover the time window when the performance degradation occurred.
Execute the Dedicated Script: Run @?/rdbms/admin/awrrpt.sql.
Respond to Interactive Navigation: Enter the output format (HTML recommended), instance number, target Begin/End IDs, and the filename to output the report.

2. Background and Fundamentals: AWR Mechanism and Retention Conditions

What is AWR (Automatic Workload Repository)?

AWR is a built-in feature that automatically collects, processes, and maintains performance metrics and operational statistics for Oracle Database. It primarily accumulates crucial information such as:

Instance load status (CPU utilization, wait events, memory usage, etc.)
Statistical information for high-load SQL statements (execution counts, parse time, buffer gets, etc.)
Segment statistics (identifying which tables or indexes have concentrated I/O)
Initialization parameter modification history

Overall Concept Diagram of AWR

       [Oracle Database]
               │
               ▼ (Automatically collected every hour by default)
 ┌──────────────────────────┐
 │     AWR Snapshot         │ ──＞ Saved to SYSAUX tablespace (8 days by default)
 └──────────────────────────┘
               │
               ▼ (Specify Begin and End IDs when trouble occurs)
 ┌──────────────────────────┐
 │  Execute AWR Report Gen  │ ──＞ Script: awrrpt.sql
 └──────────────────────────┘
               │
               ▼
 ┌──────────────────────────┐
 │   HTML / TEXT Report     │ ──＞ Identify bottlenecks by comparing with normal state
 └──────────────────────────┘

Storage Location, Collection Interval, and Retention Period

Data collected by AWR is stored in the SYSAUX tablespace, which is used for system management. The default control criteria are as follows:

Collection Interval: Every 1 hour (60 minutes)
Retention Period: 8 days (192 hours)

These setting values can be verified at any time by querying the data dictionary view DBA_HIST_WR_CONTROL.

3. Procedure / Implementation: Enabling AWR and Verifying Settings

To utilize the AWR feature, the initialization parameter STATISTICS_LEVEL, which controls the statistical collection level for the entire database, must be configured appropriately.

STATISTICS_LEVEL Settings and Impact

Setting Value	Impact on AWR Feature	Overview
BASIC	Disabled	Many automatic tuning and advisor features, including AWR, will stop (Not Recommended).
TYPICAL	Enabled (Default)	Collects the standard level of statistics required for database performance monitoring.
ALL	Enabled (Detailed)	In addition to `TYPICAL` information, includes more detailed information such as OS execution plan execution statistics.

Step 1: Verify the Current Configuration Status

To verify the current statistical collection level, execute the following command:

SHOW PARAMETER STATISTICS_LEVEL;

To check the current snapshot collection interval and retention period, execute the following query:

SELECT snap_interval, retention FROM dba_hist_wr_control;

SQL Intent and Results:

If SNAP_INTERVAL displays +00 01:00:00 (1 hour) and RETENTION displays +08 00:00:00 (8 days), AWR is operating normally under standard settings.

Step 2: Modify the Parameter as Needed

If STATISTICS_LEVEL is set to BASIC, AWR will not function. Therefore, modify it to TYPICAL using the following command:

Change the statistical collection level to the recommended value TYPICAL, applying it immediately and ensuring it persists after a restart.

ALTER SYSTEM SET STATISTICS_LEVEL = TYPICAL SCOPE=BOTH;

Operational Considerations and Remediation:

Modifications to STATISTICS_LEVEL take effect immediately. To revert to the original state (e.g., BASIC), execute SET STATISTICS_LEVEL = BASIC SCOPE=BOTH; again. However, lowering it to BASIC is not recommended except for specific troubleshooting purposes. Additionally, if free space in the SYSAUX tablespace becomes constrained, consider adjusting settings such as shortening the retention period (RETENTION).

4. Execution Examples: Snapshot Verification and AWR Report Generation

1. Identify the Target Snapshot IDs

Before generating a report, identify the SNAP_ID corresponding to the time window when performance degradation occurred.

Display a list of execution timestamps and IDs for past snapshots:

SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id;

Execution Result Image:

SQL> SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
  2  FROM DBA_HIST_SNAPSHOT
  3  ORDER BY SNAP_ID;

   SNAP_ID BEGIN_INTERVAL_TIME                      END_INTERVAL_TIME
---------- ---------------------------------------- -------------------------------
         1 25-04-04 14:12:46.000                    25-04-04 14:23:47.061
         2 25-04-04 14:23:47.061                    25-04-04 15:00:07.267
         3 25-04-04 15:00:07.267                    25-04-04 16:00:35.029
         4 25-04-04 16:00:35.029                    25-04-04 17:00:49.228
         5 25-04-04 17:00:49.228                    25-04-04 18:00:03.047
         6 25-04-04 18:00:03.047                    25-04-04 19:00:17.300

6行が選択されました。

Explanation of Results:

For instance, if an event occurred where “the system became sluggish around 16:30,” you would generate a report by specifying the window from 16:00 (SNAP_ID = 4) to 17:00 (SNAP_ID = 5), or an extended range including the periods before and after (e.g., IDs 3 to 5).

2. Execute the AWR Report Generation Script

Call the standard script provided by Oracle within SQL*Plus.

Launch the interactive script for AWR report generation:

@?/rdbms/admin/awrrpt.sql

Interactive Flow During Script Execution:

enter value for report_type: Specify the output format. Enter html (recommended), which makes it easy to check charts and links via a browser, or text.
enter value for num_days: Enter the number of days of past snapshot history you wish to display as choices (e.g., 1).
enter value for begin_snap: Enter the begin snapshot ID for the time window you want to investigate (e.g., 4).
enter value for end_snap: Enter the end snapshot ID for the time window you want to investigate (e.g., 5).
enter value for report_name: Enter the filename to be generated. If the default is acceptable, simply press Enter.

5. Operations and Practice: Key Report Sections and Comparative Analysis

AWR reports are exceptionally vast, but you should focus on the following five sections to quickly discern bottlenecks in practical operations.

List of Critical Sections in AWR Reports

Section Name	Perspective to Monitor / Verify
Load Profile	Verify the overall system workload intensity (transactions per second, logical reads, Redo generation volume, etc.).
Instance Efficiency	Verify efficiency indicators for memory and parsing processes, such as parse ratio and buffer cache hit ratio (ideally 90% or higher normally).
Top 5 Timed Events	Top 5 wait events (`DB time`) consuming the most time across the entire system. The most critical section for identifying bottlenecks.
SQL Statistics	Identify inappropriate SQL statements that exhibit an abnormally high execution frequency or consume massive amounts of CPU time or I/O (disk reads).
Segment Statistics	Specific tables or indexes where massive volumes of reads/writes are occurring. Material for evaluating physical placement or partitioning.

Effective Analysis Technique: Comparing Normal vs. Abnormal States

Looking at a single AWR report in isolation makes it difficult to judge whether the metrics of a wait event are “within an acceptable range for that system.” Real bottleneck variations emerge clearly only when you place an “AWR report from normal operations when no issues occurred” side-by-side with an “AWR report from when the issue occurred” for comparison.

Signs of Sudden Load Spikes: A sharp increase in Redo size or Executes (execution counts) within the Load Profile section.
Signs of Response Delays: In the Top 5 Timed Events, wait events that are not usually seen (e.g., log file sync waiting for log writes, or buffer busy waits indicating buffer contention) occupy top positions.
Signs of I/O Performance Degradation: In Segment Statistics, Logical Reads or Physical Reads stand out for specific objects, indicating an issue with SQL access paths (such as full table scans).

6. Troubleshooting

These are common issues encountered during AWR operation or data retrieval, along with their resolution steps.

Phenomenon / Error	Primary Cause	Workaround Steps
Snapshots are not displayed in the list.	Automatic collection had stopped because `STATISTICS_LEVEL` was configured to `BASIC`.	Change the parameter to `TYPICAL` and execute `EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;` manually to verify immediate collection.
Insufficient capacity in the `SYSAUX` tablespace.	The snapshot retention period (`RETENTION`) is too long, or the collection interval is too short.	Use the `DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS` procedure to adjust the retention period to be shorter (e.g., to 7 days).

7. Licensing and Important Considerations

While AWR is an exceptionally convenient tool, you must strictly adhere to Oracle licensing terms when using it.

Target Editions: Available exclusively for Enterprise Edition (EE). In Standard Edition 2 (SE2) environments, the AWR feature itself is unsupported or restricted.
Additional License Requirements: To reference or extract AWR data to generate and utilize reports, a contract for a paid option license called “Oracle Diagnostics Pack” is mandatory in addition to the Enterprise Edition license.
Alternative Approaches (For SE2 environments, etc.): For environments that do not possess a Diagnostics Pack license, or to perform equivalent performance analysis in a Standard Edition 2 environment, it is recommended to set up and utilize Statspack, which is provided free of charge as a standard feature.

8. FAQ: Frequently Asked Questions

Q. Can I manually capture an AWR snapshot right now without waiting for the next hour?

A. Yes, it is possible. By executing the following PL/SQL package with a user possessing administrative privileges (SYSDBA), you can generate a snapshot immediately at any timing:

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

Q. I accidentally executed the script even though I do not possess an Oracle Diagnostics Pack license. Will there be any issues with the database operation?

A. There are no adverse technical impacts, such as database termination or data corruption, caused by executing the script. However, because it risks being deemed a license violation (compliance breach), avoid executing related scripts like awrrpt.sql or directly accessing AWR-related views starting with DBA_HIST_ in environments where the license is not held.

Q. AWR data has accumulated too much and the SYSAUX tablespace is about to overflow. Can I delete old data manually?

A. Yes, by using the DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE procedure, you can manually delete data for a specified range of snapshot IDs collectively. However, since older snapshots are normally purged automatically based on the retention period (RETENTION) setting, we recommend reviewing the automatic configurations first.

9. Summary: Key Points Checklist

AWR (Automatic Workload Repository) is a standard feature for EE that automatically collects and accumulates performance information for Oracle Database.
Under default settings, data is saved to the SYSAUX tablespace, the collection interval is 1 hour, and the retention period is 8 days.
For normal operation, the initialization parameter STATISTICS_LEVEL must be configured to TYPICAL or ALL.
Report generation can be conducted interactively by executing the dedicated script @?/rdbms/admin/awrrpt.sql from SQL*Plus or similar utilities.
To accurately pinpoint the causes of performance degradation, an operational approach of retaining a baseline report from normal operations (as a comparison target)—not just the report from when trouble occurs—is extremely crucial.

This article targets Oracle Database 19c for its explanations (other versions may differ in screens or default values).

[reference]
Gathering Database Statistics

Oracle AWR Usage Guide: Steps to Generate Reports and Basics of Analysis [26ai Compatible]

Oracle AWR (Automatic Workload Repository) is the de facto standard for performance analysis in Oracle Database Enterpri…

How to Handle Increased SYSAUX Tablespace Usage

1. What is the SYSAUX Tablespace?The SYSAUX tablespace is an auxiliary tablespace for the Oracle database, primarily use…