"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3e422da2$0$247$ed9e5944_at_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.
So I take it anytime I see something by Niemic, I should take it with
a grain of salt. Alot of people hate his performance tuning book and
now that I have read several others I see the weaknesses.
Received on Thu Feb 06 2003 - 11:16:16 CST
