Oracle Wait Events.

In this post I'll mention the description of some common Oracle wait events.

Wait Event: 

An event can be defined as a particular function, or task, that the Oracle Kernel performs on behalf of the user session or its own BG process. Task such as read/write data block to and fro datafiles., waiting for latch acquisitions before accessing or manipulating data in the memory.

Every session that is connected to an Oracle instance needs resources to perform its task. A resource may be a data buffer, a latch, an enqueue (lock), a pin, or a database object. Whenever a session has to wait for something , the wait time is tracked and charged to the event that is associated with that wait.

For example, a session that needs an index block if is not in the SGA memory, will make a read call to the OS and waits for the delivery of the block. The wait time is charged to the db file sequential read event.

If a session may have completed the last instruction and is now idle, waiting for user input. In this case, the wait time is charged to the SQL*Net message from client event. 

In short, when a session is not using the CPU, it may be waiting for a resource, an action to complete. 

Hence, events that are associated with all such waits are known as wait events.

Buffer Busy Waits.

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

In order to guarantee that the reader session has a consistent image of the block with either all of the changes or none of the changes, the session modifying the block MARKS THE BLOCK HEADER WITH A FLAG   letting other sessions know that a change is taking place and to wait until the complete change is applied.

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. The Checkpoint (CKPT) process writes the checkpoint position in the online redo logs to the control files every 3 secs. Oracle uses this information during database recovery operation. Also, when you perform DML operations using either NOLOGGING or UNRECOVERABLE option, Oracle records the unrecoverable SCN in the control files.

DB File Parallel Read.

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 non-

contiguous single blocks from one or more datafiles.

DB File Single Write.
The db file single write event is occurred by DBWR. It occurs when Oracle is updating data file headers, typically during a checkpoint.

DB File Parallel Write.

This event belongs to the DBWR process, as it is the only process that writes the dirty blocks to the datafiles. 

DBWR compiles a set of dirty locks into a "WRITE BATCH". It issues multiple IO requests to write batch to the datafiles and waits on this event until the I/O requests are completed.

DB File Scattered Read.

This event happens when the session issues an I/O request to read multiple data blocks. The blocks read from the datafiles which are scattered into the buffer cache. The event typically occurs during full table scans or index fast full scans.

DB File Sequential Read.

The event occurs when reading from an index, rollback or undo segments, table access by rowid, rebuilding control files, dumping data file headers, or the data file headers. This is a single block read operation.

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 read I/O is normally used while accessing the temp segments that reside on the disks. These operations include sorts, parallel queries, and hash joins.

Direct Path Write.

The direct path write wait event when Oracle writes buffers from the session’s PGA to the datafiles. This operation is normally used when writing to temporary segments, in direct data loads (inserts with APPEND hint, or CTAS), or in parallel DML operations.

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 might be some other session is holding a lock on the resource. 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.

There are various types of enqueue to serialize access to various resources, uniquely identified by a two-character enqueue name. For example:

ST Enqueue for Space Management Transaction 

SQ Enqueue for Sequence Numbers

TX Enqueue for a Transaction

Free Buffer Waits.

This 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 sleeps and requests the latch again. The short sleep time is called “spin”. 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 Lock.
A session must acquire a library cache lock on an object to prevent other sessions from accessing it at the same 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.

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 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.

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.

Row Cache Lock.

The dictionary cache is known as row cache because it keeps the information at row level, as opposed to the buffer cache, which keeps the information at block level. The locks, which protect the definition of the data dictionary objects, are called row cache locks. Normally, DDL statements require row cache lock, and the session will wait for the row cache lock to lock the data dictionary information.