- Overview of Latch and Mutex Architecture
- Measuring Latch and Mutex Contention
- Latch and Mutex Scenarios
- Intractable Latch Contention
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Intractable Latch Contention
We often see latch contention—especially cache buffers chains latch contention—in the most highly tuned, high-powered databases.
This makes sense if you think about it. If we create a database configuration in which all other constraints are removed on database performance (locking, I/O, memory, CPU), then database sessions will essentially be competing for access to shared memory alone. In that scenario, latch contention will inevitably become the limiting factor.
So it may be that some degree of latch contention—especially on the cache buffers chains latch—has to be accepted in very high-throughput systems on premium hardware. When we hit this sort of intractable latch contention, we may have no other option than to attempt to manipulate the parameters that control the Oracle internal algorithms that control latching.
Fine Tuning Latch Algorithms
As we’ve noted, latch requests often repeatedly attempt to acquire a latch (a spin get) before surrendering the CPU and going into a latch sleep. If all else fails, fine tuning the number of spins may improve throughput for an application.
Figure 16.1 shows the results of experiments in which the variable _spin_count, which controls the default number of spins, was varied for an Oracle 11g database suffering from library cache latch contention (http://bit.ly/1fyARb5). In the figure, you can see that as the parameter _spin_count increases, application throughput may also increase. This occurs at the expense of overall CPU consumption, but provided there is free CPU, increasing the _spin_count has a beneficial effect.
)
Figure 16.1 Relationship between _spin_count, latch waits, and throughput
Latch expert Andrey Nikolaev has shown similar results manipulating _mutex_spin_count (http://arxiv.org/pdf/1212.6640v1.pdf) and _spin_count (http://arxiv.org/pdf/1111.0594v1.pdf).
It remains true that if you are unable to resolve latch contention in any other fashion, you might try manipulating the amount of spinning that Oracle performs on a latch. Unfortunately, this option has become less and less attractive as Oracle changes the underlying spin algorithms.
In the past (prior to Oracle Database 11g), you could manipulate spins using the single dynamic parameter _spin_count. Today that approach is suitable only for shared mode latch gets. Tuning exclusive latches requires manipulation of the _latch_class_0 parameter and will require a database restart. For mutexes, three parameters control spin behavior: _mutex_spin_count sets the number of spins before yielding or sleeping, while _mutex_wait_scheme and _mutex_wait_time control the waiting behavior. See Oracle Bug 10411618 for a description of how these parameters work.
In short, if all else fails, you may try to manipulate latch spinning to alleviate latch contention. However, doing so involves manipulating undocumented parameters and should be done as a last resort with extreme care.