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

'KJUSEREX', 'Exclusive',
request_level) AS GRANT_LVL,
Decode ( Substr (dl.request_level, 1, 8), 'KJUSERNL', 'Null',
'KJUSERCR', 'Row-S (SS)',
'KJUSERCW', 'Row-X (SX)',
'KJUSERPR', 'Share',
'KJUSERPW', 'S/Row-X (SSX)',
'KJUSEREX', 'Exclusive',
request_level) AS REQ_LVL,
Decode ( Substr (dl.state, 1, 8), 'KJUSERGR', 'Granted',
'KJUSEROP', 'Opening',
'KJUSERCA', 'Cancelling',
'KJUSERCV', 'Converting') AS LOCK_STATE,
sw.event EVENT,
sw.seconds_in_wait WTs
FROM gv$ges_enqueue dl,
gv$process p,
gv$session s,
gv$session_wait sw
WHERE blocker = 1
AND ( dl.inst_id = p.inst_id
AND dl.pid = p.spid )
AND ( p.inst_id = s.inst_id
AND p.addr = s.paddr )
AND ( s.inst_id = sw.inst_id
AND s.sid = sw.sid )
ORDER BY sw.seconds_in_wait DESC; = s.paddr)
AND (s.inst_id = sw.inst_id and s.sid=sw.sid)
ORDER BY sw.seconds_in_wait DESC;
INT SesID,Ser# SPID RNAME GRANT_LVL REQ_LVL
---- ------------ ------ ------------------- ---------- ----------
1 247,1 18994 [0x19][0x2],[RS] Exclusive Exclusive
3 100,1 6713 [0x1][0x0],[PW] Row-X (SX) Row-X (SX)
2 100,1 31778 [0x1][0x0],[PW] Row-X (SX) Row-X (SX)
GV$CR_BLOCK_SERVER
The view provides statistics on the global cache activity (GCS) between instances in the cluster. The view is also
extremely useful to determine LMS activity and could be useful to determine prepare time issues during global cache
management. If the overall cluster activity and response time is slow, it should be directly mapped to the prepare time
of the LMS process.
This view is heavily loaded, meaning it has a significant amount of information and provides leads into further
investigations into specific areas if required.