Analyze cluster-wide wait events (gc waits) per instance with GV$

Command

-- Cluster-class waits per instance (since startup):
SELECT inst_id, event, total_waits,
       ROUND(time_waited_micro/1e6) AS time_waited_s,
       ROUND(time_waited_micro/NULLIF(total_waits,0)/1000, 2) AS avg_ms
FROM   gv$system_event
WHERE  wait_class = 'Cluster'
ORDER  BY time_waited_micro DESC
FETCH FIRST 15 ROWS ONLY;

-- Sessions waiting right now, by instance and event:
SELECT inst_id, event, COUNT(*) AS sessions
FROM   gv$session
WHERE  wait_class <> 'Idle' AND state = 'WAITING'
GROUP  BY inst_id, event
ORDER  BY sessions DESC;

-- ASH for the last 15 minutes: which instance is waiting on gc
SELECT inst_id, event, COUNT(*) AS samples
FROM   gv$active_session_history
WHERE  sample_time > SYSTIMESTAMP - INTERVAL '15' MINUTE
  AND  wait_class = 'Cluster'
GROUP  BY inst_id, event
ORDER  BY samples DESC
FETCH FIRST 10 ROWS ONLY;

What it does

Uses GV$ views to show Cluster-class waits (gc cr/current block 2-way/3-way, gc buffer busy, gc cr request) and their average latency across all instances in one query.

When to use it

When users report slowness on RAC, to tell whether the problem comes from the interconnect/global cache and which instance it concentrates on.

Caution

gv$active_session_history requires the Diagnostics Pack license. If avg_ms is above a few ms, check the interconnect first (MTU, packet loss, netstat -s) and LMS CPU; gc buffer busy usually points to an application hot block (sequence, right-hand index).