Common Wait Events in Oracle Real Application Clusters Environment


In this section we will discuss some of the common wait events that you will see in Oracle Real Application Clusters (RAC) environment.

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 the global 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 10 g 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 the global 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 the global 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 requests waits are typically followed by the db file sequential reads and/or db file scattered read waits.

Wait Parameters

Wait parameters for global cache cr request 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 The lock element number or class of the buffer

Table 3-3 lists the most commonly seen block classes.

Table 3-3: Common Block Classes

Block Class

Description

System rollback segment

1

Data blocks

2

Sort blocks

3

Deferred rollback segment blocks (save undo)

4

Segment header blocks

5

Deferred rollback segment header blocks

6

Freelist blocks

7

Extent map blocks

8

Bitmapped space management blocks

9

Space management index blocks

10

Unused

Wait Time

The wait is up to one second between timeouts. The actual wait time will be until the buffer is cached at the requesting instance in required compatible mode.

buffer busy global cache

When a session wants to modify a buffer that is cached in the remote instances, it waits on the buffer 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 Database10 g Release 1, buffer busy global cache waits are known as gc buffer busy waits.

Wait Parameters

Wait parameters for buffer busy global cache 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 The numeric code indicating the reason

Wait Time

The wait time is one second between timeouts.

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 the buffer 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 10 g Release 1, this wait event is known as gc cr block busy waits.

Wait Parameters

Wait parameters for buffer busy global cr 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 The numeric code indicating the reason

Wait Time

The wait time is one second between timeouts.

global cache busy

When a session wants to modify a buffer that is held in a shared mode, it waits on the global 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 the global 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.

Wait Parameters

Wait parameters for global cache busy 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 The lock element number or class of the buffer

Wait Time

The wait time is one second between timeouts.

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

Wait Parameters

Wait parameters for global cache null to x 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 The lock element number or class of the buffer

Wait Time

One second

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.

Wait Parameters

Wait parameters for global cache null to s 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 The lock element number or class of the buffer

Wait Time

One second

global cache s to x

The session waits for global cache s to x when 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 the global cache converts counter. If any other instance is holding the buffer in SHARED mode, the lock mode is downgraded to null mode.

Typically, index updates will result in global cache s to x waits 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 EXCLUSIVE mode.

Wait Parameters

Wait parameters for global cache s to x 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 The lock element number or class of the buffer

Wait Time

One second

global cache open 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.

Wait Parameters

Wait parameters for global cache open x 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 The lock element number or class of the buffer

Wait Time

One second

global cache open 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.

Wait Parameters

Wait parameters for global cache open s 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 The lock element number or class of the buffer

Wait Time

One second

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.

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.

Wait Parameters

Wait parameters for row cache lock are described here:

  • P1 Cache ID of the row cache lock the session is waiting for; can be obtained from V$ROWCACHE

  • P2 The mode in which the lock is held

  • P3 The mode in which the lock is requested

The following SQL can be used to find cache number from the V$ROWCACHE:

 select cache#, type, parameter 
from v$rowcache
where cache# = &P1;

Wait Time

Three seconds between timeouts. After 100 timeouts, the process is rolled back and the session writes : WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK in the alert.log, and the process is aborted.




Oracle Wait Interface
Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)
ISBN: 007222729X
EAN: 2147483647
Year: 2004
Pages: 114

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net