Oracle AWR Report Deep Dive: SQL, Wait Events & I/O Analysis

Master Oracle AWR reports. Deep dive into top SQL, wait events, and I/O analysis to pinpoint and resolve database performance issues.

Overview

In the demanding world of enterprise applications, particularly those built on Oracle such as PeopleSoft, maintaining optimal database performance is not just a goal—it's a critical imperative. Slow transactions, unresponsive queries, or prolonged batch processing can directly impact business operations, user productivity, and ultimately, the bottom line. For Oracle Database Administrators (DBAs) and performance analysts, having robust tools to diagnose and resolve these performance bottlenecks is paramount. Enter the Automatic Workload Repository (AWR) report.

The Oracle Automatic Workload Repository (AWR) is an invaluable diagnostic tool that collects, processes, and maintains performance statistics for the Oracle database. Introduced in Oracle 10g, AWR automatically gathers database statistics, stores them in the SYSAUX tablespace, and provides a historical perspective on database performance. It captures a wide array of metrics, including top SQL statements, wait events, I/O statistics, resource utilization, and configuration changes, over defined time intervals known as "snapshots."

A deep dive into an AWR report allows us to move beyond superficial symptoms and pinpoint the root causes of performance issues. By analyzing the report, we can identify resource-intensive SQL queries, understand where the database is spending most of its time waiting, and diagnose I/O bottlenecks that might be choking the system. This article will guide you through a comprehensive analysis of the AWR report, focusing on its most critical sections: Top SQL, Wait Events, and I/O analysis, providing the practical knowledge needed to transform raw data into actionable performance tuning strategies for your Oracle PeopleSoft or any other OLTP environment.

Prerequisites

Before embarking on an AWR report deep dive, ensure you meet the following prerequisites:

Oracle Database Version: AWR is available in Oracle Database 10g and later versions. This analysis primarily applies to 11g, 12c, 18c, 19c, and 21c.
Diagnostic Pack License: Accessing and utilizing AWR reports requires the Oracle Diagnostic Pack license. Without this license, using AWR features is a violation of Oracle's licensing terms, even if the functionality is technically available.
Required Privileges: You need appropriate database privileges to generate and query AWR data. Typically, users with the DBA role have sufficient privileges. Alternatively, a user can be granted specific select privileges on the DBA_HIST_* views and execute privileges on the AWR report generation scripts.
Basic SQL*Plus Knowledge: Familiarity with SQL*Plus is necessary to execute the AWR report generation scripts and perform direct SQL queries against AWR views.
Understanding of Database Architecture: A foundational understanding of Oracle database architecture (SGA, PGA, background processes, physical vs. logical I/O) will significantly aid in interpreting the AWR report's findings.
Time Window: Identify the specific time window during which the performance issue occurred or during which you want to analyze performance. This will help you select the appropriate AWR snapshots.

Step-by-Step Implementation: Deep Diving into AWR

Generating an AWR Report

The first step is to generate the AWR report for the specific time interval you wish to analyze. The report generation script is typically located in $ORACLE_HOME/rdbms/admin/.

1. Connect to SQL*Plus as a user with DBA privileges (e.g., SYS or SYSTEM):

sqlplus / as sysdba

2. Run the AWR report script:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

The script will prompt you for several inputs:

Report Type: HTML (recommended for easy navigation) or TEXT.
Number of Days: How many days of snapshots to list. This helps narrow down the choices.
Begin Snapshot ID: The starting snapshot ID for your analysis window.
End Snapshot ID: The ending snapshot ID for your analysis window.
Report Name: A filename for the generated report (e.g., awr_report_peoplesoft_peak.html).

Example Interaction:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

This script generates an HTML or TEXT AWR report for a single instance.

Instances in this database
~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num Instance
----------- -------- --------
 1234567890        1 mydbinstance

Enter value for dbid: 1234567890
Using 1234567890 for database Id
Enter value for inst_num: 1
Using 1 for instance number

Specify the Report Type
-----------------------
'html' for HTML report
'text' for TEXT report
Enter value for report_type: html

Type specified: html

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for num_days: 7

Listing the last 7 days of snapshots.

                                Snap Id      Snap Started        Dur (Mins)
                               ------- -------------------- -----------
        DB Id     Inst Id                                             
--------------- ------- -------------------- -------- ------------
 1234567890       1     1015 05-May-24 08:00:09         60
                          1016 05-May-24 09:00:15         60
                          1017 05-May-24 10:00:03         60
                          1018 05-May-24 11:00:12         60
                          1019 05-May-24 12:00:08         60
                          1020 05-May-24 13:00:11         60
                          1021 05-May-24 14:00:05         60
                          1022 05-May-24 15:00:10         60

