Re: V$LATCH question

A slight correction...

Jonathan Lewis wrote:
>
> (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.

Actually, if you are CPU-bound, then you need to fix that problem before addressing latches. If spinning is so high it is causing CPU issues - as measure from SAR, perhaps - then you are pretty much CPU-bound anyway and need to fix that problem before addressing Oracle internals. Latch spinning/missing is so low in CPU consumption that any resultant problem is an indicator of marginal CPU capacity. You are really dancing on the line at that point and can't fix the problem by tweaking Oracle.

Really serious latch contention is inevitably going to result in high sleeps, which will cause waits at the session level but are not going to consume additional CPU. Specifically, sleeping yields CPU.

On the other hand, although a spin/miss cycle consumes CPU, it is pretty minor. If it actually measurably contributes to being CPU-bound, then CPU capacity is so marginal that you really have to fix the hardware first.

Also, some remediation techniques include adding latches to some structures, either via hidden parameters or bumping things like db_block_lru_latches. If you are truly CPU bound, this tends to be counterproductive, since more latches to manage generally means more work for the CPU. Another reason why you really have to address the hardware and operating system issues *before* you can deal with Oracle tuning.

Once you have addressed the hardware issues, perhaps by replacing slow CPUs with faster hardware, you are likely to find that the dynamics of the instance have changed altogether. You may no longer have a latch problem.

Put another way, if performance is a problem, as witnessed by high "latch free" waits, pay attention to the "sleepiest" latch and ignore misses and spin-gets altogether.

• ricky