Re: latch wait - cache buffer chain

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_at_discoverfinancial.com < genegurevich_at_discoverfinancial.com> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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