RAC Wait events

Important RAC wait events

SQL> select event from v$system_event  where event like '%global%' order by event;

EVENT
----------------------------------------------------------------
buffer busy global CR
buffer busy global cache
ges global resource directory to be frozen - no
ges global resource directory to be unfrozen - no
global cache busy
global cache cr request
global cache domain validation - no
global cache null to s
global cache null to x
global cache open s
global cache open x
global cache s to x

buffer busy global cache

This wait event falls under the umbrella of ‘global buffer busy events’. This wait event occurs when a user is waiting for a block that is currently held by another session on the same instance and the blocking session is itself waiting on a global cache transfer.

buffer busy global CR

This wait event falls under the umbrella of ‘global buffer busy events’. This wait event occurs when multiple CR requests for the same block are submitted from the same instance before the first request completes, users may queue up behind it

global cache busy

This wait event falls under the umbrella of ‘global buffer busy events’. This wait event means that a user on the local instance attempts to acquire a block globally and a pending acquisition or release is already in progress.

global cache cr request

This wait event falls under the umbrella of ‘global cache events’. This wait event determines that an instance has requested a consistent read version of a block from another instance and is waiting for the block to arrive

global cache null to s and global cache null to x

This wait event falls under the umbrella of ‘global cache events’. These events are waited for when a block was used by an instance, transferred to another instance, and then requested back again.

global cache open s and global cache open x

This wait event falls under the umbrella of ‘global cache events’. These events are used when an instance has to read a block from disk into cache as the block does not exist in any instances cache. High values on these waits may be indicative of a small buffer cache, therefore you may see a low cache hit ratio for your buffer cache at the same time as seeing these wait events.

global cache s to x

This wait event falls under the umbrella of ‘global cache events’. This event occurs when a session converts a block from shared to exclusive mode.


To find locks in RAC
SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM gV$LOCK WHERE request>0)
ORDER BY id1, request
;

Important Wait Events In Oracle Database SQL Execution Level

Here are some of the most common wait events, and what they mean:

enqueue

The process is waiting on an enqueue (a lock you can see in v$lock). This commonly occurs when one user is trying to update a row in a table that is currently being updated by another user.

library cache pin

The process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. This happens when you are compiling or parsing a PL/SQL object or a view.

library cache load lock

The process is waiting for the opportunity to load an object or a piece of an object into the library cache. (Only one process can load an object or a piece of an object at a time.)

latch free

The process is waiting for a latch held by another process. (This wait event does not apply to processes that are spinning while waiting for a latch; when a process is spinning, it is not waiting.)

buffer busy waits

The process wants to access a data block that is currently not in memory, but another process has already issued an I/O request to read the block into memory. (The process is waiting for the other process to finish bringing the block into memory.)

control file sequential read

The process is waiting for blocks to be read from a control file.

control file parallel write

The process has issued multiple I/O requests in parallel to write blocks to all control files, and is waiting for all of the writes to complete.

log buffer space

The process is waiting for space to become available in the log buffer (Space becomes available only after LGWR has written the current contents of the log buffer to disk.) This typically happens when applications generate redo faster than LGWR can write it to disk.

log file sequential read

The process is waiting for blocks to be read from the online redo log into memory. This primarily occurs at instance startup and when the ARCH process archives filled online redo logs.

log file parallel write

The process is waiting for blocks to be written to all online redo log members in one group. LGWR is typically the only process to see this wait event. It will wait until all blocks have been written to all members.

log file sync

The process is waiting for LGWR to finish flushing the log buffer to disk. This occurs when a user commits a transaction. (A transaction is not considered committed until all of the redo to recover the transaction has been successfully written to disk.)

db file scattered read

The process has issued an I/O request to read a series of contiguous blocks from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during a full table scan or full index scan.

db file sequential read

The process has issued an I/O request to read one block from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during an index lookup or a fetch from a table by ROWID when the required data block is not already in memory. Do not be misled by the confusing name of this wait event!

db file parallel read

The process has issued multiple I/O requests in parallel to read blocks from data files into memory, and is waiting for all requests to complete. The documentation says this wait event occurs only during recovery, but in fact it also occurs during regular activity when a process batches many single block I/O requests together and issues them in parallel. (In spite of the name, you will not see this wait event during parallel query or parallel DML. In those cases wait events with PX in their names occur instead.)

db file parallel write

The process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete.

direct path read, direct path write

The process has issued asynchronous I/O requests that bypass the buffer cache, and is waiting for them to complete. These wait events typically involve sort segments.

There are several wait events that we call "idle events" because each of these wait events typically occurs when the Oracle process has nothing to do and is waiting for somebody to give it a task. Idle events are usually not very interesting from a tuning standpoint, so we usually overlook them when evaluating data extracted from the wait event interface. The common idle events are as follows:


Idle Wait Events


client message: PX Idle Wait
dispatcher timer rdbms ipc message
lock manager wait for remote message smon timer
Null event SQL*Net message from client
parallel query dequeue SQL*Net message to client
pipe get SQL*Net more data from client
PL/SQL lock timer virtual circuit status
pmon timer wakeup time manager