Enter value for begin_snap: 1018
Begin Snapshot Id specified: 1018

Enter value for end_snap: 1019
End   Snapshot Id specified: 1019

Specify the Report Name
-----------------------
The default report file name is awr_1_1018_1019.html.  To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awr_peoplesoft_peak_11am_to_12pm.html

Generating AWR report for 1234567890, 1 from 1018 to 1019
Report written to awr_peoplesoft_peak_11am_to_12pm.html

Once generated, open the HTML file in a web browser. The report is highly structured with clickable links to various sections.

Understanding the AWR Report Structure

A typical AWR report is extensive, but its structure is logical. Key sections include:

Report Summary: Database, instance, host, and snapshot details.
Cache Sizes: SGA and PGA sizes, buffer pool advice.
Load Profile: Database activity metrics (transactions, logical/physical reads, etc.).
Top 5 Timed Foreground Events: The most significant wait events.
SQL Statistics: Top SQL statements by various metrics (elapsed time, CPU, reads, executions).
Wait Events Statistics: Detailed breakdown of all wait events.
I/O Statistics: File and tablespace I/O metrics.
Advisory Sections: Buffer cache, shared pool, PGA aggregate target, Java pool, Streams pool.
SQL Statistics by Plan Hash Value: Helps identify plan changes.
Object Statistics: Top segments by logical/physical reads, buffer busy waits.

Focus Area 1: Top SQL Analysis

This section is crucial for identifying SQL statements that consume the most resources and are primary candidates for tuning. The AWR report presents "SQL Statistics" broken down by different metrics.

Key Metrics to Look For:

Elapsed Time: Total time taken by the SQL statement (CPU + wait time). High elapsed time indicates a slow query.
CPU Time: Time spent on CPU processing. High CPU time might suggest inefficient algorithms, complex calculations, or lack of proper indexing leading to high logical I/O.
Physical Reads: Number of blocks read from disk. High physical reads often point to missing indexes, full table scans, or poorly cached data.
Logical Reads (Buffer Gets): Number of blocks accessed in the buffer cache (memory). High logical reads, even with low physical reads, can indicate inefficient access paths or excessive scanning of data already in memory.
Executions: How many times the SQL was executed. A query with low elapsed time but very high executions can still be a major resource consumer overall.
Rows Processed: Number of rows returned or modified. Compare this to logical/physical reads to identify inefficient queries that read many blocks to return few rows.

Identifying Problematic SQL:

Look for SQL statements that appear at the top of lists sorted by "Elapsed Time," "CPU Time," "Physical Reads," or "Logical Reads." Also, pay attention to statements with a high "Executions" count, even if their individual resource consumption is moderate. These are often "hot" queries that, in aggregate, burden the system.

The AWR report provides the `SQL_ID` for each statement. You can use this `SQL_ID` to retrieve the full SQL text and its execution plan.

Example SQL to retrieve SQL text and execution plan:

SQL> SELECT sql_fulltext
     FROM   gv$sqlarea
     WHERE  sql_id = '&sql_id'
     AND    rownum = 1;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id', '&plan_hash_value', NULL, 'ALL'));

Here, `&sql_id` and `&plan_hash_value` would be replaced by values from the AWR report. The `PLAN_HASH_VALUE` is crucial because a SQL statement can have multiple execution plans over time. Analyzing the plan helps understand *how* Oracle executes the query and identify potential tuning opportunities like adding indexes, rewriting the query, or using hints.

For PeopleSoft environments, common culprits often include custom reports, complex GL journal processes, or poorly optimized component interface calls. Always consider the application logic when analyzing the SQL.

Focus Area 2: Wait Events Analysis

The "Top 5 Timed Foreground Events" section is often the first place DBAs look. It tells you where the database spent most of its active time waiting. Oracle categorizes these waits, giving insights into the nature of the bottleneck.

Common Wait Event Categories:

CPU: Not a wait event itself, but indicates the time spent on CPU processing. If CPU is high, it means the database is working hard, potentially due to inefficient SQL or lack of resources.
User I/O: Waiting for data blocks from disk (e.g., db file sequential read, db file scattered read). Indicates I/O bottlenecks.
System I/O: Waiting for I/O related to system processes (e.g., log file sync, log file parallel write). Points to redo log or archive log I/O issues.
Concurrency: Waiting for internal database resources (e.g., latches, mutexes, enqueues). Points to contention within the database. Examples: latch: cache buffers chains, enq: TX - row lock contention.
Application: Waiting for application-specific resources (e.g., enq: TX - allocate ITL entry, buffer busy waits). Often related to application design or data hot spots.
Commit: Waiting for a commit to complete (log file sync). Indicates slow redo log writes.
Network: Waiting for data transfer over the network (e.g., SQL*Net message from client).

