latch wait - cache buffer chain

He everybody:

I am trying to tune a report which has been running for some time and has
been timing out since last week.
When I execute that report I see a large number of "latch free" wait events
with p2 parameter equal to 98.
Based on v$latch that is a cache buffer chain latch.  One of the reasons
for this event can be a hot block.
I have found the following query on the metalink to locate a hot block:
  1  select /*+ RULE */
  2    e.owner ||'.'|| e.segment_name  segment_name,
  3    e.extent_id  extent#,
  4    x.dbablk - e.block_id + 1  block#,
  5    x.tch,
  6    l.child#
  7  from
  8    sys.v$latch_children  l,
  9    sys.x$bh  x,
 10    sys.dba_extents  e
 11  where
 12    x.hladdr  = 'ADDR' and
 13    e.file_id = x.file# and
 14    x.hladdr = l.addr and
 15    x.dbablk between e.block_id and e.block_id + e.blocks -1
 16*   order by x.tch desc

but it did not return anything. Is there anything else I need to look at
related to this latch?

Thanks for any insight

thank you

Gene Gurevich


--
http://www.freelists.org/webpage/oracle-l

Other related posts: