Oracle Common wait events

最近在看《Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning》，第三章列出了常见的等待事件。

 

自己记录一下，方便查阅。

1 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 10g 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 10gRelease 1, such waits are now posted asread by other sessionevent. The buffer busy waits event denotes the waits by a session for data block change completion by some other session.

Oracle Database 10g Release 1 has another event titledbuffer busy. Do not confuse this event withbuffer busy waits. The buffer busyevent 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 encounterbuffer busy waits are data blocks, segment header, undo blocks, and undo header.

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

 

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

 

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

 

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

 

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

 

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

 

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

 

9 direct path write

The direct path write wait event is just an opposite operation to that ofdirect 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 ondirect 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.

 

10 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 10g 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 toAppendix B for a complete list of these enqueue waits.

 

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

 

12 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 inChapter 6.

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

20 log file switch completion

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

 

21 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 bepiggybacked by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.

 

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

 

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

 

还有RAC下面的：

1 global cache cr request

When a session is looking for a consistent read (CR) copy of the buffer cached by the remote instance, it waits on theglobal cache cr request event till the buffer is available in the local instance. Normally the holder constructs the CR version of the buffer and ships it to the requesting instance through the high speed interconnect. The wait ends when the CR buffer arrives at the local buffer cache.

From Oracle Database 10g Release 1, global cache cr request waits are known as gc cr request waits.

Depending on the mode of the buffer held in the remote instance and the number of times the remote instance ships the CR copy to the local instance, one of the following will happen:

The CR copy of the buffer is sent to the requesting instance and the “fairness counter” is incremented for that buffer. Subsequently theglobal cache cr blocks received statistic is incremented.

If the buffer is not cached by the remote instance (but mastered by the remote instance), it grants it to the requesting instance. The session can read the block from the disk and theglobal cache gets statistic is incremented.

If the number of CR copies for that particular buffer reaches the_fairness_threshold limit, the remote instance downgrades the lock to null mode and flushes the redo to the disk. The session can do the disk I/O to get the block from the disk.

In cases 2 and 3, the global cache cr requestswaits are typically followed by the db file sequential reads and/ordb file scattered read waits.

 

2 buffer busy global cache

When a session wants to modify a buffer that is cached in the remote instances, it waits on thebuffer busy global cache event. This is similar to buffer busy wait in single instance architecture. This wait happens at cache layer. In other words, the session is waiting for a buffer in local cache, which is busy because it is waiting for a global cache request to complete. The wait at the RAC layer is accounted in the global cache busy wait event.

From Oracle Database10g Release 1, buffer busy global cache waits are known as gc buffer busy waits.

 

3 buffer busy global cr

When more than one session is queued for a CR copy of the buffer in a same instance, and the CR copy is expected from the remote instance, the session waits on thebuffer busy global cr wait event. Once the CR copy from the remote instance arrives at the local cache, the block SCN is compared with the snapshot SCN before the buffer can be used.

From Oracle Database 10g Release 1, this wait event is known asgc cr block busy waits.

 

4 global cache busy

When a session wants to modify a buffer that is held in a shared mode, it waits on theglobal cache busy wait event till it gets the buffer in current mode. This normally happens when an instance wants to acquire a buffer from a remote instance and the lock convert or acquisition process is in progress. Normally, the segment header blocks, index branch blocks, and bitmap segment blocks are held in the shared mode. During addition of new blocks to the segment, the buffer is transformed from shared current mode to exclusive current mode.

Waits for global cache busy are usually followed by theglobal cache s to x waits. Excessive waits for these waits could be an indicator of slow interconnect or a delay in flushing the redo for that particular block on the remote instance.

 

5 global cache null to x

When a session wants to modify a block, it must hold that block in exclusive mode in its local cache. The session will wait forglobal cache null to x wait when the buffer is not in its local cache in any other mode. If the buffer is in the global cache at the remote instance, the buffer is shipped to the local cache and the exclusive lock for that buffer is granted. The statistics global cache current blocks served is incremented after the transfer.

If it is not held in any other mode in remote cache, the lock is granted for that buffer from the Global Cache Service (GCS) at the appropriate mode. Once the lock is granted for the buffer, disk I/O is done to read the buffer from the disk.

 

6 global cache null to s

When a session wants to read a buffer, it has to convert from NULL mode to SHARED mode. The buffer can be read from the disk if it is not cached globally or can be obtained from the other instance’s cache through the interconnect.

7 global cache s to x

The session waits for global cache s to xwhen it holds the buffer in SHARED mode and wants to convert it to EXCLUSIVE (X) mode. If no other instances are holding that buffer in SHARED or EXCLUSIVE mode, the lock is immediately upgraded to X mode after incrementing theglobal cache converts counter. If any other instance is holding the buffer in SHARED mode, the lock mode is downgraded tonull mode.

Typically, index updates will result in global cache s to xwaits as you lock the buffer for SHARED mode while traversing in the index tree. It will update the leaf block address in the branch block after holding the buffer in EXCLUSIVEmode.

 

8 global cacheopen x

A session waits for the global cache open x wait when it wants to modify the current block (usually for inserts and bulk loads), which is not cached in the local instance in any mode. The current block has to come from the remote instance or from the disk. If the block is not cached in any of the caches and if that is a new block to the global cache, there is no lock conversion involved. The process is called block “newing,” and it happens during the new blocks allocated to the freelist chain or bitmap segments.

9 global cacheopen s

A session waits for the global cache open s when it reads the block in the buffer cache for the first time. This can happen during the startup or while reading a block that is the first-time read into the local buffer cache. The block can either be read from the disk or shipped from the other instance cache.

10 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 requirerow cache lock, and the session will wait for therow cache lock to lock the data dictionary information.

This wait is not a RAC-specific wait. It is applicable in single instance also but has a bigger impact in the RAC environment because the library cache and row cache are globally coordinated.