Common Wait Events in Oracle

buffer busy waits

The buffer busy waits event occurs when a session wants to access a data block in the buffer cache that is currently in use by some other session. The other session is either reading the same data block into the buffer cache from the datafile, or it is modifying the one in the buffer cache.

On Oracle 9, the main reasons for buffer busy waits are

1) IO read contention (only Oracle 9i and below)

2) Insert Block Contention on Tables or Indexes

3) Rollback Segment Contention

control file parallel write

The control file parallel write event occurs when the session waits for the completion of the write requests to all of the control files. The server process issues these write requests in parallel. Starting with Oracle 8.0.5, the CKPT process writes the checkpoint position in the online redo logs to the control files every three seconds. Oracle uses this information during database recovery operation. Also, when you perform DML operations using either the NOLOGGING or UNRECOVERABLE option, Oracle records the unrecoverable SCN in the control files. The Recovery Manager (RMAN) records backup and recovery information in control files.

db file parallel read

Contrary to what the name suggests, the db file parallel read event is not related to any parallel operation—neither parallel DML nor parallel query. This event occurs during the database recovery operation when database blocks that need changes as a part of recovery are read in parallel from the datafiles. This event also occurs when a process reads multiple noncontiguous single blocks from one or more datafiles.

db file parallel write

Contrary to what the name suggests, the db file parallel write event is not related to any parallel DML operation. This event belongs to the DBWR process, as it is the only process that writes the dirty blocks to the datafiles. The blocker is the operating system I/O subsystem. This can also have an impact on the I/O subsystem in that the writes may impact read times of sessions reading from the same disks.

db file scattered read

The db file scattered read event is posted by the session when it issues an I/O request to read multiple data blocks. The blocks read from the datafiles are scattered into the buffer cache. These blocks need not remain contiguous in the buffer cache. The event typically occurs during full table scans or index fast full scans. The initialization parameter, DB_FILE_MULTIBLOCK_READ_COUNT determines the maximum number of data blocks to read.

db file sequential read

The db file sequential read wait event occurs when the process waits for an I/O completion for a sequential read. The name is a bit misleading, suggesting a multiblock operation, but this is a single block read operation. The event gets posted when reading from an index, rollback or undo segments, table access by rowid, rebuilding control files, dumping datafile headers, or the datafile headers.

db file single write

The db file single write event is posted by DBWR. It occurs when Oracle is updating datafile headers, typically during a checkpoint. You may notice this event when your database has an inordinate number of database files.

direct path read

The direct path read event occurs when Oracle is reading data blocks directly into the session’s PGA instead of the buffer cache in the SGA. Direct reads may be performed in synchronous I/O or asynchronous I/O mode, depending on the hardware platform and the value of the initialization parameter, DISK_ASYNCH_IO. Direct read I/O is normally used while accessing the temporary segments that reside on the disks. These operations include sorts, parallel queries, and hash joins.

direct path write

The direct path write wait event is just an opposite operation to that of direct path read. Oracle writes buffers from the session’s PGA to the datafiles. A session can issue multiple write requests and continue processing. The OS handles the I/O operation. If the session needs to know if the I/O operation was completed, it will wait on direct path write event.

enqueue

An enqueue is a shared memory structure used by Oracle to serialize access to the database resources. The process must acquire the enqueue lock on the resource to access it. The process will wait on this event if the request to acquire the enqueue is not successful because some other session is holding a lock on the resource in an incompatible mode. The processes wait in queue for their turn to acquire the requested enqueue. A simple example of such an enqueue wait is a session waiting to update a row when some other session has updated the row and not yet committed (or rolled back) its transaction and has a lock on it in an exclusive mode.

free buffer waits

The free buffer waits event occurs when the session cannot find free buffers in the database buffer cache to read in data blocks or to build a consistent read (CR) image of a data block. This could mean either the database buffer cache is too small, or the dirty blocks in the buffer cache are not getting written to the disk fast enough. The process will signal DBWR to free up dirty buffers but will wait on this event.

latch free

The latch free wait occurs when the process waits to acquire a latch that is currently held by other process. Like enqueue, Oracle uses latches to protect data structures. One process at a time can either modify or inspect the data structure after acquiring the latch. Other processes needing access to the data structure must wait till they acquire the latch. Unlike enqueue, processes requesting latch do not have to wait in a queue. If the request to acquire a latch fails, the process simply waits for a short time and requests the latch again. The short wait time is called “spin”. If the latch is not acquired after one or more spin iterations, the process sleeps for a short time and tries to acquire the latch again, sleeping for successively longer periods until the latch is obtained. 

library cache pin

The library cache pin wait event is associated with library cache concurrency. It occurs when the session tries to pin an object in the library cache to modify or examine it. The session must acquire a pin to make sure that the object is not updated by other sessions at the same time. Oracle posts this event when sessions are compiling or parsing PL/SQL procedures and views.

library cache lock

The library cache lock event is also associated with library cache concurrency. A session must acquire a library cache lock on an object handle to prevent other sessions from accessing it at the same time, or to maintain a dependency for a long time, or to locate an object in the library cache.

log buffer space

The log buffer space wait occurs when the session has to wait for space to become available in the log buffer to write new information. The LGWR process periodically writes to redo log files from the log buffer and makes those log buffers available for reuse. This wait indicates that the application is generating redo information faster than LGWR process can write it to the redo files. Either the log buffer is too small, or redo log files are on disks with I/O contention.

log file parallel write

The log file parallel write wait occurs when the session waits for LGWR process to write redo from log buffer to all the log members of the redo log group. This event is typically posted by LGWR process. The LGWR process writes to the active log file members in parallel only if the asynchronous I/O is in use. Otherwise, it writes to each active log file member sequentially.

log file sequential read

The log file sequential read wait occurs when the process waits for blocks to be read from the online redo logs files. The ARCH process encounters this wait while reading from the redo log files.

log file switch (archiving needed)

The log file switch wait indicates that the ARCH process is not keeping up with LGWR process writing to redo log files. When operating the database in archive log mode, the LGWR process cannot overwrite or switch to the redo log file until the ARCH process has archived it by copying it to the archived log file destination. A failed write to the archive log file destination may stop the archiving process. Such an error will be reported in the alert log file.

log file switch (checkpoint incomplete)

The log file switch wait indicates that the process is waiting for the log file switch to complete, but the log file switch is not possible because the checkpoint process for that log file has not completed. You may see this event when the redo log files are sized too small.

log file switch completion

This wait event occurs when the process is waiting for log file switch to complete.

log file sync

When a user session completes a transaction, either by a commit or a rollback, the session’s redo information must be written to the redo logs by LGWR process before the session can continue processing. The process waits on this event while LGWR process completes the I/O to the redo log file.

SQL*Net message from client

This wait event is posted by the session when it is waiting for a message from the client to arrive. Generally, this means that the session is sitting idle. Excessive wait time on this event in batch programs that do not interact with an end user at a keyboard may indicate some inefficiency in the application code or in the network layer. However, the database performance is not degraded by high wait times for this wait event, because this event clearly indicates that the perceived database performance problem is actually not a database problem.

SQL*Net message to client

This wait event is posted by the session when it is sending a message to the client. The client process may be too busy to accept the delivery of the message, causing the server session to wait, or the network latency delays may be causing the message delivery to take longer.