11.2.0.2 is the right patchset for mutexes

Many people asked me about the second part of my blog title – the mutex. This is the first post about it. Mutexes is another Oracle spinlock, which was appeared in version 10.2.0.2. Despite being known since 2005, Oracle mutex internals is still Terra incognita.

This post is inspired by several recent escalations due to mutex contention. It occurs that 11.2.0.2 patchset contains extraordinary number of mutex related changes. Some enhancements like 10411618 exist only for 11.2.0.2. The following patches even changed the mutex architecture:

• Patch 9499302 IMPROVEMENTS TO KGX MUTEXES (USING VOLATILES & END WAIT BEFORE MUTEX GET). With this patch Oracle mutex become TTS spinlock. Mutex contention no longer saturates the bus and “poisons” overall system performance
• Patches 9239863 and 9282521: “LIBRARY CACHE: MUTEX X” FOR OBJECTS HIGHLY CONTENDED FOR. These enhancements introduce new possibilities to diagnose and tune the mutex contention. Now contention can be divided between multiple copies of the objects in the library cache. Patches are tuned by new parameters “_kgl_hot_object_copies” and “_kgl_debug”
• Patch 10411618 ADD DIFFERENT WAIT SCHEMES FOR MUTEX WAITS. This patch completely changed the mutex waits and introduced exponential backoff. With this enhancement some of the mutex waits work like Oracle 8i latch waits. The patch tunables are “_mutex_spin_count”, “_mutex_wait_time” and “_mutex_wait_scheme” parameters.
• Patch 6904068 High CPU usage when there are “cursor: pin S” waits. It introduced “_first_spare_parameter” to tune the duration of “cursor: pin S” waits.

Other most notable mutex related 11.2.0.2 fixes are:

• Patch 10145558 Selects on library cache V$/X$ views cause “library cache: mutex X” waits. This particular patch decreases mutex contention induced by AWR snapshots capture.
• Patch 8793492 Mutex Waits with Resource Manager. Patch resolved mutex contention spikes due to CPU caging
• Patch 7441165 Prevent preemption while holding a mutex (Solaris)
• Patch 9881963 Linux: Improve chance to release CPU during mutex get spins

Complete list can be found in new interesting MOS note WAITEVENT: “library cache: mutex X” [ID 727400.1].

It seems that 11.2.0.2 (plus latest PSU) is the number one recommendation for systems having mutex contention. Oracle provides us with new tools to diagnose and resolve mutex contention. In the next posts I will discuss how the mutex works and why these patches are so crucial for mutexes.

Addition as of 25-MAY-2011:
On 20-MAY-2011 My Oracle Support issued the recommendation to install
Patch 12431716 Mutex waits may cause higher CPU usage in 11.2.0.2.2 PSU / GI PSU
for all 11.2.0.2.2 installations. For previous releases Oracle recommends to reinstall the interim Patch:10411618 if you have installed it.