Re: Latch free waits..

"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 - 07:02:37 CDT