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

Interesting to note that this Statspack extract has a much higher redo size than the other one, and a very much higher number of
buffer busy waits - which could be cause and effect I doubt if you are going to be able to resurrect a sufficiently large chain of stats outputs that prove that the _spin_count
change was definitely the critical factor. (And if you did, I'd only duck out of reading them all anyway).

One of the reasons I view statspack with extreme caution is that it is still only taking a large-scale average of statistics. It took me about 15 minutes yesterday to come up with a couple of bits of code which individually are catastrophic, but result in statspack reporting near-perfect database behaviour when they run in combination.

One of the nicer things about statspack, though is that if you run off the report for every adjacent pair of intervals, you can grep for (say) "buffer busy wait" and "redo size" from all of them, and check very quickly for any correlation between results.

(The answer to the point and question below is 'yes, possibly' - remember you only have to have one sluggish scanner causing the BBW, and a set of concurrent scanners could ALL end up reporting 'the same' BBW at the same time. I think in 8.0.6 you might get an interesting result if you try running the following script from just three concurrent processes:

    select * from source$ where length (source) = -1;
    select * from source$ where length (source) = -1;
    select * from source$ where length (source) = -1;
    select * from source$ where length (source) = -1;
    select * from source$ where length (source) = -1;
    select * from source$ where length (source) = -1;
    select * from source$ where length (source) = -1;

I think you should find a reasonable number of BBWs appear quite readily. For some reason, the result is not so nasty in 8.1.7).

--
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 <3d0dc1b4$0$28005$afc38c87_at_news.optusnet.com.au>...


>>

>> 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.

>

>

>Yeah, but would it be enough for such a dramatic reduction?

>We're talking in the same daily period a reduction from

>around 60000 average (default spin count) to 14000

>(spin count = 3000) to around 300 (spin count=4000)?

>That is a very large reduction for such a simple change as

>the optimizer_index_caching, no?  The other parameters were

>introduced one at a time and caused no reduction whatsoever.

>Spin count dropped them dead in this instance.

>