Resources

Oracle Wait Events: What Each One Means, the Usual Cause, and Where to Look

Oracle records what every session is waiting for, and the event name says most of what a DBA needs before any query. This page lists the events that appear at the top of real databases, by wait class, with the usual cause and the guide where one exists. Scops draws the Activity chart by wait class and samples the sessions every 5 seconds, so the history behind every event is there without AWR or the Diagnostics Pack, on any edition.

Commit

Event	What it means	Usual cause
log file sync	The session waits for LGWR to write its redo on commit.	Slow redo storage, commits per row, a SYNC standby, LGWR starved of CPU.

User I/O

Event	What it means	Usual cause
db file sequential read	A single-block read, usually through an index.	Slow storage, a working set larger than the buffer cache, an index range scan over too many rows.
db file scattered read	A multiblock read for a full table or fast full index scan.	A plan that scans where it should seek, or a missing index.
direct path read	A read that bypasses the buffer cache, straight into the session's memory.	Large table scans, parallel query, a serial scan on a big table in 11g and later.
direct path read temp / write temp	Sort or hash work spilling to the temp tablespace.	A sort or join larger than the PGA allows; the statement that spills is on the temp page.
read by other session	Another session is already reading the block this one needs.	Many sessions on the same hot blocks, often a single busy index.

Concurrency

Event	What it means	Usual cause
buffer busy waits	The block is in the cache and another session holds it in an incompatible mode.	Hot blocks: a sequence-fed index, a small table everyone updates, freelist contention in old tablespaces.
latch: cache buffers chains	Sessions queue for the latch that protects a hot block's chain.	A plan that visits the same few blocks millions of times, nested loops over a hot index.
library cache lock / latch: shared pool	Sessions wait to parse or to pin a cursor in the shared pool.	Hard parsing from literal SQL, DDL on a busy object, a shared pool that drains.
library cache: mutex X	A cursor's mutex is held while another session parses or loads it.	Version-count explosions, a storm of parses on one statement.
cursor: pin S wait on X	A session wants a shared pin on a cursor another session is building.	Hard parse of a popular statement, often after an invalidation.

Application

Event	What it means	Usual cause
enq: TX - row lock contention	The row is locked by another transaction that has not committed.	A long transaction, a forgotten session, two processes updating the same rows; the root blocker is on the blocking page.
enq: TM - contention	A table-level lock blocks the statement.	A foreign key without an index on the child table, or DDL during the day.
enq: TX - allocate ITL entry	No free transaction slot in the block header.	Many concurrent transactions on the same blocks with a small INITRANS.

Configuration

Event	What it means	Usual cause
log file switch (checkpoint incomplete)	The log cannot be reused until its checkpoint finishes.	Redo logs too small or too few for the rate, DBWR behind.
log file switch (archiving needed)	The next log is not archived yet, so nothing can write redo.	A full recovery area or archive destination, a slow archiver.
log buffer space	Sessions generate redo faster than LGWR empties the buffer.	A burst of redo on slow redo storage, a small log buffer.
free buffer waits	No clean buffer to read into; DBWR has not written the dirty ones yet.	A buffer cache too small for the write rate, slow datafile storage.
enq: HW - contention	Sessions queue to move a segment's high-water mark.	Many sessions inserting into one segment, LOB inserts, a datafile that autoextends in small steps.

Cluster (RAC)

Event	What it means	Usual cause
gc buffer busy acquire / release	The block is on its way across the interconnect for another session.	Hot blocks shared by instances, a sequence or index used from every node.
gc cr request / gc current request	The session waits for a block from another instance.	Normal at a small level; high with a slow interconnect or badly placed workloads.
gc cr block lost / gc current block lost	A block was sent and never arrived.	Interconnect packet loss, a NIC or switch problem; Radar's RAC detector watches these.

Scheduler, system I/O and network

Event	What it means	Usual cause
resmgr:cpu quantum	Resource Manager holds the session back from CPU.	A consumer group over its CPU share, common on cloud shapes and Exadata.
log file parallel write	LGWR's own write to the redo logs.	The ruler for log file sync: slow here means slow redo storage.
db file parallel write	DBWR writing dirty buffers to the datafiles.	Slow datafile storage; shows up as free buffer waits for the sessions.
control file sequential read	A read of the control file.	Backups, queries on V$ views that read the control file, many log switches.
SQL*Net more data to client / from dblink	Time spent moving rows over the network.	Large result sets, small array fetch sizes, a slow link to a remote database.
SQL*Net message from client	The session is idle, waiting for the application to send the next call.	Not a database problem. A large share of it in a slow application means the time is spent in the application, not in Oracle.

How the warnings work

Radar Engine is the detection layer of Scops. For waits it does not use thresholds: it learns the usual level of every wait class for every hour of every database, and reports a wave that is twice the hour before, a sudden event that takes a large share of the waits, slow single-block reads against the usual latency, slow commits against the usual commit time, and the configuration waits above by name. A daily pass finds a change of level in any class and ties it to a plan, parameter or statistics change on the same day. Catching Oracle Problems Before They Become Outages explains how it decides, and the detector reference lists every detector with its thresholds. For the problems that page a DBA, with the queries and the fix, see Oracle problems.