In this chapter, we looked at how latches and mutexes work and why they are a necessary part of the Oracle architecture. We learned how to diagnose the root causes of latch and mutex contention and explored remedies for common latch contention scenarios.
Latches and mutexes protect areas of Oracle’s shared memory, preventing corruption or inconsistencies that might arise if multiple sessions were to change the same area of shared memory at the same time.
Latch internal algorithms are complex and change frequently. However, most latch contention indicates a need to reduce demand by the application on shared memory. The following are the two most common causes:
- Hard parsing, in which new SQL statements are constructed for each change in a query parameter. The use of bind variables or the CURSOR_SHARING parameter may be indicated.
- Very “hot” blocks in the buffer cache, suggesting a need to partition a table or tune SQL.