Performance Tuning with Oracle AWR: Deep Dive into SQL, Waits & I/O

Deep dive into Oracle AWR reports: analyze top SQL, wait events, and I/O to optimize performance. Master performance tuning with expert insights.

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

As Someshwar Thakur, a senior technology writer at TechNews Venture, I've spent years dissecting the intricacies of enterprise systems. For Oracle Database administrators and performance engineers, few tools are as indispensable as the Automatic Workload Repository (AWR) report. It's not merely a collection of statistics; it's a diagnostic blueprint, offering a granular view into the operational heartbeat of your Oracle instance. In the complex world of Oracle Peoplesoft deployments, where transaction volumes can be immense and performance directly impacts business continuity, mastering AWR report analysis is not just a skill—it's a critical competency.

This article will guide you through a deep dive into the AWR report, focusing on three pivotal areas: identifying top SQL statements, understanding wait events, and analyzing I/O patterns. By the end, you'll be equipped with the knowledge to systematically interpret an AWR report and pinpoint the root causes of performance bottlenecks in your Oracle environment.

Overview of the AWR Report

The AWR is an internal component of the Oracle Database that automatically collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. It captures snapshots of performance data at regular intervals, storing them in the SYSAUX tablespace. These snapshots contain crucial information about the database's workload, resource consumption, and wait events over a specific period.

An AWR report is a textual or HTML representation of the differences between two AWR snapshots. It provides a comprehensive, time-based analysis of database activity, allowing DBAs to compare performance characteristics over different periods, identify performance regressions, and diagnose the underlying causes of slow performance. For large-scale applications like Oracle Peoplesoft, understanding the AWR report is paramount for maintaining optimal system health, ensuring smooth user experience, and preventing costly outages.

Key areas an AWR report illuminates include:

Load Profile: A high-level summary of database activity.
Instance Efficiency: Percentages indicating how efficiently the instance is utilizing resources.
Top 5 Timed Foreground Events: The most significant bottlenecks experienced by user sessions.
SQL Statistics: Detailed metrics for the most resource-intensive SQL statements.
I/O Statistics: Breakdown of read/write operations across datafiles, tablespaces, and disk groups.
Memory Usage: SGA and PGA allocation and efficiency.
Wait Class Statistics: Categorization of all wait events.

Prerequisites for AWR Report Generation and Analysis

Before you can dive into generating and analyzing AWR reports, ensure the following prerequisites are met:

Diagnostic Pack License: The AWR feature is part of the Oracle Diagnostic Pack, which requires a separate license. Without it, using AWR is a violation of your Oracle licensing agreement. If you do not have the Diagnostic Pack license, you must rely on the older, free STATSPACK utility, which offers similar but less detailed functionality.
STATISTICS_LEVEL Parameter: The database initialization parameter STATISTICS_LEVEL must be set to TYPICAL or ALL. TYPICAL is the default and sufficient for AWR data collection. Setting it to BASIC disables most advisory and statistics collection, including AWR.
AWR Snapshot Interval and Retention: AWR automatically takes snapshots at a default interval of 60 minutes and retains them for 8 days. You can check and modify these settings using the DBMS_WORKLOAD_REPOSITORY package. For critical systems or during active performance investigations, you might temporarily reduce the interval to 15 or 30 minutes.
Access and Privileges: You need SQL*Plus access to the database and appropriate privileges. Typically, connecting as SYSDBA or a user granted the SELECT_CATALOG_ROLE and ADVISOR roles is sufficient.

To check the current AWR snapshot interval and retention:


SQL> SELECT snap_interval, retention FROM dba_hist_wr_control;

SNAP_INTERVAL      RETENTION
------------------ ------------------
+00000 01:00:00.0   +00008 00:00:00.0

To change the snapshot interval to 30 minutes and retention to 30 days (example):


SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30, retention => 43200);

(Note: interval is in minutes, retention is in minutes. 30 days * 24 hours/day * 60 minutes/hour = 43200 minutes)

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

1. Generating the AWR Report

The most common way to generate an AWR report is via SQL*Plus:

