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

Since _spin_count affects all latches equally, and of course latch usage is not at all "equal" throughout the instance, the risk of users messing with this parameter is serious as both you and Nuno have pointed out. I sure hope other readers don't get that "I feel lucky" thing now.

And certainly things change dramatically with 8i. The buffer cache looks quite different with respect to the hash table and latches. And of course, newer and more interesting bugs come with it. :-)

I would be curious to see how the instance was affected otherwise. Was latch_free a major wait event prior to the parameter adjustment? Has overall latch_free wait time actually gone down as a result? What about sleeps for other latches? Did we trade cache buffers chains latch performance for library cache latch?

It would be useful to see before and after statspack reports rather than have to rely on anecdotal evidence. Nuno- you have statspack running on that thing? Post a zipped 30 minute busy period snap if you have one.

• ricky

Jonathan Lewis wrote:
>
> 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.
>

> 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.
>
Received on Thu Jun 13 2002 - 21:05:20 CDT