Re: Looking for Statspack documentation

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 6 Feb 2003 09:40:50 -0000
Message-ID: <3e422da2$0$247$ed9e5944@reading.news.pipex.net>

"Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message news:1efdad5b.0302050957.559e4bb2_at_posting.google.com...
> Other than what is on OTN... There was an article in Oracle Mag
> recently that discussed some of the most prevelent wait events and how
> to generically fix them.

There was, unfortunately it was a mix of good and not so good, to take two examples

3. Free Buffer. This indicates your system is waiting for a buffer in memory, because none is currently available. Waits in this category may indicate that you need to increase the DB_BUFFER_CACHE, if all your SQL is tuned.

It is easy to read this as 'If I have significant free buffer wait events I need to increase the size of the SGA'. This may well not help at all. "if all your sql is tuned" is a hell of a caveat as well.

4. Buffer Busy. This is a wait for a buffer that is being used in an unshareable way or is being read into the buffer cache. Buffer busy waits should not be greater than 1 percent.

I have read the first sentence a number of times, I'd be grateful if someone could explain what it is trying to say.The second sentence would make ever so much more sense if it said what the 1% figure referred to.

>
> Anyone know of any websites that go into more depth on this? There is
> a hardcover statspack book out. Is it any good?

Take a look at oraperf.com, they have a neat parser that will break apart your statspack report and give you a pretty decent HTML report with recommendations. There are other good resources on the site as well

to illustrate what I mean the following comes from a report on one of my statspack reports.

###########################

buffer busy waits
This event normally happens because one session is waiting for another session to read a block from disk or one session is waiting for another session to finish changing on the buffer. Depending on the class of the block different tuning options exist.

Buffer Busy Wait Statistics per Block
ClassBlock ClassCount Time

data block    55366            8570
undo block     1238                16
undo header     880

data block
Normally this indicates contention on datablocks due to the fact that multiple processes are inserting data into an object with not enough freelists. Adding freelists to an object means rebuilding the object. The maximum number of freelists is normally dependent on the block size of the database (db_block_size). For your object choose a prime number for the number of freelists as the processes are hashed by Oracle PID over the freelists.
The other reason why this contention happen is that multiple processes are accessing the same rows in a block/buffer. So reducing the number of rows per block will help to reduce the contention on the buffer. undo block
Contention on undo blocks could mean that you don't have enough Rollback segments. Adding more rollback segments will help to reduce the contention. undo header
Contention on undo headers could mean that you don't have enough Rollback segments. Adding more rollback segments will help to reduce the contention.

################################

Personally I find this much clearer and more helpful than the equivalent in the Magazine article.

The other thing to bear in mind, and this is emphatically not the fault of the article, is that you need to assess how long you are waiting in comparison to how much time you are spending doing work. If 90% of your time is spent in CPU cycles tuning the waits is not going to be an effective approach.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

Received on Thu Feb 06 2003 - 03:40:50 CST