Re: Got the darn buffer busy waits under control, at last!

You have to be VERY cautious about fiddling with spin_count, as the things it 'fixes' tend to be side-effects of the real problem. But sometimes you can hit a winner.

Your example is one where the problem might have gone away if you had upgraded to 8.1.7, or it might have got catastrophically worse.

I guess the point you picked up from Anjo is that a latch sleep takes place if a latch spin fails. In all the years that Oracle has been running, a latch sleep has been set at (usually) 1/100 sec and the default spin_count has been set to 2,000. Consequently a spin has been getting shorter and shorter relative to a sleep as CPUs get faster and faster. In theory, therefore, you could argue that the spin count should be increased to make the trade-off between spinning and sleeping closer to the original ratio.

It's not obvious to me why an improvement in latch acquisitions (I assume that your typical number of latch sleeps somewhere has dropped) should have a dramatic effect on 'buffer busy waits'. But I guess it's a side-effect that processes that used to go to sleep whilst pinning buffers in exclusive mode are now continuing to run, and therefore completing their pins before they block other processes' attempts to pin the block.

Of course, this is another nice example of choosing to burn CPU to reduce contention - this is the choice you always have to make with spin_count, and its a choice that often does not work.

As far as the index_ffs are concerned - it is inherent in both index_ffs and table scans that if multiple processes are doing them at the same time, then later processes will catch up with earlier processes and start displaying buffer busy waits. In this case, the scan is the culprit, and the "buffer busy wait" is a "good" wait, because the later processes are managing to take advantage of an earlier scan - the alternative would be waiting to do a disk read.

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

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

Nuno Souto wrote in message
<3d074393$0$28009$afc38c87_at_news.optusnet.com.au>...


>In one of the PS Fin databases.  At last.  Had been driving me nuts for

>AGES!!!!

>

>No matter what I did the darn waits were going at around 200000/day.

>Annoying.

>During the last DBForum here in Sydney I had an interesting insight with

>Anjo Kolk.  He explained what could be ticking. I think Jonathan heard

>this as well, he might still remember it?

>

>And indeed it was the spin count.  As soon as I popped it from 2000 to

>4000, the buffer busy waits went out the window.  Tried first with 3000

>and there was a BIG improvement.  4000 proved to be the sweet spot.

>

>Conditions:

>8.0.6, running in 8.0.6 compatible mode.

>HPUX 11.

>4CPU HP server, 2Gb mem, EMC disk farm.

>Between 150 and 200 concurrent connections, no MTS.

>Buffers at 24000, with 2000 allocated to keep and 2000 allocated to

>recycle (I know it should be less, but it works well with this particular

>load).

>All the heavy output tables AND indexes have had FREELISTS 4 INITRANS 4

>applied.

>

>Before the change the average CPU use was 33% overall.  It jumped to 35%

>after.  Ie, I didn't kill one problem and create another.  Buffer busy

>waits dropped to under 200/day.  Customer claims the system is overall

>noticeably faster, with extra capacity available during the peak period

>(12:00 to 15:00).

>

>Another thing that helped: disabled index fast full scans.  They were

>fairly new (read: flaky) at 8.0.6 and were stuffing up the odd SQL here

>and there.

>

>This alone was worth the price of the forum.  Thanks a million, folks.

>

>--

>Cheers

>Nuno Souto

>nsouto_at_optushome.com.au.nospam