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