latch wait - cache buffer chain

From: <genegurevich_at_discoverfinancial.com>
Date: Tue, 24 Oct 2006 14:19:39 -0500
Message-ID: <OFF75A31A3.CE04F3E3-ON86257211.0069983D-86257211.006A29F0@discoverfinancial.com>

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

Received on Tue Oct 24 2006 - 14:19:39 CDT