Re: Performance base line

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote:

>The baseline statistics you want, I think, simply means grabbing a statspack
>report at a time when none of your users are complaining, and everyone is
>generally happy with performance. If that happens to include 3 buffer busy
>waits per hour, so be it: you know that 3 bbw/hr is an acceptable level of
>performance. When you subsequently see that creeping up to 5, 8, 15, 24 per
>hour, you know you have a performance issue.

I can't get rid of the impression that we are talking about different things when it comes to performance. If people are starting long running, untuned PL/SQL procedures and batch jobs, I don't need the statspack to see that the server is overloaded (I happen to be a Unix administrator and usually I keep a close eye on CPU, Network and I/O). I even examine the hit rate of the battery buffered Raid read/write cache.

My concern is the effectiveness of my tuning measures, which can be assessed mainly by the time consumed to execute something (and by the resources consumed thereby, but that is secondary in case there is no contention by concurrent users).

In this sense, I don't think that I have a performance issue when the machine is servicing heavily and the number of buffer busy waits is high - that is completely logical to me in the given situation. I have a performance issue if the jobs are running slower than they could because something hasn't been optimized. So if the counts delivered by statspack aren't normalized to time units, then they are useless to me, because there is no way to relate increase/decrease of workload to my tuning actions.

As a matter of fact, statspack could be even misleading. Suppose that I switch the degree of parallelism for a big table (along with the corresponding init-parameters) in order to take advantage of several CPUs. Then the associated query would take a shorter time to execute but would cause unproportionally more I/O (assuming the existence of independent disk devices) and consume unproportionally more CPU time. Statspack would reflect that and some DBA would think that there is a performance issue.

I have been chasing Oracle for a time now, but I am amazed about the difficulty trying to assess its performance.

In a couple of weeks (after the performance training) I could have changed my mind.

Bye
Rick Denoire
>Regards
>HJR
Received on Mon Sep 15 2003 - 17:46:02 CDT