The problem is not spin_count. The problem is the speed of the CPU and the granularity of the sleep time (1/100 sec). This has stayed the same since version 6. But since version 6 (1988), the speed of cpu has increased dramatically. In fact it is becoming obvious that most latch free waits are waiting on nothing. The latch is already free and the process is waiting on nothing.
If you actually want to improve the latch contention, either go to post/wait (whn the latch is freed the process will post the first waiter) or fiddle around with _latch_classes and _latch_class. I have played with setting them, but haven't checked if they are really doing what they are supposed to do. The potential cool thing here is that you can specify the number of times you want yield (return to the end of the run queue) the cpu, then how often you want to spin and then want to wait.
In all these cases you are fixing symptoms and not the real problems. Look at your app, that is where you will fix the problems.
Thanks very much foor the reply.
Since spin_count is just a numbe rof loops, if we have a faster CPU shouldnt we increase the value? I mean running 2000 loops in a 3GHZ CPU is definitely not the same as running in a 500MHz CPU. In fact i recall, Guy Harrison wrote a latch related paper mentioning that since 2000 for spin_count has been used since Oracle 7, that is 10 - 13 years ago a higher value for modern CPU wouldnt surprise.
Regarding the CPU usage, if with 8 500MHz CPU I consume 30000 seconds CPU in 1 hour, if I replace those with 1GHZ CPU would I reduce my CPU time/usage to half?
On 9/4/06, Tim Gorman <tim@xxxxxxxxx> wrote: > > Spin count has little to do with CPU cycles, and everything to do with > the duration of non-pre-emptive waits on latches (i.e. column SPIN_GETS > on V$LATCH). It is literally the count in a loop that Oracle spins > while waiting non-pre-emptively ( i.e. without losing the CPU) for a > latch. Really, really bad feng-shui to mess with spin count. > > Oracle cannot record if your CPU is working "efficiently" or not. All > it can do is report on how much CPU time was consumed. If you do the > math, 3 million centi-seconds is 30,000 seconds. An hour has 3,600 > seconds, so eight CPUs can use 28,800 seconds in an hour. It pretty > much adds up to Oracle consuming all the CPU on your server, which is OK > if it is the only thing on the server. As far as the discrepency > between 28,800 and 30,000, don't expect too much accuracy from a > mechanism that is constantly rounding or truncating micro-seconds to the > nearest centi-second... > > > amonte wrote: > > Hi > > > > I was wondering if anyone know how is spin_count measured? In CPU > CYCLES? > > > > I havea doubt with CPU usage as well, I undersstand that in v$sysstat > > it is using centiseconds to measure CPU usage. I wonder how can we > > determine 1 centisecond is equivalent to how many CPU cycles? For > > example I have a server with 8 PA-RISC 1200 MHz CPU, in 1 hour it > > reports 3 million centiseconds usage, am I using fully? Basically is > > how do you know how efficiently is your CPU working from Oracle > > Statistics. > > > > TIA > > > > Alex > > > > -- > -- > -Tim Gorman > consultant - Evergreen Database Technologies, Inc. > > website = http://www.evdbt.com > email = tim@xxxxxxxxx > mobile = +1-303-885-4526 > fax = +1-303-484-3608 > > -- > //www.freelists.org/webpage/oracle-l > > >
-- Anjo Kolk Owner and Founder OraPerf Projects tel: +31-577-712000 mob: +31-6-55340888