Re: Got the darn buffer busy waits under control, at last! (for R. Sanchez)

Nuno,

It's a pity that you've managed to chop out the redo size stats from the first report, as these stats don't really look as if they were really comparable - even though the commit count in the same ballpark.

Personally I would have said that the buffer busy waits were a visual irritant, but not a problem. The changes that MAY (and I stress MAY) have caused the beneficial increase are:

    Reduction in size of multiblock read count     Disabling of fast full scans
    Increase in optimizer_index_caching
    Movement of files in file system
rather than the change in spin_count

A common cause of buffer busy waits is excessive table / index scanning, with one process simply waiting for another process to complete its multiblock read so that it can read the same blocks.

The four changes above would:

    Reduce the probability of table scans

    Eliminate the possibility of index multiblock reads

    Reduce the size of each multblock read by reducing     the number of blocks requested.

    Further reduce the wait time on each multiblock reads     by spreading the load on the particular disc.

The various changes in access paths that reduce the amount of sorting, combined with the increase in sort_area_retained_size to reduce the I/O to the temp files would also have had a beneficial effect on the filesystem overload, again potentially having a side effect on the BBW issue.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Zo(t) wrote in message <3d0c63da$0$28007$afc38c87_at_news.optusnet.com.au>...


>Here are a before and an after log. Logs edited

>for confidentiality and security.

>

>Same period, same kinda load.

>Note how much better the waits are for

>the second one.  I've slightly modified the

>file locations between these two, was getting too much

>contention on the earlier one in a single fs.  Now

>is MOL OK.  Didn't affect the heavy use areas, to do

>with something else running on that system.

>

>If you can't get the attachments, drop

>me a line and I'll send via e-mail. Use

>mail below minus ".nospam".

>--

>Cheers

>Nuno Souto

>

>>

>>

>> Don't have statspack, only the older bstat stuff.

>> I've got a few from before and am collecting afters.

>> It's EOM for them now, had to wait a coupla days until things

>settled

>> back to normal load.  Otherwise it's apples and oranges.

>> Will post both this weekend.

>>

>>

>> --

>> Cheers

>> Nuno Souto

>> nsouto_at_optushome.com.au.nospam

>