Spotlight on Oracle – Relieving Latch Contention

Relieving latch contention

Operations that affect the contents of the SGA require that a process acquire a latch. A latch is similar to a lock, but instead of preventing two sessions from concurrently changing the same row, a latch prevents two sessions from altering the same area in shared memory at once.

Latches are usually held for a very brief interval. In a healthy database, there should be little or no contention for latches. Unfortunately, very busy databases often suffer considerably from latch contention.

If a process requires a latch and cannot obtain it on the first attempt, a latch miss results. The session repeatedly attempts to obtain the latch up to the value of the configuration parameter spin_count. This technique is known as acquiring a spin lock. If the session still cannot obtain the latch, then the session relinquishes the CPU and a latch sleep results. A latch sleep is recorded as a latch free wait. When the session wakes up, it repeats the attempt to obtain the latch.

The latches that contribute to a high proportion of misses or sleeps deserve attention. Not surprisingly, the latches that are used most heavily (and which typically suffer the most contention), are the latches associated with the three major areas of the SGA. They are:

• Buffer cache latches
• Library cache latches
• Redo buffer latches

For more information on latch contention, see Spin count and latch sleeps below.

Spin count and latch sleeps

If a session sleeps (because it cannot obtain a latch), response time is significantly degraded. You can decrease the probability of the session sleeping by increasing the value of the configuration parameters _SPIN_COUNT (also called _LATCH_SPIN_COUNT). This parameter controls the number of attempts the session makes to obtain the latch before sleeping.

Spinning on the latch consumes CPU. If you increase this parameter, you may see an increase in your systems overall CPU utilization. If your computer is near 100% CPU utilization, and your application is throughput rather than response time driven, you could consider decreasing _SPIN_COUNT in order to conserve CPU.

Adjusting _SPIN_COUNT is a trial and error process. In general, only increase this parameter if there are ample free CPU resources available on your system, and decrease it only if there is no spare CPU capacity.