Interpreting "Top 5 Timed Events":

Focus on events with high "Total Wait Time (sec)" and "Wait Avg(ms)".

If db file sequential read or db file scattered read are high, it points to disk I/O as a bottleneck. Investigate storage, indexing, and full table scans.
If log file sync is high, redo log writes are slow. Check disk I/O for redo logs, increase log buffer, or tune commit frequency.
If latch: cache buffers chains is high, there's contention for data blocks in the buffer cache, often due to hot blocks or inefficient SQL accessing the same blocks repeatedly.
If enq: TX - row lock contention is high, multiple sessions are trying to modify the same row, indicating application-level concurrency issues.

Drilling Down into Wait Events:

You can query `DBA_HIST_SYSTEM_EVENT` for a historical view of all wait events and `DBA_HIST_ACTIVE_SESS_HISTORY` for detailed session activity during specific waits.

SQL> SELECT event_name,
            total_waits_fg,
            total_timeouts_fg,
            time_waited_fg,
            average_wait_fg
     FROM   dba_hist_system_event
     WHERE  snap_id BETWEEN &begin_snap_id AND &end_snap_id
     AND    dbid = &dbid
     AND    instance_number = &instance_num
     AND    event_name NOT LIKE 'SQL*Net message%' -- Exclude network idle waits
     ORDER BY time_waited_fg DESC
     FETCH FIRST 10 ROWS ONLY;

This query helps confirm the top wait events and their aggregated statistics over the snapshot period.

Focus Area 3: I/O Analysis

I/O operations are frequently a performance bottleneck. The AWR report provides detailed I/O statistics to help pinpoint issues with storage, file placement, and database configuration.

Key I/O Metrics and Sections:

Tablespace IO Stats: Shows read/write activity per tablespace. Helps identify which tablespaces are experiencing heavy I/O.
File IO Stats: Provides I/O statistics per data file. This is more granular and can pinpoint specific files (and thus segments) causing I/O contention. Look for files with high "Avg Rd (ms)" (average read time in milliseconds) – anything consistently above 10-20ms is typically a concern.
Buffer Pool Advisory: Helps determine optimal sizing for the buffer cache.
Redo Bytes Generated: Indicates the amount of redo generated, which impacts redo log file I/O.

Interpreting I/O Statistics:

High Physical Reads and Writes: Indicates heavy disk activity. This could be due to full table scans, inefficient SQL, small buffer cache, or insufficient indexing.
High Average Read Times: If the average read time for data files is consistently high (e.g., > 20ms for random reads, > 10ms for sequential reads), it suggests a storage subsystem bottleneck. This might require engaging storage administrators.
db file sequential read vs. db file scattered read:
- db file sequential read: Typically for index lookups or single block reads. A high count implies many index lookups or single-row access.
- db file scattered read: Typically for full table scans. A high count suggests many full table scans are occurring, often due to missing indexes or optimizer choices.
Redo Log File I/O: If log file sync is a top wait event, examine the "Redo Log File I/O" section. Slow redo writes can be caused by slow disks, contention for the redo log group, or frequent small commits.

Example SQL to query historical I/O statistics:

SQL> SELECT df.file_name,
            ts.tsname AS tablespace_name,
            io.phyrds,
            io.phywrts,
            io.readtim,
            io.writetim,
            io.singleblkrdtim,
            io.avgrd_ms
     FROM   dba_hist_filestat_summary io
     JOIN   dba_data_files df ON io.file# = df.file_id
     JOIN   dba_tablespaces ts ON df.tablespace_name = ts.tablespace_name
     WHERE  io.snap_id BETWEEN &begin_snap_id AND &end_snap_id
     AND    io.dbid = &dbid
     AND    io.instance_number = &instance_num
     ORDER BY io.readtim DESC
     FETCH FIRST 10 ROWS ONLY;

This query can help identify specific data files or tablespaces that are experiencing the most I/O activity and longest read times, allowing you to focus tuning efforts on the objects within those areas.

Security Considerations

While AWR is a powerful performance diagnostic tool, it also collects and stores sensitive information about your database's operations. Therefore, proper security considerations are essential:

