Re: Performance base line

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Tue, 16 Sep 2003 06:44:48 +1000
Message-ID: <3f66253b$0$13416$afc38c87@news.optusnet.com.au>

Rick Denoire wrote:

> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote:
>

>>I think you forgot statspack :(

>
> OK, statspack!
>
> I was just reading the Oracle documentation about v$ Performance
> Views. It is impressive. It is overwhelming. I ended up confused. I am
> afraid these views are an oversized answer to the question, how fast a
> DB is running. One can find many answers there, I at times don't see
> the corresponding questions. One exception is the v$system_event view,
> from which - as far as I understood - it is possible to see your
> "mileage". What I miss in most of the performance views are the actual
> execution times. And I have lot of difficulties trying to undestand
> the Oracle terminology (consistent gets, pins, latches,
> multiblock-reads, etc.) and how wait events should be interpreted.
>
> But I will give statspack a go.
>
> Thanks
> Rick Denoire
>
> (who desperate needs that Performance Tuning training)

I don't want to get you too depressed, and your instructor might do things differently in any case, but the 5-day Performance Tuning course mentions Statspack briefly, but doesn't dwell on what any of it means, really. It then keeps cropping up a source of specific information in various contexts (such as tuning your SGA, identifying what wait events you have etc etc).

The main thrust of the course is: what would you do, if you had a completely free hand, to design a well-behaved Oracle system from scratch?

You might for example want to implement physical denormalisation in some contexts: so the course explains what Index Clusters and Materialized Views are. You might want to parallelize operations as much as possible, so the course explains what Partitioning is, and how that can help achieve that. You might want to implement truly useful indexes, so it goes over what various index options you have. Lots and lots of design work, in other words... which usually takes me through to the Wednesday.

Then it talks about statistics collection and SQL statement tuning (again, assuming you have a free hand in specifying what code the application issues).

It also goes into SGA tuning, though these days it (happily) steers clear of hit ratio tuning lunacy.

The point about the V$ views is well-taken: there are an awful lot of them (though in the course, you'll see a handful crop up time and time again), but they are -by themselves- pretty useless, since their numbers are cumulative since instance startup. When you query one of them and it tells you you have 3 million buffer busy waits, the usual reaction is to have a heart attack... but if your instance has been running for 18 months uninterrupted, 3 million is probably peanuts. That's why statspack is so useful... it allows you to snapshot the v$'s at one time, snapshot them again at another time, and perform a difference between them... so you discover you had 3 million buffer busy waits at 9.00am, 3 million and two at 11am, so now you know that 2 buffer busy waits happened during the busiest period of your working day... and can therefore ignore them.

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.

Regards
HJR Received on Mon Sep 15 2003 - 15:44:48 CDT