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

Overview

As a senior technology writer at TechNews Venture, I’ve witnessed countless organizations grapple with the elusive beast of database performance. In the Oracle ecosystem, few tools are as potent and comprehensive for performance diagnostics as the Automatic Workload Repository (AWR) report. It serves as an indispensable diagnostic and tuning aid, providing a detailed historical record of database activity and performance metrics. For mission-critical applications like Oracle Peoplesoft, where performance directly impacts business operations and user experience, a deep understanding of AWR is not just beneficial—it's imperative.

The AWR report, generated from snapshots of database statistics collected over specific intervals, offers a holistic view of an Oracle database's health and workload. It captures everything from CPU utilization and I/O statistics to SQL execution details and wait events, providing a rich tapestry of data that helps DBAs and performance engineers pinpoint bottlenecks, identify resource-intensive operations, and perform effective capacity planning. Without AWR, diagnosing intermittent performance issues or understanding long-term trends would be akin to navigating a complex maze blindfolded.

This article aims to provide a publication-ready deep dive into the Oracle AWR report, focusing on three critical areas: Top SQL analysis, Wait Events analysis, and I/O analysis. We will explore how to generate these reports, interpret their key sections, and leverage the insights gained to optimize your Oracle database environment, ensuring applications like Peoplesoft run with optimal efficiency and responsiveness.

Prerequisites

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

• Oracle Database Enterprise Edition: AWR is a licensed feature available only with Oracle Database Enterprise Edition. It is not available in Standard Edition.
• STATISTICS_LEVEL Parameter: The database initialization parameter STATISTICS_LEVEL must be set to TYPICAL (the default) or ALL. This setting enables the collection of all necessary statistics for AWR. You can check its current value with:

  
  SHOW PARAMETER STATISTICS_LEVEL;
  

• Sufficient Privileges: To generate an AWR report, you typically need to connect as SYSDBA. Alternatively, a less privileged user can be granted the AWR_REPORT_ROLE.
• SQL*Plus Knowledge: Familiarity with SQL*Plus is essential for generating the report and querying AWR views.
• Basic Performance Concepts: A foundational understanding of database performance concepts such as CPU utilization, I/O operations, memory management, and wait events will greatly aid in interpreting the report.
• AWR Snapshots: AWR automatically takes snapshots at regular intervals (defaulting to 60 minutes). Ensure that snapshots cover the period you wish to analyze. You can check the current snapshot interval with:

  
  SELECT snap_interval, retention FROM dba_hist_wr_control;
  

Step-by-step Implementation: Generating and Analyzing the AWR Report

Generating an AWR Report

Generating an AWR report is a straightforward process using the provided SQL script awrrpt.sql located in the $ORACLE_HOME/rdbms/admin/ directory. Here's how to do it:

1. Connect to SQL*Plus: Open a terminal and connect to your Oracle database as SYSDBA or a user with AWR_REPORT_ROLE.

   
   sqlplus / as sysdba
   

