Re: V$LATCH question

Well, actually, I was correcting this statement here:

...(b) if the CPU is completely overloaded AND the number of misses is very high,
and most of those misses are acquired
through spin_gets, then you MAY be
losing too much CPU to latch contention, and need to investigate.
...

By suggesting "losing too much CPU to latch contention", you seemed to imply there could be a "regular" latch contention problem here, which is highly unlikely. I see what you meant though, so I will elaborate.

Your scenario gives that the CPU is completely overloaded. In such cases, latch spins are not the fundamental problem. Oh yes, someone can set _spin_count to a ludicrous value and make things extreme, but that is because someone has already taken the wrong path in a performance tuning effort. In such a case you 1) bitch-slap the dummy who decided to SWAG a tuning effort and 2) reset to default values and start over, first by checking the pulse of the underlying box.

I suspect we basically agree on the approach. I only wanted to clarify that a swamped CPU is fundamentally a different problem that can't be "tuned" away.

I have only seen once case where a _spin_count adjustment actually helped resolve a problem. In virtually all other cases any change from the default value makes things worse. Inevitably, such changes are based on "black magic" hand-me-down knowledge from dbas or consultants who were in fact complete dolts. And there are a few out there. :-)

> On a more serious note - you mentioned that
> sleeps are for 10 milliseconds - but all the
> measurements I make on HP suggest that
> the sleep time on the cache buffers chains latch
> is 20 milliseconds. Is this something that may
> vary with platform ?

Not sure, I'll get back to you on that one. Regardless, as you know, they increase exponetially.

• ricky

>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
Received on Sat Dec 01 2001 - 12:45:52 CST