Informacion sobre Latch
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;