Oracle-Wait Events and Potential Causes

buffer busy waits

Buffer cache, DBWR

Depends on buffer type. For example, waits for an index block may be caused by a primary key that is based on an ascending sequence.

Examine V$SESSION while the problem is occurring to determine the type of block in contention.

free buffer waits

Buffer cache, DBWR, I/O

Slow DBWR (possibly due to I/O?)

Cache too small

Examine write time using operating system statistics. Check buffer cache statistics for evidence of too small cache.

db file scattered read

I/O, SQL statement tuning

Poorly tuned SQL

Slow I/O system

Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.

db file sequential read

I/O, SQL statement tuning

Poorly tuned SQL

Slow I/O system

Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.

enqueue waits (waits starting with enq:)

Locks

Depends on type of enqueue

Look at V$ENQUEUE_STAT.

library cache latch waits:library cache, librarycache pin, and librarycache lock

Latch contention

SQL parsing or sharing

Check V$SQLAREA to see whether there are SQL statements with a relatively high number of parse calls or a high number of child cursors (column VERSION_COUNT). Check parse statistics in V$SYSSTAT and their corresponding rate for each second.

log buffer space

Log buffer, I/O

Log buffer small

Slow I/O system

Check the statistic redo buffer allocation retries inV$SYSSTAT. Check configuring log buffer section in configuring memory chapter. Check the disks that house the online redo logs for resource contention.

log file sync

I/O, over- committing

Slow disks that store the online logs

Un-batched commits

Check the disks that house the online redo logs for resource contention. Check the number of transactions (commits + rollbacks) each second, from V$SYSSTAT.