PROMPT
PROMPT ******************************************** LATCH ***************************************************************************************************
PROMPT

PROMPT ******************************************** ESPERAS POR LATCH

SELECT inst_id, event evento, total_waits esperas, total_timeouts timeouts, average_wait promedio
FROM gv$system_event
WHERE event = ‘latch free’
ORDER BY 1;

PROMPT
PROMPT ******************************************** CONTENCION POR LATCH (HIT % > 99%)

COLUMN “HIT %” FORMAT 999.99
COLUMN “IHIT %” FORMAT 999.99

SELECT inst_id, name latch, gets, misses, 100*(1-misses/DECODE(gets,0,1,gets))  “HIT %”,
       immediate_gets igets, immediate_misses imisses,
       100*(1-immediate_misses/DECODE(immediate_gets + immediate_misses,0,1,immediate_gets + immediate_misses)) “IHIT %”
FROM gv$latch
WHERE gets + immediate_gets > 0 AND
      (1-sleeps/DECODE(gets,0,1,gets) < 0.990 OR 1-immediate_misses/DECODE(immediate_gets + immediate_misses,0,1,immediate_gets + immediate_misses) < 0.990)
ORDER BY 2,1;

COLUMN “HIT %” CLEAR
COLUMN “IHIT %” CLEAR

PROMPT
PROMPT ******************************************** SESIONES OCUPANDO LATCH

SELECT s.inst_id, s.username usuario, s.osuser, l.name latch, l.laddr laddress, s.machine, s.program, s.status, s.sid, s.serial#, type, module, a.name accion
FROM gv$session s, gv$latchholder l, audit_actions a
WHERE s.inst_id = l.inst_id AND
      s.sid = l.sid AND
      a.action = command;

PROMPT
PROMPT ******************************************** SQL

BREAK ON hash_value

SELECT hash_value, sql_text
FROM gv$sqltext_with_newlines
WHERE (address, hash_value) IN (SELECT sql_address, sql_hash_value
                                FROM gv$session s, gv$latchholder l
                                WHERE s.inst_id = l.inst_id AND s.sid = l.sid)
ORDER BY 1, piece;

PROMPT
PROMPT ******************************************** BLOQUES CUBIERTOS

SELECT dbarfil, dbablk, class, state
FROM X$BH
WHERE HLADDR IN (SELECT laddr FROM gv$latchholder);

PROMPT
PROMPT ******************************************** SESIONES ESPERANDO LATCH
SELECT inst_id, sid, event EVENTO, wait_time, seconds_in_wait, state, name latch, p1 address
FROM gv$session_wait s, v$latchname l
WHERE event LIKE ‘latch%’ AND
      l.latch#=s.p2;