Reading Oracle AWR & ASH Reports: Wait-Event Troubleshooting

"The database is slow." Every DBA hears it, and the panic answer — start changing parameters — usually makes things worse. The disciplined answer is to let the database tell you what it's waiting on. Oracle's AWR and ASH reports do exactly that. After 18+ years of production firefighting across banking, pharma, and ERP systems, I've learned that almost every performance problem becomes obvious once you read these reports in the right order. Here is that order.

1. AWR vs ASH — Pick the Right Lens

• AWR aggregates activity between two snapshots (usually hourly). Use it for "the system was slow this afternoon" — trends and overall load.
• ASH samples every active session once per second. Use it for "it froze for 90 seconds at 2:14 pm" — short, sharp spikes an hourly average would smooth away.

Rule of thumb: AWR for the hour, ASH for the moment.

2. Generating the Reports

-- AWR (pick begin/end snapshot IDs when prompted)
@?/rdbms/admin/awrrpt.sql

-- ASH for a specific window
@?/rdbms/admin/ashrpt.sql

-- list recent snapshots
SELECT snap_id, begin_interval_time
FROM   dba_hist_snapshot
ORDER  BY snap_id DESC FETCH FIRST 12 ROWS ONLY;

Keep the AWR window tight — one hour that covers the problem beats a six-hour report that averages the pain away.

3. Start at the Top: DB Time and Load Profile

Don't scroll to the wait events first. Read the header. DB Time is the total time sessions spent working plus waiting. Compare it to elapsed time: 60 minutes elapsed with 600 minutes of DB Time means heavy concurrency.

The Load Profile tells you the shape of the workload — per second and per transaction:

• DB Time / sec — average active sessions; the single best "how busy" number.
• Logical & physical reads — is the work in memory or on disk?
• Hard parses / sec — high values scream "no bind variables".
• Redo size, commits, rollbacks — write intensity and transaction pattern.

4. The Heart of It: Top Foreground Wait Events

This section ranks where DB Time actually went. The top two or three events are your problem — everything below is noise. Read them as a sentence: "the database spent most of its time waiting on ____." Then fix that.

5. The Wait Events You'll Actually See

• db file sequential read — single-block (index) reads from disk. High = physical I/O from missing/poor indexes, small buffer cache, or a query reading too many blocks.
• db file scattered read — multi-block full-scan reads. High = full table scans that may need an index, or a deliberately large scan.
• log file sync — sessions waiting on COMMIT to flush redo. High = too-frequent commits or slow redo storage. Look at commits/sec and log writer latency.
• buffer busy waits — contention for the same blocks; often hot blocks or insufficient freelists/ASSM pressure.
• library cache / cursor: pin S wait on X — parsing contention, usually literal SQL instead of bind variables.
• gc buffer busy / gc cr block busy (RAC) — cross-instance block contention. Check interconnect latency and hot-block design.
• enq: TX - row lock contention — application locking; one session blocking others on the same row.
• CPU time at the top — not a wait at all; the database is busy computing. Find the SQL burning the CPU.

6. Follow the Wait to the SQL

A wait event tells you the symptom; the SQL section names the culprit. Read SQL ordered by Elapsed Time and by Gets, grab the worst SQL_ID, and pull its plan:

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

-- or the live plan from cursor cache
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

Now the story closes: high db file sequential read + a top SQL doing a huge INDEX RANGE SCAN into a TABLE ACCESS BY ROWID on millions of rows = the index or the query needs work.

7. ASH for the Transient Spike

When the freeze lasted seconds, query ASH directly for that window:

SELECT event, COUNT(*) AS samples
FROM   v$active_session_history
WHERE  sample_time BETWEEN TIMESTAMP '2026-06-12 14:13:00'
                       AND TIMESTAMP '2026-06-12 14:16:00'
GROUP  BY event
ORDER  BY samples DESC;

Each sample ≈ one second of one active session, so the counts approximate seconds of wait. You'll often see a single blocking session or one event dominate the spike that AWR completely averaged out.

8. Let ADDM Give You a Second Opinion

Oracle's Automatic Database Diagnostic Monitor reads the AWR data and writes findings in plain language — a useful sanity check, not a replacement for judgement:

@?/rdbms/admin/addmrpt.sql

Treat ADDM findings as leads to verify, not orders to follow blindly.

9. A Repeatable Troubleshooting Workflow

1. Scope it — exact time window and symptom. Hour-long? AWR. Seconds? ASH.
2. Read DB Time & Load Profile — how busy, in memory or on disk, parsing hard?
3. Read the top 2–3 wait events — name what the database waited on.
4. Map wait → SQL — find the statement responsible and read its plan.
5. Form one hypothesis, make one change — index, bind variables, commit frequency, memory.
6. Re-measure — confirm DB Time dropped before declaring victory.

10. Mistakes That Waste Hours

• Chasing buffer cache hit ratios — a near-100% ratio can hide a query doing millions of logical reads. Tune the SQL, not the ratio.
• Reports that are too wide — a 4-hour AWR averages the incident into invisibility.
• Changing many things at once — you'll never know which one helped (or hurt).
• Ignoring the application — row-lock contention and literal SQL are code problems, not parameter problems.
• Tuning without a baseline — keep a "good day" AWR to compare against.

Final Thoughts

Performance tuning stops being guesswork the moment you trust the instrumentation. AWR and ASH don't hide the answer — they hand it to you, if you read them top-down: how busy, waiting on what, caused by which SQL. Resist the urge to change parameters from memory. Measure, form one hypothesis, change one thing, measure again. That discipline is the entire difference between a DBA who calms an incident and one who prolongs it.

If you have a recurring slowdown, a month-end spike, or an AWR report you'd like a second pair of eyes on, let's talk. I diagnose and tune production Oracle performance for banking, pharma, and ERP systems and can help you find the real bottleneck quickly.

Related Articles