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

In article <1023890293.14326.0.nnrp-12.9e984b29_at_news.demon.co.uk>, you said (and I quote):
>
> 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.

Absolutely. With V6 it was something we all fiddled with, but there was really no body of knowledge as to why. Since then, I've been very reluctant to touch it. Strictly reserved to "last resort". In this case it worked, but it's definitely not something I'd recommend as a habit. Particularly after hearing what you and Anjo had to say about it.

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

Yes, definitely. The 8.1.7 upgrade is planned for EOY. It's gonna be fun. I've asked for a solid week of testing: made so many tweaks to this 8.0.6 over the years I'm quite sure half will be completely irrelevant in the later version. It's gonna be a completely new experience. I'm trying to push for 9i, but if I get 8i I'll be happy. At least I know it will be more stable than this 8.0.6.

> I guess the point you picked up from Anjo is that a
> latch sleep takes place if a latch spin fails.

And where the trade-offs and sweet spots are more likely to be valid. Mostly to do with load on the system, number of concurrent connections, etc. Very useful. Helped me make up my mind to try it out in this case.

> acquisitions (I assume that your typical number of
> latch sleeps somewhere has dropped) should have

Yes, that is correct.

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

That is my gut feel. No way I can prove it other than by indirect observation. Unfortunately, the waits are so rare and far between now that I have no chance whatsoever of trapping a "live" one, other than by accident. So I have to go by what happened to the overall counters, the CPU use, the app throughput.

BTW, is there any other way? I'm not aware of any to reliably trap the remaining few bbwaits, using the common search on session-waits. And I found nothing to help me in v$bh or v$cache. Haven't looked in the X$ stuff yet other than what is in Steve's site.

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

In this case I suspect it was just a bug in the optimizer. A simple delete with a IN subquery was hanging indefinitely. No CPU used, no I/O, nothing. I suspected a lock, of course. But it wasn't.

Interestingly, I was getting increased bbwaits. Then I tried to replace the DELETE with a SELECT, still with the subquery. To eliminate the possibility of a lock. Same problem, minus the bbwaits. Explain plan indicated there was a fast full scan taking place. So I turned it off and it went through in less than 5 seconds. Tried the DELETE and same. Bottom line: out goes the FFS, (but only in 8.0.6!).

There was very little other SQL using it anyway. One of the things I like to do every now and then is to extract the heaviest and the most used SQL off v$views. And run it all through explain plan. It gives me a good feeling of how well execution is going on critical SQL. And helps me trap SQL that according to developers "has NOT been changed!"...

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam