Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Latch free waits..

Re: Latch free waits..

From: Ron <>
Date: Wed, 28 Jul 2004 13:41:22 -0700
Message-ID: <>

Hello Richard,

  Good points on debugging individual sessions (10046 is a good start, but to get to the root cause you'd need to do more digging).

  May be I misunderstood, but I though that OP had latching issue with number on sessions, not a single one (OP can confirm, if not):

High amount of latch free waits is slowing down sessions on db.. any ideas on how to debug further?

   BTW - statspack is good to collect information on individual sessions as well:

execute statspack.snap(i_session_id=><N>);


   DBA Infopower

"Richard Foote" <> wrote in message news:xBMNc.20376$
> "Ron" <> wrote in message
> >
> > Hello Thomas,
> >
> > As a start point, please generate statspack report during the time
> > database experiences bad performance and compare it to the statspack
> report
> > from the time when performance is acceptable.
> >
> > Statspack report provides you with information on what database is
> waiting
> > most and in the latch section of the report what latches are the problem
> > ones.
> >
> > If it is acceptable, please share generated statspack report with the
> > group for further review (no need to send SQL part)
> >
> Actually Ron, using statspack to determine performance issues for these
> types of scenarios is entirely the *wrong* thing to do.
> Why ?
> Because the OP is complaining about particular sessions performing
> sub-optimally, perhaps/perhaps not because of latch contention issues. I
> perhaps not, because without determining the *particular* bottlenecks for
> the *sessions* in question, one would only be guessing at what the root
> cause of the problems really are.
> The problem with statspack is that it provides information at the
> level which in most/many cases will totally drown out the wait information
> associated with the transaction performance that matters most to the
> business/OP. What may look like issues at the database level may have
> absolutely *nothing* to do with the problems needing to be addressed by
> OP. Using statistics generated and summarised by potentially 10,000s of
> different transactions may very well be totally misleading with addressing
> issues face by the "few" transactions in question/interest.
> At the end of the day, using such data to diagnose these issues means
> only *guessing and hoping* you're on the right track. The infamous Method
> approach to performance tuning ...
> A far better "start point" for the OP to take would be to trace just those
> sessions that are "slowing down" with a level 12 10046 event and see
> *exactly* what is causing the slow down. No summarisations, no taking all
> the other transactions data into the mix, no guessing and hoping you might
> stumble on the real issue(s), but *know for sure exactly* what the
> associated waits and resources are that are causing the slow down. Once
> know what the issues are, what's most affecting the response times, then
> you'll know what actions to take to address them.
> Ron, it's time you dumped Method C and moved onto Method R ...
> Cheers
> Richard
Received on Wed Jul 28 2004 - 15:41:22 CDT

Original text of this message