Latch type and sql hash value - Oracle Database Script.
SET lines 160 pages 100 COLUMN event format A35 COLUMN name format A35
SELECT x.event,
x.sql_hash_value,
CASE
WHEN x.event LIKE 'latch%' THEN l.name
ELSE ' '
END name,
x.cnt
FROM
(SELECT substr(w.event, 1, 28) event,
s.sql_hash_value,
w.p2,
count(*) cnt
FROM v$session_wait w,
v$session s,
v$process p
WHERE s.sid=w.sid
AND p.addr = s.paddr
AND s.username IS NOT NULL
AND w.event NOT LIKE '%pipe%'
AND w.event NOT LIKE 'SQL*%'
GROUP BY substr(w.event, 1, 28),
sql_hash_value,
w.p2) x,
v$latch l
WHERE x.p2 = l.latch#(+)
ORDER BY cnt;