How to Analyze an Oracle AWR Report: A Step-by-Step Guide
You have generated an Oracle AWR report. Now what?
An AWR HTML report can be 50+ sections long and hundreds of pages when printed. Most DBAs open it, scroll through the first few sections, and either get lost in the details or miss the key insight entirely.
This guide gives you a systematic approach: a specific reading order, the key numbers to look at in each section, and how to connect the dots between what you see and the root cause of the problem.
Don't have an AWR report yet? See How to Generate an Oracle AWR Report first.
The Golden Rule: Read in Order, Stop When You Find It
AWR analysis is not about reading every section. It's about finding the dominant bottleneck as quickly as possible. The sections below are ordered by diagnostic value — read them in sequence and stop when you have a clear answer.
Most performance problems fall into one of four categories: 1. I/O problem — too much physical I/O, or slow storage 2. CPU problem — queries consuming too much CPU 3. Concurrency problem — sessions waiting for each other 4. Application problem — bad SQL, excessive parsing, locking
The wait profile tells you which category you're in. The SQL sections tell you which specific statement to fix.
Step 1: Check the Report Header
Before looking at any performance data, confirm you have the right report.
DB Name DB Id Instance Inst Num Startup Time Release
PRODDB 1234567890 PRODDB1 1 20-Jun-26 06:00 19.0.0.0.0
Host Name Platform CPUs Cores Sockets Memory (GB)
prodserver01 Linux x86 64-bit 8 8 1 64.00
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 4818 20-Jun-26 13:00:21 42 12.3
End Snap: 4819 20-Jun-26 14:00:35 45 11.8
Elapsed: 60.23 (mins)
DB Time: 245.67 (mins)Check: - Database name and instance — confirm this is the right database - Snap time — confirm the window covers the period you're investigating - Elapsed time — should be close to your snapshot interval (60 min) - DB Time — this is the critical number
Understanding DB Time
DB Time is the total time all foreground sessions spent doing work or waiting during the report window. It's the sum of CPU time plus all non-idle wait time across all sessions.
Average Active Sessions = DB Time / Elapsed TimeIn the example above: 245.67 / 60.23 = 4.08 average active sessions
This number tells you the scale of the workload: - On an 8-CPU server, 4 average active sessions is moderate — not stressed - On a 2-CPU server, 4 average active sessions means the CPUs are likely saturated - On a 200-connection OLTP system, 4 average active sessions is very low — something may have been idle
Compare DB Time against your baseline. A normal Monday morning might show 180 minutes of DB Time in a 60-minute window. If today shows 600 minutes, something changed.
Step 2: Top 10 Foreground Events — The Most Important Section
This is where every AWR analysis starts. Open the Top 10 Foreground Events by Total Wait Time section (it appears near the top of the report).
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Total Wait Wait % DB
Event Waits Time (sec) Avg(ms) time Wait Class
-------------------------------- ------ ----------- --------- ----- ----------
db file sequential read 823,412 9,842.3 11.95 66.8 User I/O
log file sync 42,981 891.4 20.74 6.1 Commit
db file scattered read 18,203 412.6 22.67 2.8 User I/O
CPU time 4,102.1 27.9
buffer busy waits 3,847 187.3 48.68 1.3 ConcurrencyWhat to Look At
% DB Time column — this is your primary focus. The events that account for the largest percentage of DB time are where your time went.
Avg(ms) column — the average wait time per wait. This tells you severity:
- db file sequential read at 1-2ms: healthy SSD storage
- db file sequential read at 20-50ms: slow spinning disk or storage contention
- log file sync at 1-5ms: normal
- log file sync at 20-100ms: redo I/O problem
CPU time row — CPU is not a "wait" but appears in this section. Compare CPU time vs. total wait time to understand whether you have a CPU-bound or wait-bound workload.
Reading the Pattern
| What you see | What it means |
|---|---|
db file sequential read dominates |
Index-driven I/O — check Top SQL by physical reads |
db file scattered read dominates |
Full table scans — check for missing indexes |
log file sync dominates |
Commit frequency or slow redo I/O |
buffer busy waits or latch: dominates |
Concurrency hotspot — specific object contention |
enq: TX - row lock dominates |
Application locking — find the blocking session |
| CPU dominates (> 60-70% of DB time) | CPU-intensive SQL — check Top SQL by CPU |
| Multiple events roughly equal | Mixed workload — no single bottleneck |
In the example above, db file sequential read at 66.8% of DB time is the clear story. This is an I/O problem driven by index access. The next step is finding which SQL is causing it.
For a detailed explanation of each wait event, see Oracle Wait Events Explained.
Step 3: Load Profile — Is This Normal?
The Load Profile section shows activity rates per second and per transaction:
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~ --------------- --------------- -------- --------
DB Time(s): 4.08 0.02
DB CPU(s): 1.14 0.01
Logical read (blocks): 4,821.3 28.32
Block changes: 123.4 0.72
Physical reads (blocks): 892.1 5.24
Physical writes (blocks): 12.3 0.07
Read IO requests: 412.8 2.43
Write IO requests: 8.1 0.05
User calls: 103.2 0.61
Parses: 201.4 1.18
Hard parses: 2.1 0.01
Sorts: 28.3 0.17
Logons: 0.3 0.00
Executes: 852.1 5.01
Rollbacks: 0.2 0.00
Transactions: 17.0Key Ratios to Calculate
Parse ratio:
Hard parses / Parses = 2.1 / 201.4 = 1.0%cursor_sharing parameter and look for SQL not using bind variables.
Physical read ratio:
Physical reads / Logical reads = 892.1 / 4821.3 = 18.5%Commit frequency:
Transactions per second = 17.0log file sync counts in the wait events. If you have 17 transactions/second and 42,981 log file sync waits in a 60-minute window (3,600 seconds × 17 = ~61,200 transactions), most transactions are generating one sync each. If log file sync count is much higher, the application may be committing multiple times per transaction.
Rollback ratio:
Rollbacks / (Transactions + Rollbacks) = 0.2 / (17.0 + 0.2) = 1.2%Step 4: SQL Statistics — Find the Culprit
After understanding the wait profile and load, pivot to the SQL sections to identify the specific statements responsible.
AWR provides multiple SQL rankings. The most useful are:
SQL Ordered by Elapsed Time
SQL ordered by Elapsed Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Elapsed Elapsed Time (s) Executions Elapsed Time per Exec (s)
SQL Id Module
------------ -----------
abc123def456 8,231.4 12,847 0.641
SELECT c.customer_id, o.order_date, p.product_name
FROM customers c JOIN orders o ON ...Elapsed time = total wall-clock time all executions of this SQL consumed. This is your primary ranking for "what made users wait."
High elapsed time with high execution count = a frequently run query that's a little slow. Each execution may feel fast to the user, but the aggregate cost is huge.
High elapsed time with low execution count = a slow query. Each execution takes a long time.
SQL Ordered by CPU Time
Shows which SQL consumed the most CPU. If CPU dominates your wait profile, start here instead of elapsed time.
SQL Ordered by Gets (Logical Reads)
Shows which SQL read the most blocks from the buffer cache. High logical reads usually mean large table scans or inefficient index usage.
SQL Ordered by Reads (Physical Reads)
Shows which SQL generated the most physical I/O. If db file sequential read or db file scattered read dominate your wait profile, start here.
How to Use the SQL Sections
- Take the top 3 SQL IDs from the section that matches your wait profile
- Click the SQL ID in the HTML report to see the full SQL text
- Check the execution plan in the SQL Plan section (or run
DBMS_XPLAN.DISPLAY_CURSOR) - Look for full table scans, high-cost operations, or unexpected plan choices
-- Get the current execution plan for a SQL ID
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR('abc123def456', NULL, 'ALLSTATS LAST')
);
-- Get the historical plan from AWR
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_AWR('abc123def456')
);Step 5: Instance Activity Statistics
This section provides raw cumulative statistics for the period. The most useful ones to check:
-- You can query AWR directly for specific stats
SELECT stat_name, value
FROM dba_hist_sysstat s
JOIN dba_hist_snapshot sn ON sn.snap_id = s.snap_id
WHERE sn.snap_id BETWEEN 4818 AND 4819
AND stat_name IN (
'physical reads',
'physical reads direct',
'session logical reads',
'parse count (hard)',
'parse count (total)',
'execute count',
'redo size',
'user commits',
'user rollbacks'
)
ORDER BY stat_name;Key stats to check in the report:
| Statistic | What to Look For |
|---|---|
parse count (hard) vs parse count (total) |
Hard parse ratio > 1% = cursor sharing issue |
physical reads direct vs physical reads |
High direct reads = lots of parallel/sort activity |
redo size |
Correlates with DML activity — cross-check with commit count |
table scans (long tables) |
Count of full scans on large tables |
sorts (disk) |
Disk sorts indicate PGA is too small |
Step 6: Segments — Which Objects Are the Problem?
The Segments sections identify the specific tables and indexes causing the most activity.
Segments by Physical Reads — which objects are being read from disk most often. Cross-reference with your Top SQL findings.
Segments by Buffer Busy Waits — which objects have the most block contention. If buffer busy waits appeared in your Top 10 events, this section tells you exactly which table or index has the hotspot.
Segments by Row Lock Waits — which tables have the most row locking. If enq: TX appeared in your Top 10, start here.
Common AWR Patterns and Their Diagnoses
Pattern 1: High db file sequential read + High Physical Reads in Top SQL
Story: Lots of index-driven I/O. One or more SQL statements are reading too many blocks through indexes.
Investigation:
1. Find the top SQL by physical reads
2. Check the execution plan — is it using the right index?
3. Check the index columns — is the query selective enough?
4. Check average db file sequential read wait time — if > 10ms, storage is slow
Typical fixes: Add a composite index, rewrite the query to be more selective, increase buffer cache.
Pattern 2: High log file sync with High Commit Count
Story: Too many commits. The application is committing row-by-row or after every small operation.
Investigation:
1. Check user commits in Instance Activity — transactions per second
2. Check log file sync average wait time — if < 5ms, the commits are fast but too frequent; if > 10ms, the redo I/O path is slow
3. Find the application module generating the commits in ASH
Typical fixes: Batch commits in application code, move redo logs to faster storage.
Pattern 3: High CPU with Fast Individual Queries
Story: Many fast queries adding up to high CPU. No single query dominates elapsed time, but CPU is saturated.
Investigation:
1. Check SQL ordered by CPU — look for queries with billions of logical reads
2. Check parse count (hard) — high hard parses consume CPU on query compilation
3. Check for queries not using bind variables (identical SQL text with different literal values)
Typical fixes: Add indexes to reduce logical reads, implement cursor sharing, convert literals to bind variables.
Pattern 4: High buffer busy waits or latch: cache buffers chains
Story: A hotspot — many sessions competing for the same block.
Investigation: 1. Check Segments by Buffer Busy Waits — find the object 2. Use ASH to find the specific block and the SQL accessing it 3. Determine if it's an insert hotspot (sequence-based) or a read hotspot (small lookup table)
Typical fixes: Hash partitioning, reverse key index, result caching for lookup tables.
Pattern 5: Low DB Time Despite User Complaints
Story: AWR shows nothing wrong, but users say the system was slow.
Possible explanations: - The problem was shorter than the snapshot interval (use ASH instead) - The problem is in the application tier, not the database - The snapshot was taken at the wrong time - Network latency between application and database
Investigation: Check ASH for the specific time window, check application logs, check network metrics.
The AWR Analysis Checklist
Use this checklist for every AWR analysis:
□ 1. Confirm correct database, instance, and time window
□ 2. Note DB Time and calculate Average Active Sessions
□ 3. Compare DB Time to baseline
□ 4. Read Top 10 Foreground Events — note top 2-3 by % DB time
□ 5. Note Avg(ms) for top wait events — is it the count or the duration?
□ 6. Check Load Profile — parse ratio, commit frequency, physical/logical read ratio
□ 7. Go to SQL section matching the top wait event
□ 8. Note top 3 SQL IDs and their resource consumption
□ 9. Check execution plans for top SQL IDs
□ 10. Check Segments section for the matching statistic
□ 11. Form hypothesis: "The problem is X, caused by SQL Y on object Z"
□ 12. Validate with a second data source (ASH, v$ views, application logs)Frequently Asked Questions
How long does it take to analyze an AWR report? An experienced DBA can usually identify the primary bottleneck in 5-10 minutes by reading only the key sections. A thorough analysis covering secondary issues takes 30-60 minutes. Junior DBAs often spend hours because they read every section linearly.
What if there is no obvious top wait event? A flat wait profile (many events each at 5-10% DB time) usually indicates a mixed workload with no single bottleneck. In this case, focus on the SQL ordered by elapsed time and look for optimization opportunities in the highest-consuming statements.
Can I compare two AWR reports directly?
Yes. Oracle provides the AWR Compare Periods report (awrddrpt.sql), which generates a side-by-side comparison of two time windows. This is invaluable for before/after comparisons after a change, or for comparing a slow period against a fast one.
What if the top SQL changes between snapshots?
AWR captures a rolling history. Use dba_hist_sqlstat to see how a specific SQL ID has performed over time:
SELECT snap_id, executions, elapsed_time/1000000 AS elapsed_sec
FROM dba_hist_sqlstat
WHERE sql_id = 'abc123def456'
ORDER BY snap_id;Should I always start with AWR or ASH? Start with AWR if the incident lasted more than 30 minutes or you're doing periodic performance review. Start with ASH if the incident was short (under 30 minutes) or you need to identify a specific point in time. Often you use both together — AWR for the overall picture, ASH to drill into the specific moment.
Analyze AWR Reports Automatically
Working through an AWR report systematically takes experience — knowing what to look for, how to correlate sections, and what constitutes an anomaly vs. normal behaviour for your specific workload.
DBA Copilot automates this process. Upload your AWR HTML report and the AI reads every relevant section, identifies the dominant wait events and top SQL, correlates the findings, and generates a plain-language diagnosis with prioritised recommendations.
No direct database connection required. No need to share credentials. Works with Oracle 11g through 23ai.
Try DBA Copilot free — no credit card required
New to AWR? Start with What is Oracle AWR: Complete Guide
Need to generate an AWR report first? See How to Generate an Oracle AWR Report
Understanding wait events? See Oracle Wait Events Explained