Analizar los wait events de todo el clúster (gc waits) por instancia con GV$

Comando

-- 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;

Qué hace

Usa las vistas GV$ para mostrar en una sola consulta las esperas de clase Cluster (gc cr/current block 2-way/3-way, gc buffer busy, gc cr request) y su latencia media en todas las instancias.

Cuándo usarlo

Cuando los usuarios reportan lentitud en RAC, para saber si el problema viene del interconnect/global cache y en qué instancia se concentra.

Precaución

gv$active_session_history requiere la licencia de Diagnostics Pack. Si avg_ms supera unos pocos ms, revise primero el interconnect (MTU, pérdida de paquetes, netstat -s) y la CPU de LMS; gc buffer busy suele apuntar a un bloque caliente de la aplicación (secuencia, índice de lado derecho).