Oracle DBA – Tips and Techniques

Knowledge is Power – so share it ….

Identify ‘hot’ objects when faced with ‘Cache Buffers Chains Latch’ wait event

Posted by Gavin Soorma on June 29, 2009

select
   count(*)    child_count,
   sum(gets)   sum_gets,
   sum(misses) sum_misses,
   sum(sleeps) sum_sleeps
from
   v$latch_children
where
   name = 'cache buffers chains';

select
   p1 "File #".
   p2 "Block #"
from
   v$session_wait
where
   event ='cache buffer chains';

select
   owner,
   segment_name,
   segment_type
from
   dba_extents
where
   file_id = &P1
and
 &P2 between block_id and block_id + blocks -1;

col object_name format a20
col owner format a10
col subobject_name format a20

select distinct o.owner, o.object_name, o.subobject_name, o.object_type
from dba_objects o, v$bh b
where o.data_object_id=b.objd and b.objd < power(2,22) and status != 'free'
  and b.file#=&P1
  and b.block#=&P2
;

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <pre> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>