Analisar wait events de todo o cluster (gc waits) por instância com 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;

O que faz

Usa as views GV$ para mostrar, em uma única consulta, as esperas da classe Cluster (gc cr/current block 2-way/3-way, gc buffer busy, gc cr request) e sua latência média em todas as instâncias.

Quando usar

Quando usuários relatam lentidão no RAC, para saber se o problema vem do interconnect/global cache e em qual instância ele se concentra.

Atenção

gv$active_session_history exige a licença do Diagnostics Pack. Se avg_ms passar de alguns ms, verifique primeiro o interconnect (MTU, perda de pacotes, netstat -s) e a CPU do LMS; gc buffer busy normalmente aponta para um hot block da aplicação (sequence, índice crescendo à direita).