Diagnosing "latch free" waits in v$session_wait

Home

Toolkit:

My OraFAQ Blog

Contact me

These queries are meant to be used to help diagnose the waitevents shown in v$session_wait.

Monitor v$session_wait with this query. If you see latch waitevents, use the queries shown here to dig deeper.

Use the values of v$session_wait.p2 and v$session_wait.p1raw in the scripts below. Always enclose p1raw in single quotes.

Symptom: One specific session is hanging on a latch wait.


select * from v$latch where latch#=

select addr, latch#, sleeps from v$latch_children 
where latch#= value of v$session_wait.p2 
order by sleeps;     

 SELECT name, 'Child '||child#, gets, misses, sleeps 
  FROM v$latch_children
 WHERE addr= value of v$session_wait.p1raw
UNION
 SELECT name, null, gets, misses, sleeps
  FROM v$latch
 WHERE addr= value of v$session_wait.p1raw

Symptom: Many latch waits in v$session; you want more information on these many latch waits.

/* LATCH WAITS:  This shows the latches sessions are waiting on */
select s.sid, s.username, lc.addr, lc.latch#, lc.child#, lc.level#, 
lc.name, lc.gets,
decode(gets,0,0,round(100*(gets-misses)/gets,2)) hitratio, 
decode(immediate_gets, 0,0, 
round(100*(immediate_gets-immediate_misses)/immediate_gets,2)) 
immed_hitratio,
decode(gets,0,0,round(100*sleeps/gets,2)) pct_sleeps, 
waiters_woken, waits_holding_latch, spin_gets,
sleep1, sleep2, sleep3, sleep4, sleep5
from v$latch_children lc, v$session_wait w, v$session s
where latch#=p2 and lc.addr=p1raw
and s.sid=w.sid

Symptom: Waiting on cache buffer chains latch (latch #66).

Long waits on latch #66 can often indicate that your buffer cache contains "hot blocks" that are getting a disproportionately high number of touches. When you try to access other blocks whose pointers are stored in the same memory structure as these hot blocks, you get a wait. This query will help you find and diagnose any "hot blocks". 


/*  Cache buffers chains latches?  Identify buffer chains w/ high sleep 
counts (Query takes a long time) */

SELECT hladdr "LATCH_ADDR", dbarfil "FILE#", dbablk "BLOCK#",
    tch "TOUCHES", object_name, o.object_id
    FROM x$bh b, dba_objects o 
    WHERE 1=1
    and hladdr=value of p1raw in v$session_wait
       AND b.obj=o.object_id
    ORDER BY 4 desc, 1,2,3
Once you've identified hot blocks, fix the problem by adding FREELISTS to the object owning the block, eg: 
ALTER TABLE HOT_BLOCK_TABLE
STORAGE (FREELISTS 2) ;

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...