Connect to your database as a user with appropriate privileges (e.g., SYSDBA):
```
sqlplus / as sysdba
```
Run the AWR report script. This script is located in $ORACLE_HOME/rdbms/admin/awrrpt.sql:
```
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
```
The script will prompt you for several inputs:
- Report Type: Choose html for a more readable, navigable report, or text for command-line viewing. For deep analysis, HTML is highly recommended.
- Number of Days of Snapshots to List: Enter a number (e.g., 1 for snapshots from the last day).
- Begin Snapshot Id: Select the snapshot ID representing the start of your analysis window.
- End Snapshot Id: Select the snapshot ID representing the end of your analysis window.
- Report Name: Provide a filename for the generated report (e.g., awr_peoplesoft_peak_hour.html).

A typical interaction might look like this:


Enter value for report_type: html
Type specified: html

Enter the number of days of snapshots to list: 1

Listing the last 1 days of snapshots
... (list of snapshots with IDs, dates, and times) ...

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

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

Enter value for report_name: awr_report_20231027_peak.html
Report name specified: awr_report_20231027_peak.html

Generating AWR Report - awr_report_20231027_peak.html

Once generated, open the HTML file in a web browser.

2. Navigating and Interpreting Key Sections

a. Load Profile

The Load Profile section provides a high-level overview of the database's activity during the report interval. Look for:

DB Time (s): This is the total time spent by foreground sessions in the database, either actively working or waiting. It's often considered the primary metric for database workload. High DB Time indicates a busy database.
Redo size: Amount of redo generated. High values can indicate intensive DML operations or frequent commits.
Logical reads (blocks): Number of blocks read from the buffer cache. High logical reads per transaction often point to inefficient SQL or missing indexes.
Physical reads (blocks): Number of blocks read from disk. High physical reads indicate I/O bound operations.
Hard parses: Number of times SQL statements had to be re-parsed. High hard parses indicate shared pool contention and can be very expensive.

Expert Tip: Correlate "DB Time" with your business metrics. If DB Time is high but your application's throughput is low, it suggests inefficiency. If both are high, your database is simply working hard to meet demand.

b. Instance Efficiency Percentages

These percentages offer insights into how efficiently the Oracle instance is using its memory structures and other resources. While not always definitive, they can highlight potential areas for improvement:

Buffer Nowait %: Should be close to 100%. Lower values indicate contention for buffer cache blocks.
Redo NoWait %: Should be close to 100%. Lower values indicate contention for redo buffer space.
Buffer Hit %: Indicates how often a requested block was found in the buffer cache. While traditionally seen as critical, a high buffer hit ratio isn't always good if it's accompanied by high logical reads and inefficient SQL. Focus more on physical reads and wait events.
In-memory Sort %: The percentage of sorts that occurred in memory. High values are good; low values suggest a need to increase PGA_AGGREGATE_TARGET.
Library Hit %: The percentage of times an executable form of SQL or PL/SQL was found in the library cache without needing to be re-parsed. High values (close to 100%) are ideal; low values indicate high hard parsing.

c. Top 5 Timed Foreground Events

This is arguably the most critical section. It lists the wait events that consumed the most database time for foreground sessions. These are the primary bottlenecks. Focus on the "Time (s)" and "% DB time" columns.

db file sequential read: Waiting for single blocks to be read from disk, typically during index lookups. High values might indicate inefficient index usage, missing indexes, or slow storage for indexed access.
db file scattered read: Waiting for multiple blocks to be read from disk, usually during full table scans or fast full index scans. High values suggest inefficient queries doing large table scans, or slow storage.
log file sync: Waiting for the redo log buffer to be written to the redo log file on disk (happens on commit). High values indicate frequent commits, slow I/O for redo logs, or network latency if redo logs are on remote storage.
CPU + CPU waiting for CPU: This isn't a wait event in the traditional sense, but represents time spent on CPU or waiting for CPU. If this is high, the database is CPU-bound. Solutions include optimizing CPU-intensive SQL, reducing logical I/O, or adding more CPU resources.
latch: cache buffers chains: Contention for latches that protect the chains of blocks in the buffer cache. Often indicates "hot blocks" (blocks frequently accessed by many sessions), frequently updated rows, or inefficient block access patterns.
library cache lock / library cache pin: Contention in the shared pool, often due to frequent hard parsing, invalidation of objects, or long-running DDL operations.