2. Execute the AWR Report Script: Run the awrrpt.sql script.

   
   SQL> @?/rdbms/admin/awrrpt.sql
   

   You will be prompted to provide several parameters:

   • Report Type: Choose html for a web-browser-friendly report or text for a plain text report. HTML is generally preferred for its readability and navigability.
   • Number of Days of Snapshots: Specify how many days of recent snapshots you want to list. This helps narrow down the selection for begin and end snapshot IDs.
   • Begin and End Snapshot IDs: These are crucial. Select the snapshot IDs that encompass the performance period you want to analyze. For example, if a performance degradation occurred between 10 AM and 11 AM, you would choose the snapshot just before 10 AM as your begin ID and the snapshot at or just after 11 AM as your end ID.
   • Report Name: Provide a filename for your AWR report. A descriptive name, like peoplesoft_performance_issue_20231026.html, is highly recommended.

   Here’s a full example of the command execution and typical responses:

   
   SQL> @?/rdbms/admin/awrrpt.sql
   
   -- Prompts will follow:
   -- Specify the Report Type
   -- ~~~~~~~~~~~~~~~~~~~~~~
   -- Enter 'html' for HTML report, or 'text' for plain text report
   -- Type Specified:  html
   
   -- Enter the number of days of snapshots to choose from
   -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   -- Entering the number of days will display the most recent snapshots
   -- (e.g., 7 days will display the last 7 days of snapshots).
   -- Pressing <return> without a value will default to 7 days.
   --
   -- Defaults to 7 days
   --
   -- Enter value for num_days: 1
   
   -- Listing the last 1 days of snapshots.
   
   -- Instance     DB Name        Snap Id   Snap Started    Snap Level
   -- ------------ ------------ --------- --------------- ----------
   -- mydbinst     MYDB           1000     26 Oct 2023 10:00          1
   -- mydbinst     MYDB           1001     26 Oct 2023 11:00          1
   -- mydbinst     MYDB           1002     26 Oct 2023 12:00          1
   -- mydbinst     MYDB           1003     26 Oct 2023 13:00          1
   -- mydbinst     MYDB           1004     26 Oct 2023 14:00          1
   -- mydbinst     MYDB           1005     26 Oct 2023 15:00          1
   
   -- Specify the Begin and End Snapshot Ids
   -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   -- Enter the Begin Snapshot Id: 1002
   -- Enter the End   Snapshot Id: 1004
   
   -- Specify the Report Name
   -- ~~~~~~~~~~~~~~~~~~~~~~~
   -- The default report file name is awr_1002_1004.html.  To use this name,
   -- press <return> to continue, otherwise enter an alternative.
   --
   -- Enter value for report_name: peoplesoft_awr_report_20231026_noon_to_2pm.html
   
   -- Generating AWR Report...
   -- Report written to peoplesoft_awr_report_20231026_noon_to_2pm.html
   

3. Open the Report: Once generated, open the HTML file in a web browser.

Deep Dive into AWR Sections

Report Summary and Workload Profile

The initial sections of the AWR report provide a high-level overview. The "Workload Profile" section, in particular, shows statistics like CPU usage, logical/physical reads, user calls, and transactions per second. Pay attention to the "DB time" metric, which indicates the total time spent by the database processing user requests. A high DB time suggests that the database is actively working, and the subsequent sections will help identify where that time is being spent.

Top SQL Analysis

The "SQL Statistics" section is arguably one of the most frequently visited parts of an AWR report. It identifies the SQL statements consuming the most resources, making them prime candidates for tuning. SQL statements are typically ordered by various metrics:

• SQL Ordered by Elapsed Time: Shows queries that took the longest to complete. These are often the ones directly impacting user experience.
• SQL Ordered by CPU Time: Identifies CPU-intensive queries, often due to complex calculations, extensive sorting, or inefficient join operations.
• SQL Ordered by Physical Reads: Highlights queries performing a large number of disk I/Os. This can indicate missing indexes, full table scans, or inefficient data access patterns.
• SQL Ordered by Executions: Shows frequently executed queries. Even if a query is fast, high execution counts can accumulate significant resource usage.

When analyzing Top SQL:

• Look for High Elapsed Time: These are directly visible to users. If a Peoplesoft page is slow, check for SQL statements with high elapsed time during that period.
• Correlate with Physical Reads: A query with high elapsed time and high physical reads often points to inefficient data access. Check for full table scans or suboptimal index usage.
• Examine Execution Counts: A query with low elapsed time but millions of executions can still be a bottleneck.
• Review SQL Text: The report provides the full SQL text. Analyze the query structure, predicates, and joins.
• Use DBMS_XPLAN: Once a problematic SQL ID is identified, use DBMS_XPLAN.DISPLAY_AWR to retrieve its execution plan from the AWR to understand how Oracle is processing the query.

  
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('<SQL_ID>', NULL, NULL, 'ALL'));
  

For programmatic analysis of top SQL from AWR views, you can use queries like this:

