Re: latch wait - cache buffer chain

Replace "v\$session_wait" of "v$session_wait". I use this in a perl script.

    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;

On 10/25/06, eagle fan <eagle.f_at_gmail.com> wrote:
>
> 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_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
> >
> >
> >
>
>
> --
> Eagle Fan
>
> Oracle DBA

-- 
Eagle Fan

Oracle DBA

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