Tuning Contention - Oracle Database 11gR2 Performance Tuning

Databases Reference

In-Depth Information

Single processor machines are being replaced by multi-core

machines. However, when using Instance Caging, we can

have single processor/core assigned to a database instance.

In these situations, the considerations made for single

processor machine are still valid.

On multiprocessor/multicore systems, another mechanism is in place. The requesting process

can "spin", which means it holds the CPU and counts to a specific number, consuming CPU

cycles but not releasing the CPU, avoiding a costly context switch. This behavior allows

processes executed on other machine cores to proceed and eventually release the required

latch. Obviously, a high number of "spins" affects performance, and the maximum number

of spins a process can execute when waiting for a latch before releasing the CPU is platform

and OS specific. In single core/processor systems, spinning makes no sense (when a process

"spins" no other processes are executed, which results only in wasting CPU time).

We have referred to two different types of latches: willing-to-wait latch requests and immediate

latch requests. The difference is in the behavior of the process when the latch is in use. In

willing-to-wait requests the process sleeps and then acquires the latch again, when it becomes

available. In immediate requests, the process not acquiring the latch will eventually release the

latches already acquired—to avoid deadlocks—and releases the CPU before trying to acquire

the latches again.

The query used in this recipe investigates the top 10 requested latches, showing the misses

for both willing-to-wait and immediate (also referred as no-wait) latch requests.

The answer to the question titling this recipe is "NO", we don't tune latches; we investigate

possible latch contentions using the statement in this recipe and then we tune the resources

causing latch contention.