Oracle Wait Events, Decoded: The Half-Dozen

A wait event is not a mystery. It’s just a label for time a session spent not on CPU — blocked, waiting for a single block to come back from disk, for a commit to flush, for another session to let go of a lock. That’s all it is. Oracle gives each kind of waiting a name, sums the time, and ranks it. The skill isn’t memorizing the catalog — Oracle ships hundreds of events — it’s knowing the half-dozen that actually show up and what each one is telling you to go fix.

So ignore the catalog. On a real database the time piles up behind a handful of events: single-block reads, multi-block scans, direct path reads, the commit wait, and a small cluster of concurrency waits. Learn those, learn to rank them by DB time instead of by raw count, and learn which ones to throw away entirely — and you can read almost any performance problem from the wait interface alone. This targets Oracle 19c, with notes on 23ai/26ai, and there’s a free lab so you can induce each event and read its signature yourself.

The short version. A wait event is time a session spent off-CPU, waiting on a resource. DB time is CPU plus all non-idle waits; rank events by their share of it, not by how many times they fired. Six events carry most real problems: db file sequential read (index/rowid single-block I/O), db file scattered read (multi-block scans), direct path read (scans/sorts bypassing the cache), log file sync (commit), and the concurrency pair buffer busy waits / enq: TX - row lock contention. Idle waits like SQL*Net message from client top the raw counters and mean nothing — the database is waiting for you.

The wait interface, and why DB time is the ruler

Every session is, at any instant, in one of two states: running on a CPU, or waiting on a named event. Oracle records both. DB time is the sum across all foreground sessions of CPU time plus non-idle wait time — the total time the database spent doing user work. Because it sums sessions in parallel, it can exceed wall-clock: a 5-minute interval with 4 active sessions can show 20 minutes of DB time. Divide DB time by elapsed time and you get Average Active Sessions (AAS), the single most useful load number there is. AAS near your core count means CPU saturation; AAS dominated by one wait class points straight at the resource behind it.

The wait interface exposes this at four zoom levels:

The crucial discipline lives in how you rank. Oracle’s Top 10 Foreground Events section of an AWR report (older reports: “Top 5 Timed Events”) ranks events — plus DB CPU — by total time consumed and by % of DB time, with idle events already stripped out. So the top rows are your bottleneck. ASH gives you the same view live: because it samples active sessions every second, count(*) over a window approximates seconds of DB time, and grouping by wait_class or sql_id yields the breakdown.

And this is where most wait tuning goes wrong before it starts: the idle vs non-idle distinction. Oracle classifies every event into a wait class — User I/O, Commit, Concurrency, Configuration, Application, System I/O, Network, Cluster, and the rest — plus one class you must ignore: Idle. SQL*Net message from client, rdbms ipc message, the pmon/smon timers — these top the raw v$session_event totals on every database, because the server spends most of its life waiting for the next request. They are the database waiting for you, not a bottleneck. Oracle states it plainly: idle events “should be ignored when tuning, because they do not indicate the nature of the performance bottleneck.” Rank by time, exclude idle, and the noise falls away.

The half-dozen that carry real problems

You don’t need the catalog. You need these six. Each row tells you what the session is actually blocked on, the cause you should suspect first, and the first move that isn’t a guess.

Two of those rows hide a common trap, so read them again: buffer busy waits is in the Concurrency class and read by other session (its close cousin — you want a block another session is mid-read on) is in User I/O, while enq: TX - row lock contention is in Application. The class is a hint, not a label to obsess over. What matters is the move.

The I/O events: count vs latency

The User I/O events are where beginners burn the most time, usually by blaming storage. Resist it. db file sequential read is the single-block read — the “sequential” in the name refers to walking blocks in access-path order (down an index, then to the table by rowid), not a sequential scan. On a healthy OLTP system it is the top non-idle event and that is fine. It only becomes a problem two ways, and the wait histogram tells you which: too many reads (a SQL/plan/stats/clustering problem — fix the SQL, gather stats, repair the index, raise the clustering factor) or reads that are too slow (a storage problem — look at per-read latency; as a rule of thumb a sustained average above ~10ms on spinning disk warrants a look, flash should be low single-digit ms, and you always compare against your own baseline). Numerous-but-fast and few-but-slow are opposite fixes. The classic error is dropping indexes or forcing full scans to “cure” it — that just converts it into db file scattered read and more total I/O.

db file scattered read is the multi-block read — the fingerprint of a full scan (table or index fast full scan) that Oracle chose to route through the buffer cache. Its presence on an OLTP system that should be doing small indexed lookups is a red flag for a missing index or a bad plan — a SQL problem, not a hardware one. But here’s the modern wrinkle that confuses people: a large serial full scan usually does not produce scattered reads at all. Since 11g, Oracle can decide at runtime — based on internal size thresholds rather than a documented switch — to read large segments via direct path read straight into the PGA, bypassing the cache. So the absence of scattered reads on a big scan is normal, not a bug — and growing the buffer cache won’t route that scan back through it. For direct path read temp, the cause is almost never slow TEMP storage; it’s a work area too small for the sort or hash, spilling to disk. The cure is PGA sizing and fewer sorted/hashed rows, not faster disks.

The commit event: it’s a commit count problem

