Finding RAC Wait Events—Sessions Waiting

To identify the sessions experiencing waits on the system, perform the following tasks:

1.   Query GV$SESSION_WAIT to determine whether any sessions are experiencing RAC-related waits (at the current time).

2.   Identify the objects that are causing contention for these sessions.

3.   Try to modify the object or query to reduce contention.

For example, query GV$SESSION_WAIT (shown next) to determine whether any sessions are experiencing RAC cache–related waits. Note that the GV$ views are used much more to show statistics for the entire cluster, whereas the V$ views still show statistics from a single node. If you plan to use RAC, you must extend the V$ views and queries to the GV$ views for multiple nodes. This section is only an initial guide to help you see all of the components. The scope of this book does not cover RAC specifically, but some things that will help you tune RAC.

The output from this query should look something like this:

Run this query to identify objects that are causing contention for these sessions and the object that corresponds to the file and block for each FILE_NUMBER/BLOCK_NUMBER combination returned (this query is a bit slower):

The output is similar to

Modify the object to reduce the chances for application contention by doing the following:

   Reduce the number of rows per block.

   Adjust the block size to a smaller block size.

   Modify INITRANS and FREELISTS.