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_at_gmail.com> 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_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
Received on Tue Oct 24 2006 - 15:33:40 CDT
