Script: To Monitor TOP Latches Statistics & Wait Information
Display Latch Statistics
WITH latch AS (
SELECT name,
ROUND(gets * 100 / SUM(gets) OVER (), 2) pct_of_gets,
ROUND(misses * 100 / SUM(misses) OVER (), 2) pct_of_misses,
ROUND(sleeps * 100 / SUM(sleeps) OVER (), 2) pct_of_sleeps,
ROUND(wait_time * 100 / SUM(wait_time) OVER (), 2) pct_of_wait_time
FROM v$latch)
SELECT * FROM latch
WHERE pct_of_wait_time > .1 OR pct_of_sleeps > .1
ORDER BY pct_of_wait_time DESC;
WITH latch AS (
SELECT name,
ROUND(gets * 100 / SUM(gets) OVER (), 2) pct_of_gets,
ROUND(misses * 100 / SUM(misses) OVER (), 2) pct_of_misses,
ROUND(sleeps * 100 / SUM(sleeps) OVER (), 2) pct_of_sleeps,
ROUND(wait_time * 100 / SUM(wait_time) OVER (), 2) pct_of_wait_time
FROM v$latch)
SELECT * FROM latch
WHERE pct_of_wait_time > .1 OR pct_of_sleeps > .1
ORDER BY pct_of_wait_time DESC;
Display Latch Statistics for Active Session History
WITH ash_query AS ( SELECT event, program, h.module, h.action, object_name,
SUM(time_waited)/1000 time_ms, COUNT( * ) waits, username, sql_text,
RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited)) OVER (), 2) pct_of_time
FROM v$active_session_history h
JOIN dba_users u USING (user_id)
LEFT OUTER JOIN dba_objects o
ON (o.object_id = h.current_obj#)
LEFT OUTER JOIN v$sql s USING (sql_id)
WHERE event LIKE '%latch%' or event like '%mutex%'
GROUP BY event,program, h.module, h.action,
object_name, sql_text, username)
SELECT event,module, username, object_name, time_ms,pct_of_time, sql_text
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank;
Display information about Top Latches
SELECT l.latch#, l.name, l.gets, l.misses, l.sleeps,
l.immediate_gets, l.immediate_misses, l.spin_gets
FROM v$latch l
WHERE l.misses > 0
ORDER BY l.misses DESC;
Display Latches wait information
SELECT latch#, name, gets, misses, sleeps
FROM v$latch
WHERE sleeps>0
ORDER BY misses, sleeps;
Display Latche Children information
select addr, latch#, gets, misses,sleeps
FROM v$latch_children
WHERE sleeps>0 and latch# in (select unique p2
from v$session_wait
where event ='latch free')
ORDER BY sleeps;
Display the information of Latches has been waiting for
select * from v$latchname
WHERE latch# in (select unique p2
from v$session_wait
where event ='latch free');
