Re: V$LATCH question

It might also be worth pointing out that spin_gets is the column which tells you
how many times Oracle managed to
acquire a latch without having to go to
sleep after a miss.

Also - you should find on more recent
versions of Oracle that

    sleeps =

        1 x sleep1 + 2 x sleep2 +
        3 x sleep3 + 4 x sleep4

To decide if you need to tune the
latches, (a) check v$system_event
for the event 'latch free'. This is the amount of time lost in latch sleeps;
if this is relatively high, then you may need to investigate latch contention.
(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.

Note - however, that latch contention is usually a side-effect of another more
important issue, such as unsuitable
indexing, poor programming strategies
etc.

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

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Ricky Sanchez wrote in message <3C085322.DADEC3B2_at_more.net>...


>Jim-

>

>Sorry to intervene here, but this is incorrect. A "willing to wait"

>latch has no other work to do until it gets that latch. As such, upon a

>miss it does not go away until some future time. I immediately attempts

>to get the latch again and will continue to do so until it either gets

>the latch or hits the predefined limit of _spin_count. When it hits that

>limit, it then sleeps for a predetermined amount of time.

>

>The spin process is simply a loop. Therefore, from the perspective of

>performance, a miss in inconsequential. Not much more than a few machine

>cycles. A sleep on the other hand, is enormously expensive. The first

>time a latch get sleeps, it will be for 10 milliseconds. Consider

>today's processor speeds and compare 10 milliseconds to less than a

>microsecond. Clearly, sleeps are huge. Moreover, after the process

>sleeps, it performs another cycle of spin get attempts. If it fails

>again to get the latch, it sleeps again, but this time for twice as

>long. Each subsequent sleep will be double the time of the previous

>sleep, up to a maximum of about 2 seconds.

>

>So, in terms of performance, latch misses are insignificant while sleeps

>are *huge*.

>