Re: latch wait - cache buffer chain

  • From: "goran bogdanovic" <goran00@xxxxxxxxx>
  • To: genegurevich@xxxxxxxxxxxxxxxxxxxxx
  • Date: Tue, 24 Oct 2006 22:28:46 +0200

Hi,

hot blocks are not only reason for cbc latches. The possible reasons could
be also long hash chains, but I would say most likely inefficient
statements. Check for stmt. which have high buffer_gets per execution stats.
You may have a situation when one process  own a cbc latch  (which may cover
many data blocks) and the same latch may be needed by your process/report.
Hot blocks happen usually when multiple sessions at the same time access one
or more blocks that are protected by the same child cache buffer chains
latch.

regards,
goran

On 10/24/06, genegurevich@xxxxxxxxxxxxxxxxxxxxx <
genegurevich@xxxxxxxxxxxxxxxxxxxxx> wrote:


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


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



Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 10-2006