Example Interpretation: If "db file sequential read" is the top event with 30% DB time, it means 30% of the database's active time was spent waiting for single-block I/O. This immediately tells you to investigate SQL statements performing many index lookups or single-block reads, and the underlying storage performance.

d. SQL Ordered by Elapsed Time (and other metrics)

This section identifies the most resource-intensive SQL statements. While AWR reports multiple lists (by Elapsed Time, CPU Time, Gets, Reads, Executions), "SQL Ordered by Elapsed Time" is usually the most important as it directly correlates with user experience.

For each SQL ID, examine:

Elapsed Time (s): Total time spent executing this SQL statement.
CPU Time (s): CPU time consumed by this SQL.
Executions: Number of times the SQL was executed.
Gets (Buffer Gets): Number of logical reads. High values per execution often mean inefficient access paths.
Reads (Physical Reads): Number of physical disk reads. High values per execution indicate I/O-intensive SQL.
Writes (Physical Writes): Number of physical disk writes.
Parse Calls: Number of times the SQL was parsed. High parse calls relative to executions might indicate cursor sharing issues.

Look for SQL statements with:

High Elapsed Time but few Executions (a single slow query).
Moderate Elapsed Time but many Executions (a frequently run query that adds up).
High "Gets/Exec" or "Reads/Exec" (inefficient SQL accessing too much data).

To get the full SQL text and its execution plan for a suspicious SQL_ID (e.g., g2y5c6x7p8q9r):


SQL> SELECT sql_text FROM dba_hist_sqltext WHERE sql_id = 'g2y5c6x7p8q9r';

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('g2y5c6x7p8q9r', null, null, 'ALL'));

Analyzing the execution plan is the next step to understand *why* the SQL is slow (e.g., full table scans, poor join order, missing indexes).

e. I/O Analysis: Tablespace, File, and Disk Group I/O Stats

These sections help identify I/O hotspots and potential storage bottlenecks.

Tablespace IO Stats: Lists I/O activity per tablespace. Look for tablespaces with high "Reads" or "Writes" and, critically, high "Avg Read Time (ms)". High average read times suggest slow storage.
File IO Stats: Provides a more granular view, showing I/O activity per datafile. This helps pinpoint specific datafiles within a tablespace that are experiencing heavy I/O. For example, if PS_APP_DATA01.dbf in PSAPPL tablespace has high reads and slow average read times, you know exactly which file to investigate.
Disk Group IO Stats (for ASM environments): If you're using Automatic Storage Management (ASM), this section shows I/O activity across your disk groups. It helps identify if a particular disk group is overloaded or experiencing performance issues.

Correlation: If "db file sequential read" or "db file scattered read" are top wait events, this I/O section helps you identify *which* tablespaces or datafiles are contributing most to those waits.

f. Memory Statistics

The AWR report provides insights into SGA and PGA usage and advisories.

SGA Target and Current: Shows the configured and currently allocated SGA size.
Buffer Cache Advisory: Suggests optimal buffer cache sizes based on predicted physical reads. This can help you determine if increasing DB_CACHE_SIZE would significantly reduce physical I/O.
Shared Pool Advisory: Provides similar advice for the shared pool. Low library cache hit ratios or high hard parses might suggest increasing shared pool size.
PGA Aggregate Target Advisory: Recommends optimal PGA_AGGREGATE_TARGET values to minimize PGA memory spills to disk for sorting and hashing operations.

These advisories are very useful for tuning memory parameters, but always consider the overall system memory availability.

g. Wait Class Statistics

This section categorizes all wait events into broader classes (e.g., User I/O, System I/O, Concurrency, Application, Commit, CPU, Network, Other). It gives a high-level view of where the database spends most of its time waiting. If "User I/O" dominates, it reinforces that disk I/O is the primary bottleneck. If "CPU" is highest, the system is CPU-bound.

Security Considerations

AWR reports contain highly sensitive information about your database's workload, including full SQL text, object names, and sometimes even bind variable values (if enabled for capture). Therefore, handling AWR reports requires careful security consideration:

Access Control: Restrict access to AWR data (DBA_HIST_* views) and the ability to generate reports. Granting SELECT_CATALOG_ROLE or ADVISOR role should be done judiciously. Only authorized DBAs or performance engineers should have this access.
Report Storage: If AWR reports are generated and stored outside the database (e.g., on a file system), ensure they are stored in secure locations with appropriate file system permissions. For highly sensitive environments, consider encrypting these reports at rest.
Anonymization: Before sharing AWR reports with external parties (e.g., Oracle Support, third-party consultants), consider anonymizing sensitive SQL text or object names if possible and necessary.
Auditing: Implement auditing for access to AWR-related views and packages (e.g., DBMS_WORKLOAD_REPOSITORY) to track who is generating or viewing performance data.

Best Practices for AWR Analysis

Establish Baselines: Regularly generate AWR reports during periods of normal, healthy operation (e.g., daily during peak business hours). These baseline reports are invaluable for comparing against problem periods to quickly identify deviations.
Focus on "DB Time" and "Top 5 Timed Foreground Events": These are your primary indicators of where the database is spending its time. Address the highest contributors first.
Don't Chase Hit Ratios Blindly: While hit ratios (Buffer Hit %, Library Hit %) can be indicators, they are often misleading. A high buffer hit ratio with high logical I/O might still mean inefficient SQL. Focus on absolute waits and resource consumption.
Correlate with OS and Application Metrics: An AWR report tells you what the database is doing, but not always why. Correlate AWR findings with operating system CPU, memory, and I/O metrics (e.g., using vmstat, iostat, sar), network latency, and application-level logs.
Use AWR Diff Reports: When comparing two periods (e.g., before and after a change, or a good period vs. a bad period), use awrddrpt.sql to generate a differential report. This highlights the changes in metrics between the two intervals.
Iterative Tuning: Performance tuning is an iterative process. Identify the top bottleneck, implement a change, and then generate a new AWR report to measure the impact.
Consider ADDM: The Automatic Database Diagnostic Monitor (ADDM), which runs automatically after each AWR snapshot, provides intelligent advice based on AWR data. Always review ADDM findings in conjunction with your manual AWR analysis.

Frequently Asked Questions (FAQ)

Q1: Can I use AWR without the Diagnostic Pack license?

A: No. The AWR feature, along with ADDM and SQL Monitoring, is part of the Oracle Diagnostic Pack, which requires a separate license. Using these features without the appropriate license is a breach of your Oracle agreement. If licensing is an issue, Oracle's free STATSPACK utility provides similar, though less comprehensive, performance reporting capabilities.

Q2: How do I determine the optimal snapshot interval for AWR?

A: The default 60-minute interval is generally suitable for proactive monitoring. However, for active performance investigations or highly volatile workloads, a shorter interval (e.g., 15 or 30 minutes) might be more appropriate to capture transient spikes. Be cautious not to set the interval too short (e.g., 5 minutes or less) as it can introduce overhead due to frequent data collection, potentially impacting database performance itself. Balance the need for granularity with the overhead cost.

Q3: My AWR report shows "CPU + CPU waiting for CPU" as the top event. What does this indicate, and what should I do?

A: If "CPU + CPU waiting for CPU" is the highest contributor to DB time, it means your database is primarily CPU-bound. This indicates that the database sessions are spending most of their time actively executing on the CPU or waiting for CPU resources. This could be due to:

Inefficient SQL: Queries that perform excessive logical I/O or complex calculations.
Insufficient CPU resources: The server simply doesn't have enough CPU cores or capacity to handle the workload.
High concurrency: Many sessions concurrently executing CPU-intensive tasks.

Your first step should be to investigate the "SQL Ordered by CPU Time" section to identify the most CPU-intensive SQL statements and optimize them. If SQL optimization yields limited results, consider scaling up your CPU resources or load balancing the workload.

Conclusion

The Oracle AWR report is an unparalleled diagnostic tool for understanding and resolving performance issues in complex database environments like Oracle Peoplesoft. By systematically diving into its sections—from the high-level load profile to the granular details of top SQL, wait events, and I/O statistics—DBAs and performance engineers can precisely pinpoint bottlenecks. Remember, AWR analysis is not a one-time task but an ongoing, iterative process. By establishing baselines, correlating data with other monitoring tools, and following best practices, you can proactively maintain the health and optimal performance of your Oracle databases, ensuring your critical applications run smoothly and efficiently.

Performance Tuning with Oracle AWR: Deep Dive into SQL, Waits & I/O