Data Exposure: AWR reports contain actual SQL text, execution plans, object names, and user activity patterns. This information could potentially expose application logic, data access patterns, and even sensitive data if it appears in SQL literals (though this is generally poor practice).
Access Control: Restrict access to AWR generation scripts and the underlying `DBA_HIST_*` views. Only grant the `DBA` role or specific `SELECT` privileges to trusted personnel who require performance analysis capabilities. Avoid granting these privileges to application users or developers who do not need them.
Report Storage: If AWR reports are generated and stored as files, ensure these files are kept in secure locations with appropriate file system permissions.
Data Retention: AWR data is stored in the `SYSAUX` tablespace according to a configurable retention policy. Ensure that this retention policy aligns with your organization's data privacy and compliance requirements, especially if sensitive SQL text is inadvertently captured. The default retention is 8 days, and the interval is 60 minutes. You can adjust these using `DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS`.
Separation of Duties: Implement a clear separation of duties where those responsible for performance tuning do not necessarily have full database administration rights that could alter data.

Best Practices

To maximize the utility of AWR reports and ensure effective performance management, consider these best practices:

Establish Baselines: Regularly generate AWR reports during periods of normal, healthy database operation (baselines). This allows you to compare current performance against a known good state, making it easier to identify performance regressions.
Trend Analysis: Don't just look at single AWR reports. Compare reports generated over different time periods (e.g., day-over-day, week-over-week, before/after a patch or upgrade) to identify trends and long-term performance shifts. Tools like AWR Compare Reports (`awrddrpi.sql` or `awrcp.sql`) are excellent for this.
Proactive Monitoring: Integrate AWR analysis into your routine database health checks. Don't wait for performance issues to arise; identify potential bottlenecks before they impact users.
Holistic Approach: While AWR provides rich internal database metrics, remember that database performance is often influenced by external factors (network, storage, application code, operating system). Use AWR in conjunction with OS-level tools (e.g., `vmstat`, `iostat`, `top`) and application-level monitoring.
Tune Iteratively: Performance tuning is an iterative process. Implement one change at a time, then re-evaluate the AWR report to measure the impact of that change before moving to the next.
Resource Management: Use AWR advisories (Buffer Cache Advisory, Shared Pool Advisory, PGA Aggregate Target Advisory) to make informed decisions about sizing your SGA and PGA components. Misconfigured memory can lead to excessive I/O or CPU consumption.
Understand Application Context: AWR reports show *what* is happening, but not always *why*. A deep understanding of the application (e.g., PeopleSoft modules, batch processes, user behavior) is crucial for translating AWR findings into meaningful solutions. For example, a high `log file sync` might be normal during a large PeopleSoft payroll run that commits frequently, but abnormal during typical online transaction processing.
Custom Snapshots: If a performance issue is very short-lived (e.g., a specific batch job that runs for only 10 minutes), consider creating custom snapshots around that specific event using `DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()`.

FAQ

Q1: Is AWR a free feature of Oracle Database?

No, the Automatic Workload Repository (AWR) is part of the Oracle Diagnostic Pack, which requires a separate license. While the functionality is built into the database, using AWR reports and its associated views (`DBA_HIST_*`) without a Diagnostic Pack license is a violation of Oracle's licensing terms. Oracle Enterprise Manager (OEM) also leverages AWR data for its performance pages, and using those also requires the license.

Q2: How often should I generate AWR reports?

The frequency depends on your needs. For routine performance monitoring, reviewing AWR reports weekly or monthly during peak load periods is a good practice to establish baselines and identify trends. When diagnosing an active performance issue, you should generate reports for the specific time window when the problem occurred, potentially with shorter snapshot intervals (e.g., 15-30 minutes) if the issue is transient. For critical systems, comparing AWR reports daily or even hourly can be beneficial.

Q3: Can AWR help with capacity planning?

Absolutely. AWR reports provide historical data on CPU utilization, I/O rates, memory usage, and growth trends for various database components. By analyzing AWR reports over extended periods (e.g., months or years), you can forecast resource requirements, identify bottlenecks that might emerge with increased workload, and make informed decisions about hardware upgrades, storage provisioning, or database scaling. The "Load Profile" and "Host CPU" sections, in particular, are very useful for capacity planning.

Conclusion

The Oracle AWR report is an indispensable tool in the arsenal of any Oracle DBA or performance analyst. It provides a comprehensive, historical view of database performance, allowing for precise identification and diagnosis of bottlenecks related to SQL execution, wait events, and I/O operations. By methodically dissecting the report's key sections, understanding the underlying metrics, and applying the best practices outlined in this article, you can transform raw performance data into actionable insights.

Whether you're managing a complex PeopleSoft environment with its myriad batch processes and online transactions, or any other mission-critical Oracle application, a deep dive into AWR empowers you to proactively tune your database, optimize resource utilization, and ensure a consistently high level of performance. Mastering AWR analysis is not just about fixing problems; it's about building a resilient, high-performing database infrastructure that supports your business objectives effectively and efficiently.

Oracle AWR Report Deep Dive: SQL, Wait Events & I/O Analysis