The condition "hladdr='ADDR'" is not correct.
Use this sql:
select /*+ first_rows */ hladdr,file#,dbablk,tch from x$bh where
hladdr in (select distinct p1raw from v\$session_wait where event='latch
free'
and p2=98) and tch>10;
You can find the file# and block_id (maybe not one blocks) which are on
the chain.
On 10/25/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
>
>
>
--
Eagle Fan
Oracle DBA