log file sync is the wait a session sits in after COMMIT, while LGWR flushes its redo to the online log and posts it back. The instinct — “high log file sync means slow redo disk, buy SSD” — is the single most common wait-event misdiagnosis, and it’s usually wrong. The tell is the comparison: log file sync (Commit class, a foreground wait) versus log file parallel write (System I/O class, LGWR’s background write). LFPW is just the pure I/O slice; LFS is LFPW plus LGWR scheduling, queueing, and the post-back round trip. If average LFS ≈ average LFPW, the redo I/O genuinely is the bottleneck — then faster, dedicated redo storage helps. But if LFS ≫ LFPW, the disk is fine; the time is going to commit frequency (row-by-row COMMIT in a loop, each one forcing a synchronous write + post) or to LGWR being starved of CPU. The fix for the common case is free: batch the commits. Move COMMIT out of the loop, use array DML, commit per logical unit of work — routinely a 10–100x reduction with zero hardware change. Always compare the two averages before you touch storage.

The concurrency cluster: serialize on the same thing

The Concurrency family is a set of “something else has it, wait your turn” events. buffer busy waits means a session can’t pin a block because another session already has it pinned mid-modification — intra-cache contention, not disk. The classic shape is a hot block: concurrent inserts to the same block or segment header, or a monotonically increasing primary key hammering the right-hand leaf of its index. Its User-I/O cousin read by other session is when you want a block another session is currently reading in — also a hot-block symptom, not a SAN problem. And latch: cache buffers chains is almost always a SQL problem wearing a latch mask: a statement doing far too many logical reads against a hot block. You don’t add latches and you rarely fix it by growing the cache — you fix the SQL.

enq: TX - row lock contention is different in kind: it’s an Application-class wait, pure application design. A session wants to modify a row another transaction has locked and not yet committed. No amount of database tuning fixes it — you walk the blocking tree (BLOCKING_SESSION, FINAL_BLOCKING_SESSION, v$lock with TYPE='TX'), find the row via ROW_WAIT_OBJ#/ROW_WAIT_ROW#, and fix the transaction that’s holding too long. The whole cluster shares one rule: pivot from the event to the object, SQL, or blocker — never tune the latch or the wait itself in isolation.

Triage: from high DB time to the move

The path is always the same. Start at DB time, read the ranked events, find the dominant wait class, and let the class pick the move.

flowchart TD
A([High DB time / AAS]) --> B[Read Top Timed Events<br/>rank by % DB time, idle excluded]
B --> C{Top event's<br/>wait class?}
C -- DB CPU --> D[Do less work:<br/>inefficient SQL, excess logical reads]
C -- User I/O --> E{Many-but-fast<br/>or few-but-slow?}
E -- Many/fast --> E1[Tune the SQL/plan/stats<br/>fix clustering or index]
E -- Few/slow --> E2[Storage: latency, hot LUNs,<br/>spread I/O]
C -- Commit --> F{avg LFS vs<br/>avg LFPW?}
F -- LFS ~ LFPW --> F1[Redo I/O: dedicated, fast<br/>redo storage]
F -- LFS >> LFPW --> F2[Batch commits / fix<br/>LGWR CPU starvation]
C -- Concurrency --> G[Pivot to the hot block / SQL:<br/>ROW_WAIT_OBJ#, high-LIO SQL]
C -- Application --> H[Walk the blocking tree:<br/>fix the transaction holding the lock]

What teams get wrong

• Tuning idle waits. SQL*Net message from client tops the raw counters on virtually every transactional database, because the server is waiting for the client. It is not a bottleneck. Oracle says ignore idle events — so ignore them. If that wait is huge, look at the application, the network, or a slow client, not the database.
• Chasing events instead of DB time. A million waits that sum to 2% of DB time are noise; one event at 60% is your headline. Rank by time consumed, never by wait count — and make sure TIMED_STATISTICS is on (it’s the default) so events are ordered by time, not occurrences.
• “db file sequential read is bad.” It’s the normal top event on healthy OLTP — index access doing its job. Don’t reflexively “fix” it; check the histogram first. Too-many is a SQL/plan problem; too-slow is storage. They’re opposite fixes, and dropping indexes to escape it usually makes total I/O worse.
• Treating log file sync as a storage problem. It’s usually a commit-count problem. Compare average LFS to average log file parallel write: only when they’re close is redo I/O the culprit. When LFS ≫ LFPW, you’re committing too often or starving LGWR of CPU — batching commits fixes the too-frequent case for free (CPU starvation needs CPU, not batching).

Want to see these for real? The wait-events/ lab in github.com/pyaroslav/oracle-labs induces four of these on Oracle Database Free and lets you read each signature directly in v$session_event — index access for db file sequential read, a buffered full scan for db file scattered read, a large scan that bypasses the cache for direct path read, and a row-by-row commit loop for log file sync (then move the COMMIT outside the loop and watch it collapse). Each drill flushes the cache, runs the workload, and re-queries the counter — the jump is the proof.

Where this fits

Wait events are the vocabulary; the AWR report is the sentence they form. Once you can name the half-dozen and rank them by DB time, the natural next step is reading them in context — alongside Load Profile, the SQL lists, and the segments that own the I/O. That’s the cornerstone: How to Read an AWR Report Without Drowning, where DB time runs the whole report and the top event routes you to the fix. And if the top event turns out to be a gc cluster wait, you’ve crossed into the other half of the stack — the interconnect and the RAC vs Data Guard decision tree, where Cluster-class waits live. Same method, different resource: name the wait, rank it by time, follow it to the object.