SELECT
    s.sql_id,
    s.module,
    s.executions_delta,
    ROUND(s.elapsed_time_delta / 1000000, 2) AS elapsed_sec,
    ROUND(s.cpu_time_delta / 1000000, 2) AS cpu_sec,
    s.physical_reads_delta,
    s.logical_reads_delta,
    s.rows_processed_delta,
    SUBSTR(TO_CHAR(st.sql_text), 1, 200) AS sql_text_snippet
FROM
    dba_hist_sqlstat s
JOIN
    dba_hist_snapshot sn ON s.snap_id = sn.snap_id AND s.instance_number = sn.instance_number
JOIN
    dba_hist_sqltext st ON s.sql_id = st.sql_id
WHERE
    sn.snap_id BETWEEN 1002 AND 1004 -- Replace with your snapshot range
AND
    s.instance_number = 1 -- Replace with your instance number
ORDER BY
    s.elapsed_time_delta DESC
FETCH FIRST 10 ROWS ONLY;

This query retrieves the top 10 SQL statements by elapsed time within a specified snapshot range directly from AWR history views.

Wait Events Analysis

Wait events are critical for understanding where your database is spending its time when it's not actively processing CPU instructions. The "Top 5 Timed Foreground Events" section in the AWR report is a cornerstone of performance tuning. It shows the primary bottlenecks experienced by user sessions.

Common wait events and their implications:

• DB CPU: This isn't a wait event but indicates the percentage of DB time spent on CPU. If this is high, your system is CPU-bound, or queries are CPU-intensive.
• db file sequential read: Waiting for single block reads, typically from index lookups. High values might indicate inefficient indexing, excessive small I/O operations, or hot blocks.
• db file scattered read: Waiting for multi-block reads, usually from full table scans or fast full index scans. High values suggest missing indexes, inefficient query predicates, or large table scans.
• log file sync: Waiting for the log writer to write redo entries to disk after a commit. High values indicate frequent commits (OLTP systems), slow redo log I/O, or contention for the log buffer.
• latch: cache buffers chains: Contention for latches protecting buffer cache blocks. Often caused by "hot blocks" (blocks frequently accessed by many sessions), leading to serialization.
• enq: TX - row lock contention: Sessions waiting for a row lock held by another transaction. This points to application concurrency issues, long-running transactions, or poor transaction design (common in Peoplesoft if customizations introduce locking).
• direct path read / direct path write: I/O operations bypassing the buffer cache, often associated with parallel query, large sorts, or temporary segment operations.

To analyze wait events programmatically:

SELECT
    e.event_name,
    e.total_waits_delta,
    ROUND(e.time_waited_delta / 1000000, 2) AS total_wait_sec,
    ROUND(e.average_wait_delta / 1000000, 2) AS avg_wait_sec,
    e.wait_class
FROM
    dba_hist_system_event e
JOIN
    dba_hist_snapshot sn ON e.snap_id = sn.snap_id AND e.instance_number = sn.instance_number
WHERE
    sn.snap_id BETWEEN 1002 AND 1004 -- Replace with your snapshot range
AND
    e.instance_number = 1
AND
    e.wait_class NOT IN ('Idle', 'Other') -- Exclude idle waits
ORDER BY
    e.time_waited_delta DESC
FETCH FIRST 10 ROWS ONLY;

This query provides the top 10 timed foreground events, excluding idle waits, from the AWR history.

"The wait event analysis is the heart of Oracle performance tuning. It tells you exactly why your database is pausing, allowing you to target the root cause rather than guessing."

I/O Analysis

Database I/O can often be a major bottleneck, especially for data-intensive applications like Peoplesoft. The AWR report provides detailed I/O statistics at various levels:

• Tablespace IO Stats: Shows I/O activity per tablespace (e.g., PS_APP_DATA, PS_INDEX, UNDOTBS1, SYSAUX, SYSTEM, TEMP). High reads/writes or slow average read/write times for a specific tablespace can indicate an issue with the underlying storage or objects within that tablespace.
• File IO Stats: Provides even finer-grained statistics for individual data files. This helps pinpoint specific files experiencing high I/O.

When analyzing I/O:

• Look for High Physical Reads/Writes: Identify tablespaces or data files with exceptionally high read or write activity.
• Check Average Read/Write Times: High average read or write times (e.g., above 10-20ms for reads, depending on storage type) indicate slow storage performance.
• Correlate with Top SQL and Wait Events: If `db file sequential read` or `db file scattered read` are top wait events, cross-reference them with the I/O stats to identify which tablespaces or files are being hit hardest. Then, look at Top SQL to see which queries are driving that I/O.
• Consider Storage Configuration: Is the storage properly configured? Are disks balanced? Is RAID being used effectively? Are there enough IOPS available?

To retrieve I/O statistics for data files from AWR views:

SELECT
    f.tsname,
    f.filename,
    f.phyrds_delta AS physical_reads,
    f.phywrts_delta AS physical_writes,
    ROUND(f.readtim_delta / 100, 2) AS avg_read_ms,
    ROUND(f.writetim_delta / 100, 2) AS avg_write_ms
FROM
    dba_hist_filestatxs f
JOIN
    dba_hist_snapshot sn ON f.snap_id = sn.snap_id AND f.instance_number = sn.instance_number
WHERE
    sn.snap_id BETWEEN 1002 AND 1004 -- Replace with your snapshot range
AND
    f.instance_number = 1
ORDER BY
    f.phyrds_delta DESC
FETCH FIRST 10 ROWS ONLY;

This query provides the top 10 data files by physical reads within the specified snapshot range, including average read and write times.

Advanced AWR Views

Beyond the HTML report, Oracle provides a rich set of AWR history views (prefixed with DBA_HIST_) that allow for programmatic access to the collected performance data. These views are invaluable for custom reporting, trend analysis, and integration with other monitoring tools. Key views include:

• DBA_HIST_SNAPSHOT: Information about AWR snapshots.
• DBA_HIST_SQLSTAT: Performance statistics for SQL statements.
• DBA_HIST_ACTIVE_SESS_HISTORY (ASH): More granular session-level activity sampled every second, offering deeper insights into transient performance issues.
• DBA_HIST_SYSSTAT: System-wide performance statistics.
• DBA_HIST_SYSMETRIC: System metrics like CPU usage, I/O rates, and network traffic.

Security Considerations

AWR reports contain a wealth of sensitive information about your database's internal workings, including actual SQL text (which might contain application logic or even sensitive data if not properly masked), object names, user activity patterns, and resource consumption. This data, if exposed, could potentially be leveraged by malicious actors to identify vulnerabilities or gain insights into your application's architecture.

• Restrict Access: Limit access to AWR reports and the ability to generate them to authorized personnel only (e.g., DBAs, senior performance engineers). The AWR_REPORT_ROLE should be granted judiciously.
• Secure Storage: Ensure that generated AWR reports are stored in secure locations with appropriate file system permissions. Avoid placing them on publicly accessible network shares.
• Data Masking: If sharing AWR reports with third parties or less trusted individuals, consider redacting or masking sensitive SQL text, especially if it contains literal values that might expose data.
• Audit Access: Regularly audit who is accessing and generating AWR reports to ensure compliance with security policies.
• Encryption: If AWR reports need to be transmitted over networks or stored on portable media, ensure they are encrypted.

Best Practices

To maximize the utility of AWR for performance management:

• Establish Baselines: Regularly generate AWR reports during periods of normal, peak, and off-peak activity. These "baselines" are crucial for comparison when performance degrades.
• Proactive Monitoring: Don't wait for performance issues to arise. Review AWR reports periodically (e.g., weekly or monthly) to identify emerging trends or potential bottlenecks before they impact users.
• Correlate with OS Metrics: AWR provides database-centric data. Always correlate AWR findings with operating system metrics (CPU, memory, disk I/O, network) using tools like vmstat, iostat, and sar to get a complete picture of resource utilization.
• Focus on Top Contributors: AWR reports can be overwhelming. Concentrate your efforts on the top 5-10 items in each critical section (Top SQL, Top Wait Events, Top I/O consumers). Addressing the biggest bottlenecks typically yields the most significant improvements.
• Iterative