11g等待事件之library cache: mutex X

library cache: mutex X替代了之前的library cache latch，主要作用是在hash bucket中定位handle時使用。（比如SQL硬解析時，需要往hash bucket中新增一個cursor時，需要library cache latch）。如下圖所示:

<a href="http://www.dbafree.net/wp-content/uploads/2012/07/a.jpg"></a>

文檔上面的解釋如下：

The library cache mutex is acquired for similar purposes that the library cache latches were acquired in prior versions of Oracle. In 10g, mutexes were introduced for certain operations in the library cache. Starting with 11g, the library cache latches were replaced by mutexes, hence this new wait event.

Mutexes are a lighter-weight and more granular concurrency mechanism than latches. Mutexes take advantage of CPU architectures that offer the compare and swap instructions (or similar). The reason for obtaining a mutex in the first place, is to ensure that certain operations are properly managed for concurrency. E.g., if one session is changing a data structure in memory, then another session must wait to acquire the mutex before it can make a similar change – this prevents unintended changes that would lead to corruptions or crashes if not serialized.

This wait event is present whenever a library cache mutex is held in exclusive mode by a session and other sessions need to wait for it to be released. There are many different operations in the library cache that will require a mutex, so its important to recognize which “location” (in Oracle’s code) is involved in the wait. “Location” is useful to Oracle Support engineers for diagnosing the cause for this wait event.

Individual Waits:

Parameters:

P1 = “idn” = Unique Mutex Identifier

P2 = “value”

P3 = “where” = location in code (internal identifier) where mutex is being waited for

@The meaning of the code for “where” can be found by looking in kgl0.h for entries with the prefix “”kglml_XXX”. For example, if P3=2, then it corresponds to “kglml_kglget2″. You can then search source code for this symbol to see where the mutex is acquired.

測試一個硬解析的場景如下：

生成awr report，檢視等待事件：

我們的hard parse

查了下metalink，與"library cache: mutex X"等待相關的BUG大約有30多個，會在11G各個版本之間發生，是以碰到這個等待事件，可以先确認下是不是碰上BUG了。以下兩個BUG，在網上好象大家經常碰到，給出metalink相關的描述：

Bug 5928271 - Excessive waits on "library cache: mutex X" [ID 5928271.8]

Versions &gt;= 11.1.0.6 but BELOW 11.2

This fix alleviates some waits on "library cache: mutex X" when looking

up a library cache object.

Bug 9530750 High waits for 'library cache: mutex X' for cursor Build lock

Versions BELOW 12.1

High waits may be seen for "library cache: mutex X" for

a build lock mutex with a call stack including kksGetBuildLock.

Rediscovery Notes:

The mutex is for an object with a name like "$BUILD$.xxxxxxxx"

參考：

metalink相關文章

《Oracle Wait Interface A Practical Guide to Performance Diagnostics &amp; Tuning》

《Oracle Core_ Essential Internals for DBAs and Developers - Jonathan Lewis》http://blog.tanelpoder.com/2008/08/03/library-cache-latches-gone-in-oracle-11g/