How to Read an AWR Report in Oracle

When an Oracle database slows down, the first place a DBA should look is the AWR report — a detailed snapshot of what’s happening inside the system.

It reveals where time is spent, which queries are consuming resources, and whether the database is healthy or struggling.

In this post, we’ll walk through the key sections to check so you can quickly tell if your database is performing well — and where to focus your tuning efforts.

Below is the order I (and most senior DBAs) follow when analyzing an AWR report:

🔎 AWR Analysis: Step-by-Step Checklist

1. 📊 Report Summary (Top Section)

This gives you a quick overview of database health.

• DB Time vs Elapsed Time
  • Shows total time spent on database activity.
  • 🔍 If DB Time ≈ Elapsed Time × #CPU cores, DB load is heavy.
  • Rule of thumb:
    • ✅ Healthy: DB Time per second is similar to CPU count (e.g., ~8 sec/s for 8 CPUs)
    • ❌ Bad: Much higher → indicates bottlenecks.
• Average Active Sessions (AAS)
  • DB Time / Elapsed Time
  • ✅ Healthy: AAS ≤ number of CPU cores.
  • ❌ Bad: AAS > CPUs → system is CPU-bound or waiting on something.
• %DB CPU
  • Percentage of DB time spent on CPU.
  • ✅ 60–90% means most time is spent doing useful work.
  • ❌ Too low → most time spent waiting (IO, locks, latches, etc.).

2. ⚙️ Load Profile

This tells you how “busy” your DB is.

• DB Calls / Transactions / Execs per second — Gives workload idea.
• Redo size per second — High redo = heavy DML.
• Logical Reads per sec — Reflects buffer cache activity.
• Physical Reads per sec — High values may mean poor caching or full scans.
• Parses per second — Too many parses = poor cursor sharing (look for high % of hard parses).

✅ Healthy signs:

• Low hard parse rate (<5%)
• Logical reads >> physical reads
• Parses per exec < 0.1 (meaning most statements are reused)

3. 🔥 Top 5 Timed Foreground Events

This is the heart of AWR analysis.

• It shows where most of your DB time is spent.
• Look for the top wait events and check their nature:

✅ Healthy:

• CPU time at or near the top
• Wait events are low in total time (<20% total DB time)

❌ Bad:

• Non-CPU waits dominate
• One wait event uses >30–40% DB time → investigate root cause.

4. 🧠 Instance Efficiency Percentages

This section is often misunderstood, but a few key metrics are useful:

• Buffer Hit %: Should be > 90%
• Library Hit %: Should be > 95%
• Soft Parse %: > 95% (if low → check shared pool or cursor sharing)
• Latch Hit %: > 99%

These are general guidelines — bad numbers here mean inefficient memory usage or SQL reuse problems.

5. 🔍 Top SQL Statements

Check the Top SQL by:

• Elapsed time
• CPU time
• Buffer gets
• Physical reads
• Executions

✅ Healthy:

• No single SQL dominates >30–40% of DB time.

❌ Problem:

• One or two SQL statements consume most resources → focus tuning here first.

6. 🧰 IO and File Statistics

Check Tablespace IO, File IO, and Segments by Physical Reads:

• Look for imbalances: one datafile or tablespace doing 90% of IO → possible hotspot.
• High read times (ms per read > 10 ms) → IO subsystem is slow.

7. 🧵 Wait Class Breakdown

This gives a high-level view of where the time is going:

• CPU + User I/O: Typically OK (normal workload)
• Concurrency, Commit, Configuration: Possible tuning needed
• System I/O or Network: Possible infrastructure issues

✅ Healthy:

• Most time spent in CPU or User I/O.

❌ Bad:

• High percentages in Concurrency, Commit, or Configuration wait classes.

8. 📈 OS Statistics

Check CPU utilization and run queue:

• %User + %Sys CPU < 90% → CPU OK
• If Run Queue > #CPUs → CPU saturation

Also check memory and swap usage.

✅ Final Quick Health Checklist (DBA’s Shortcut)

✅ In summary:
A database is doing well if:

• DB time and AAS are aligned with CPU capacity.
• CPU is the main “wait”.
• No single SQL or wait event dominates.
• Hit ratios are high and parse rates are low.
• IO latency and contention are minimal.