Re: Latch spin - OS timeslice

In principle the simplest argument is that it should increase with the CPU speed, as it is (should be) set as a double trade-off:

First between the CPU cost of the context switching as your session goes to sleep and wakes up again, and the loss of CPU spent in spinning.

Secondly, as the sleep is coded to 1/100 sec minimum, it should arguably be set so that 'opportunity cost' of that lost time is related to the CPU cost of spinning.

On a CPU at 200MHz, a sleep is typically equivalent to 'not doing' 200 logical I/Os. On a CPU at 500MHz, a sleep is typically equivalent to 'not doing' 500 logical I/Os. Therefore you should be prepared to waste a little more CPU spinning in order to avoid missing the opportunity for doing so much extra work.

>I'm thinking: like so many other things, it's highly dependent on your
>mix of use. But worth a poke here and there as it can help.

But your comment above is the most important one. Despite any theoretical argument, you always have to face the real world - which tends to produce the counterargument  with startling ease.

If you have 2 CPUs and 20 concurrent sessions - then when you start spinning, there are 19 different sessions which might be holding the latch you want, and 18 of them are also spinners that might get it before you do - so there is a reasonable probability that increasing the spin-count will waste CPU without saving anything.

Bottom line - there are two possible performance problems - excessive CPU usage with minimal time lost on sleeps (latch free waits), or excessive sleeps on latches with plenty of spare CPU. If all else failed, the former might suggest reducing the spin_count cautiously, the latter might suggest increasing the spin_count cautiously.

Interesting point - Oracle 9 introduces shared read latches to reduce the potential impact of latch collision; and also may reduce the sleep times to microsecond levels in response to increased CPU speeds in 9.2.

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

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

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


Nuno Souto wrote in message <3ca3ccee.5955644_at_news-vip.optusnet.com.au>...


>

>Should it increase or decrease with a faster CPU?

>

>I've found that increasing it slightly can work wonders to get rid of

>buffer busy waits, at the expense of a slight increase in CPU use.

>From 2000 to 3000, for example.

>

>I'm thinking: like so many other things, it's highly dependent on your

>mix of use.  But worth a poke here and there as it can help.

>But it is definitely arbitrary.  I think over the years it's one of

>those from Oracle that hasn't received much attention.  Used to be all

>the rage in V6 to change its value to astronomical numbers.  Then it

>fell into disuse with V7.  Like so many other parameters, it just

>needs to be taken into consideration, that's all.

>