Re: What's wrong with these two ways of looking at wait events?

"thu do" <secemad_at_hotmail.com> wrote in message news:a777bc73.0409092240.1cc02bb4_at_posting.google.com...
> Hi,
>
> I am trying to figure out how much of a problem buffer busy waits is
> in our system. If I have the following information in the statspack,
> how do I know if the 19.64% number is bad or not? (The statspack is
> made up since I don't have a real one, the number of waits is much
> much higher in case that number makes a difference).
>
> The statspack is for one hour period. Our database is running with 80
> CPUs on Fujitsu, at 60% utilization with I/O wait or 35% utilization
> without I/O wait during peak hour. This is an OLTP environment.
>
> The 19.64% looks high however if I take the Total Wait time of 1460
> and divide by number of users on the system, I'd get 1.5 second wait
> per user for the whole hour, that's nothing.
>
> How do I know when I'd have a problem or close to a huge problem? I
> prefer not to wait until there is a huge problem before doing
> something about it.
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> buffer busy waits 10,669 1,460
> 19.64
>

Hi Thu

It's only a problem if the users are complaining about response times and the cause of the poor response times are as a result of these buffer busy waits.

Looking at a database wide statspack report in isolation is problematic. How do you know if it's directing impacting on the responses times that cause concern ? How do you know if it's only a few key sessions experiencing these buffer busy waits or whether they're being "shared" among hundreds of sessions, with little overall impact or whether it's all the result an unimportant batch job for which response times is not a concern ?

The answer is you can't.

To answer your question of how do you know if you have a problem, you first need to try and answer the above questions. Are the buffer busy waits directly impacting response times of transactions that are causing users grief and if so what are the objects and transactions that are causing these waits ?

An excellent method is to trace sessions that are experiencing unacceptable response times and see *exactly* what is causing the problems. No ifs and buts but know *exactly* if buffer busy waits are an issue and what are the "offending" blocks.

I recently did a little presentation on all this, the slides of which are accessible here
http://www.actoug.org.au/Downloads/Extended%20SQL%20Trace.pdf.

Have a look then do yourself a favour and get a hold of Cary Millsap's book (highlighted in the presentation).

Good Luck

Richard Received on Fri Sep 10 2004 - 08:53:01 CDT