Re: Latch free waits..

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>);

Cheers

   Ron,
   Support
   DBA Infopower
   http://www.dbainfopower.com

"Richard Foote" <richard.foote_at_bigpond.nospam.com> wrote in message news:xBMNc.20376$K53.3804_at_news-server.bigpond.net.au...
> "Ron" <support_at_dbainfopower.com> wrote in message
> news:t96dnWl8DP4nTpvcRVn-vg_at_comcast.com...
> >
> > 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
say
> 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
*database*
> 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
the
> 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
you're
> only *guessing and hoping* you're on the right track. The infamous Method
C
> 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
you
> 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