Tuning Global Cache - Expert Oracle RAC Performance Diagnostics and Tuning - page 419

■ “ gc current block 2-way ” wait event occurs when the block is currently not in the buffer cache of the local
instance but is available on another instance (holder), and the block needs to be transferred over the interconnect to the
requesting instance, performing a two-hop operation.

Note

Applying this to the previous discussion, the block was not in the buffer cache of instance 2 (requestor);
however, because a previous user executed this query on instance 1 (holder), the blocks had to be transferred via the
interconnect to instance 2.

■ “ gc current block 3-way ” wait event occurs when the block is currently not in the buffer cache of the local
instance (requestor) but is available on another instance (holder); however, the block was mastered on a third instance,
and the request for the block (message) had to perform 3 hops before the requesting instance received the block.
irrespective of the number of instances in the cluster, this is the maximum number of hops that can occur before the
requestor receives the block.

Note

Comparing the logical I/O operations between Oracle Database 11g Release 2 and Oracle Database 10g Release 2,
it is obvious that there is a higher amount of logical I/O in Oracle Database 10g Release 2 compared to Oracle
Database 11g Release 2. This is the result of the improvements that have been incorporated into the Oracle Database
11g Release 2 optimizer.

Queries with High Cluster Overhead
Queries not tuned can also be an overhead to the performance across the cluster, causing high delays. In Oracle
Database 10g, four new columns were introduced to help identify queries that are performing poorly in general and
specifically in a RAC environment.
Using the CLUSTER_WAIT_TIME column in GV$SQLSTATS view, queries that are experiencing cluster-related waits
can be identified and tuned. For example, the following query lists the SQL queries giving the wait times experienced
at various stages of the operation:

Script: MVRACPDnTap_sqlstats.sql

SELECT inst_id - INT,
sql_id,
application_wait_time awt,
concurrency_wait_time conwt,
cluster_wait_time clwt,
user_io_wait_time uiwt
FROM gv$sqlstats
WHERE cluster_wait_time > 10000
ORDER BY inst_id,
user_io_wait_time desc;