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

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