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

Blockers and Deadlocks
When two users request access to same row of data for update, all users except the first will remain in wait mode
until the first user has committed or rolled back the change. This is true irrespective of whether it is a single-instance
configuration or a clustered RAC configuration. In the RAC configuration, apart from users on the same instance,
users from other instances can also request the same row at the same time. The following query helps identify a
blocking session in a RAC environment:
Script: MVRACPDnTap_blockers.sql
SELECT DECODE(G.INST_ID,1,'SSKY1',2,'SSKY2') INSTANCE,
S.SID,
G.TYPE,
S.USERNAME,
S.SERIAL#,
S.PROCESS,
DECODE(LMODE,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/ROW', 6,'Exclusive') LMODE,
DECODE(REQUEST,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share', 5,'S/ROW',6,'Exclusive')
REQUEST,
DECODE(REQUEST,0,'BLOCKER','WAITER') STATE
FROM GV$GLOBAL_BLOCKED_LOCKS G,
GV$SESSION S
WHERE G.SID = S.SID
AND G.INST_ID = S.INST_ID
ORDER BY STATE
INSTANCE SID TY USERN SERIAL# PROCESS LMODE REQUEST STATE
---------- ---- -- ----- ---------- ------------ --------- --------- -------
SSKY2 132 TX OE 519 2576:820 Exclusive None BLOCKER
SSKY2 148 TX OE 78 980:3388 None Exclusive WAITER
SSKY1 139 TX OE 114 3192:2972 None Exclusive WAITER
In the preceding output, the session with SID # 132 on instance SSKY2 is the BLOCKER because this session
accessed this row first in an exclusive mode and has not either committed or rolled back the transaction.
Subsequently, the session with SID # 148 also on instance SSKY2 requested for this same row for update, followed
by SID # 139 from instance SSKY1 . Both these SIDs (Session IDs) will remain, as WAITER (s), until such time the row is
available to the session to complete its update operation.
Blockers can also be determined from the EM by selecting the “Blocking Sessions” option from the database
performance page.
Identifying Hot Blocks
When the same sets of blocks are requested back and forth between the various instances in the cluster, the sessions
requesting for the block may have to wait until the holding instance has released it. Similarly, the frequent changes of
blocks may require the copies of blocks currently in the buffer of the respective instances to be refreshed, increasing
high interconnect traffic. Although all of this is normal behavior in a RAC cluster, excessive access of a few blocks of
data from multiple instances can cause I/O contention and become segment-level hot spots for tuning.