Chapter 3: Common Wait Events
You learned about the components of the OWI. Now, you are ready to explore some of the common Oracle wait events. Of the numerous wait events, only a few common wait events are of importance to the DBA. The wait events in your database will depend on how the application works in your database environment. You should familiarize yourself with the wait events you see in your environment. Many environments will have a handful of the wait events described in the following sections.
The section titled ‚“Common Wait Events in Oracle Real Application Clusters Environment ‚½ discusses the wait events seen in the RAC environment.
Introduction to Common Wait Events
We will describe what the event means, what information Oracle provides you in the additional wait parameters, P1, P2 and P3 in the V$SESSION_WAIT view, the extended SQL trace file and the V$SESSION view in Oracle Database 10 g Release 1. We will also discuss the wait time, or the timeout value for these wait events. Subsequent chapters will discuss in detail the common causes of these events and actions to take to minimize them.
We will also discuss the importance of tracking CPU usage statistics in conjunction with the wait event information.
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.
In order to guarantee that the reader session has a coherent 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.
Prior to Oracle Database 10 g Release 1, buffer busy waits event was posted by the session when it had to wait for the other session to read the same data block into the buffer cache. However, starting with Oracle Database 10 g Release 1, such waits are now posted as read by other session event. The buffer busy waits event denotes the waits by a session for data block change completion by some other session.
Oracle Database 10 g Release 1 has another event titled buffer busy . Do not confuse this event with buffer busy waits . The buffer busy event is posted by sessions accessing cached metadata in a database using Automatic Storage Management (ASM).
Although the view V$WAITSTAT is not a component of Oracle Wait Interface, it provides valuable wait statistics for each class of buffer. The most common buffer classes that encounter buffer busy waits are data blocks, segment header, undo blocks, and undo header.
The following example shows a sample output from querying V$WAITSTAT view:
select *
from v$waitstat
where count > 0;
CLASS COUNT TIME
------------------ ---------- ----------
data block 4170082 1668098
segment header 116 98
undo header 916 1134
undo block 2087 1681
Wait Parameters
Wait parameters for buffer busy waits are described here:
-
P1 From Oracle8 Database onwards, P1 shows the absolute file number where the data block in question resides.
-
P2 The actual block number the processes need to access.
-
P3 Prior to Oracle Database 10 g Release 1, this is a number indicating the reason for the wait. Oracle posts this event from multiple places in the kernel code with different reason code. The value of this reason code depends on the Oracle release ‚ that is, the reason code changed from pre-Oracle8 Database to Oracle9 i Database. Oracle Database 10 g Release 1 does not use reason code anymore, and P3 refers to the class in the V$WAITCLASS view in Oracle Database 10 g . Chapter 6 has more details about how to interpret this information.
For Oracle releases prior to Oracle Database 10 g Release 1, Table 3-1 lists the reason codes and their descriptions. The reason code in parentheses applies to Oracle releases 8.1.5 and below.
| Reason Code | Description |
|---|---|
| 100 (1003) | The blocking session is reading the block into cache, most likely the undo block for rollback; the waiting session wants exclusive access to create a new block for this information. |
| 110 (1014) | The blocked or waiting session wants to access the current image of the block in either shared (to read) or exclusive (to write) mode, but the blocking session is reading the block into cache. |
| 120 (1014) | The blocked session wants to access the block in current mode; the blocking session is reading the block in the cache. This happens during buffer lookups. |
| 130 (1013) | One or more sessions want to access the same block, but it is not in the buffer. One session will perform the I/O operation and post either a db file sequential read or a db file scattered read event, while the waiting sessions will post buffer busy waits with this reason code. |
| 200 (1007) | The blocking session is modifying the block in the cache; the waiting session wants exclusive access to create a new block. |
| 210 (1016) | The blocking session is modifying the block, while the blocked session wants the current version of the block in exclusive mode. This happens when two processes want to update the same block. |
| 220 (1016) | The blocking session is modifying the block, while the blocked session wants to access the block in current mode during buffer lookup. |
| 230 (1010) | The blocking session is modifying the block, while the blocked session wants shared access of a coherent version of the block. |
| 231 (1012) | The blocking session is modifying the block, while the blocked session is the reading current version of the block when shared access of a coherent version of the block was wanted. |
Wait Time
100cs or 1 second
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.
There is no blocking session for control file parallel write . The session is blocked waiting on the OS and its I/O subsystem to complete the write to all control files. The session performing the write to the control files will be holding the CF enqueue so other sessions may be waiting on this enqueue. If systemwide waits for this wait event are significant, this indicates either numerous writes to the control file, or slow performance of writes to the control files.
Wait Parameters
Wait parameters for control file parallel write are described here:
-
P1 Number of control files the server process is writing to
-
P2 Total number of blocks to write to the control files
-
P3 Number of I/O requests
Wait Time
The actual elapsed time to complete all I/O requests.
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.
Wait Parameters
Wait parameters for db file parallel read are described here:
-
P1 Number of files to read from
-
P2 Total number of blocks to read
-
P3 Total number of I/O requests (the same as P2 since multiblock read is not used)
Wait Time
No timeouts. The session waits until all of the I/Os are completed.
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.
DBWR compiles a set of dirty blocks into a ‚“write batch ‚½. It issues multiple I/O requests to write the write batch to the datafiles and waits on this event until the I/O requests are completed. However, when using asynchronous I/O, DBWR does not wait for the whole batch write to complete, it waits only for a percentage of the batch to complete before pushing the free buffers back onto the LRU chain so that they can be used. It may also issue more write requests.
Wait Parameters
Wait parameters for db file parallel write are described here:
-
P1 Number of files to write to
-
P2 Total number of blocks to write
-
P3 From Oracle9 i Release 9.2 onward, P3 shows the timeout value in centiseconds to wait for the I/O completion; prior to this release, P3 indicates the total number of I/O requests, which is the same as P2 (blocks).
Wait Time
No timeouts. The session waits until all the I/Os are completed.
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.
Waiting on datafile I/O completion is normal in any Oracle database. The presence of this wait event does not necessarily indicate a performance problem. However, if the time spent waiting for multiblock reads is significant compared to other waits, you must investigate the reason for it.
Wait Parameters
Wait parameters for db file scattered read are described here:
-
P1 File number to read the blocks from
-
P2 Starting block number to begin reading
-
P3 Number of blocks to read
Wait Time
No timeouts. The session waits until all of the I/Os are completed to read specified number of blocks.
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.
Waiting on datafile I/O completion is normal in any Oracle Database. The presence of this wait event does not necessarily indicate a performance problem. However, if the time spent waiting for single block reads is significant compared to other waits, you must investigate the reason for it.
Wait Parameters
No timeouts. Wait parameters for db file sequential read are described here:
-
P1 File number to read the data block from
-
P2 Starting block number to read
-
P3 1 in most cases, but for temporary segments can be more than 1
Wait Time
No timeouts. The session waits until the I/O is completed to read the block.
| Note ‚ | In a paper titled ‚“Why are Oracle ‚ s Read Events ‚Named Backwards ‚ ? ‚½ Jeff Holt explains how the events db file sequential read and db file scattered read got their names . Basically, the db file sequential read happens when the buffer cache memory locations that receive data from disk are contiguous. In the case of db file scattered read those are not guaranteed to be contiguous . The paper is available at www.hotsos.com. |
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.
Wait Parameters
Wait parameters for db file single write are described here:
-
P1 File number to write to
-
P2 Starting block number to write to
-
P3 The number of blocks to write, typically 1
Wait Time
No timeouts. Actual time it takes to complete the I/O 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 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.
The number of waits and time waited for this event are somewhat misleading. If the asynchronous I/O is not available, the session waits till the I/O completes. But these are not counted as waits at the time the I/O request is issued. The session posts a direct path read wait event when accessing the data after the completion of the I/O request. In this case, the wait time will be negligibly small.
If the asynchronous I/O is available and in use, then the session may issue multiple direct path read requests and continue to process the blocks that are already cached in the PGA. The session will register direct path read wait event only when it cannot continue processing because the required block has not been read into the buffer. Therefore, the number of read requests may not be the same as the number of waits. Due to these anomalies, it is unlikely that you will see this wait event reported in V$SYSTEM_EVENT and V$SESSION_EVENT views.
Starting from Oracle Release 8.1.7 there is a separate direct path read (lob) event for reading LOB segments.
Wait Parameters
Wait parameters for direct path read are described here:
-
P1 Absolute file number to read from
-
P2 Starting block number to read from
-
P3 Number of blocks to read
Wait Time
No timeouts. Actual time until the outstanding I/O request completes.
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.
The direct path write operation is normally used when writing to temporary segments, in direct data loads ( inserts with APPEND hint, or CTAS), or in parallel DML operations.
As with the direct path write event, the number of waits and time waited for this event can be misleading when asynchronous I/O is in use.
Starting from Oracle 8.1.7 there is a separate direct path write (lob) event for writing to uncached LOB segments.
Wait Parameters
Wait parameters for direct path write are described here:
-
P1 Absolute file number to write to
-
P2 Starting block number to write from
-
P3 Number of blocks to write
Wait Time
No timeouts. Actual time until the outstanding I/O request completes.
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.
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
Note ‚ In Oracle Database 10 g Release 1, each enqueue type is represented by its own wait event, which makes it much easier to understand exactly what type of enqueue the session is waiting for. Please refer to Appendix B for a complete list of these enqueue waits.
Wait Parameters
Wait parameters for enqueue are described here:
-
P1 Enqueue name and mode requested by the waiting process. This information is encoded in ASCII format. The following SQL statement shows how you can find out the enqueue name and mode requested by the waiting process:
col Name format a4
select sid,
chr(bitand(p1, -16777216)/16777215)
chr(bitand(p1,16711680)/65535) "Name",
(bitand(p1, 65535)) "Mode"
from v$session_wait
where event = 'enqueue';
SID Name Mode
---------- ---- ----------
64 TX 6 -
P2 Resource identifier ID1 for the requested lock, same as V$LOCK.ID1
-
P3 Resource identifier ID2 for the requested lock, same as V$LOCK.ID2
The values for resource identifiers ID1 and ID2 are dependent on the enqueue name.
Wait Time
The wait time is dependent on enqueue name, but in most cases Oracle waits for up to three seconds or until the enqueue resource becomes available, whichever occurs first. When the wait event times out, Oracle will check that the session holding the lock is still alive and, if so, wait again.
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.
Wait Parameters
Wait parameters for free buffer waits are described here:
-
P1 File number from which Oracle is reading the block
-
P2 Block number from the file that Oracle wants to read into a buffer
-
P3 Not used prior to Oracle Database 10 g Release 1; in this release it shows the SET_ID# for the LRU and LRUW lists in the buffer cache
Wait Time
Oracle will wait up to one second for free buffers to become available and then try to find a free buffer again.
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.
The most common latches you need to know are cache buffer chains, library cache, and shared pool . These and other latches are discussed in detail in Chapter 6.
Wait Parameters
Wait parameters for latch free are described here:
-
P1 Address of the latch for which the process is waiting
-
P2 Number of the latch, same as V$LATCHNAME.LATCH#. To find out the latch name waited on, you can use the following SQL statement:
select *
from v$latchname
where latch# = &p2_value; -
P3 Number of tries; a counter showing the number of attempts the process made to acquire the latch
Wait Time
The wait time for this event increases exponentially. It does not include the time the process spent spinning on the latch.
In Oracle Database 10 g Release 1, most latches have their own wait events. Table 3-2 lists the wait events associated with latches.
| latch: In memory undo latch | latch: messages |
| latch: KCL gc element parent latch | latch: object queue header heap |
| latch: cache buffer handles | latch: object queue header operation |
| latch: cache buffers chains | latch: parallel query alloc buffer |
| latch: cache buffers lru chain | latch: redo allocation |
| latch: checkpoint queue latch | latch: redo copy |
| latch: enqueue hash chains | latch: redo writing |
| latch: gcs resource hash | latch: row cache objects |
| latch: ges resource hash list | latch: session allocation |
| latch: library cache | latch: shared pool |
| latch: library cache lock | latch: undo global data |
| latch: library cache pin | latch: virtual circuit queues |
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.
What actions to take to reduce these waits depend heavily on what blocking scenario is occurring. A common problem scenario is the use of DYNAMIC SQL from within a PL/SQL procedure where the PL/SQL code is recompiled and the DYNAMIC SQL calls something that depends on the calling procedure. If there is general widespread waiting, the shared pool may need tuning. If there is a blocking scenario, the following SQL can be used to show the sessions that are holding and/or requesting pins on the object that are given in P1 in the wait:
select s.sid, kglpnmod "Mode", kglpnreq "Req"
from x$kglpn p, v$session s
where p.kglpnuse=s.saddr
and kglpnhdl='&P1RAW' ;
Wait Parameters
Wait parameters for library cache pin are described here:
-
P1 Address of the object being examined or loaded
-
P2 Address of the load lock
-
P3 Contains the mode plus the namespace (mode indicates which data pieces of the object are to be loaded; namespace is the object namespace as displayed in V$DB_OBJECT_CACHE view)
Wait Time
For the PMON process it is one second; for all others it is three seconds.
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.
Wait Parameters
Wait parameters for library cache lock are described here:
-
P1 Address of the object being examined or loaded
-
P2 Address of the load lock
-
P3 Contains the mode plus the namespace (mode indicates which data pieces of the object are to be loaded; namespace is the object namespace as displayed in V$DB_OBJECT_CACHE view)
Wait Time
For the PMON process it is one second; for all others it is three seconds.
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 .
Wait Parameters
Wait parameters are not used for log buffer space .
Wait Time
Normally one second, but five seconds if the session has to wait for a log file switch to complete.
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.
The waits on this event usually indicate slow disk devices or contention where the redo logs are located.
Wait Parameters
Wait parameters for log parallel write are described here:
-
P1 Number of log files to write to
-
P2 Number of OS blocks to write to
-
P3 Number of I/O requests
Wait Time
Actual elapsed time it takes to complete all I/Os. Although the log files are written to in parallel, the write is not complete till the last I/O operation is complete.
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.
Wait Parameters
Wait parameters for log file sequential read are described here:
-
P1 Relative sequence number of the redo log file within the redo log group
-
P2 Block number to start reading from
-
P3 Number of OS blocks to read starting from P2 value
Wait Time
Actual elapsed time it takes to complete the I/O request to read.
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.
Wait Parameters
Wait parameters are not used for log file switch (archiving needed) .
Wait Time
One second
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.
Wait Parameters
Wait parameters are not used for log file switch (checkpoint incomplete) .
Wait Time
One second
log file switch completion
This wait event occurs when the process is waiting for log file switch to complete.
Wait Parameters
Wait parameters are not used for log file switch completion .
Wait Time
One second
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.
If a session continues to wait on the same buffer#, the SEQ# column of V$SESSION_WAIT view should increment every second. If not, then the local session has a problem with wait event timeouts. If the SEQ# column is incrementing, the blocking process is the LGWR process. Check to see what LGWR process is waiting on because it may be stuck.
Tune LGWR process to get good throughput to disk; for example, do not put redo logs on RAID-5 disk arrays. If there are lots of short-duration transactions see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each COMMIT has to have it confirmed that the relevant REDO is written to disk. Although commits can be piggybacked by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.
Wait Parameters
Wait parameters for log file sync are described here:
-
P1 The number of the buffer in the log buffer that needs to be synchronized
-
P2 Not used
-
P3 Not used
Wait Time
One second
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.
Wait Parameters
Wait parameters for SQL*Net message from client are described here:
-
P1 Prior to Oracle8 i release, the value in this parameter was not of much use. Since Oracle8 i , the P1RAW column contains an ASCII value to show what type of network driver is in use by the client connections; for example, bequeath, and TCP.
-
P2 The number of bytes received by the session from the client ‚ generally one, even though the received packet will contain more than 1 byte.
-
P3 Not used.
Wait Time
The actual time it takes for the client message to arrive since the last message the session sent to the client.
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.
Wait Parameters
Wait parameters for SQL*Net message to client are described here:
-
P1 Prior to Oracle8 i Database, the value in this parameter was not of much use. Since Oracle8 i , the P1RAW column contains an ASCII value to show what type of network driver is in use by the client connections, for example, bequeath and TCP.
-
P2 Number of bytes sent to client. This is generally one even though the sent packet will contain more than 1 byte.
-
P3 Not used.
Wait Time
Actual elapsed time it takes to complete the message delivery to the client.
EAN: 2147483647
Pages: 114