Re: latch wait - cache buffer chain

take the p1raw from the v$session_wait for waiting session and this is your
hladdr in the query you are looking for.

On 10/24/06, Frits Hoogland <frits.hoogland@xxxxxxxxx> wrote:

You should find the hottest child latch yourself, and swap the value of ADDR on line 12 with the value of hladdr of the child latch.

if you execute this query, no rows should come up, because the chance of
having an hladdr = 'ADDR' in x$bh is fairly low.

cheers!

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

Other related posts: