参考自:
WAITEVENT: "library cache: mutex X" (Doc ID 727400.1)
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
11g及其更高的版本:
若是用以上找不到blocker,那就有可能是bug,
请参考:WAITEVENT: "library cache: mutex X" (Doc ID 727400.1)文档中的"Known Bugs"部分.
WAITEVENT: "library cache: mutex X" (Doc ID 727400.1)
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
awr报告中搜索"Mutex Sleep Summary",定位到Mutex Sleep Summary部分,也能获取一些有价值的东西,如下:
Mutex Sleep Summary
- ordered by number of sleeps desc
Mutex Type | Location | Sleeps | Wait Time (ms) |
---|---|---|---|
Library Cache | kglpnal2 91 | 532 | 0 |
Library Cache | kglpin1 4 | 147 | 0 |
Library Cache | kglhdgn2 106 | 144 | 0 |
Library Cache | kgllkdl1 85 | 141 | 0 |
Library Cache | kglhdgn1 62 | 129 | 0 |
Library Cache | kgllkc1 57 | 95 | 0 |
Library Cache | kglpndl1 95 | 83 | 0 |
Library Cache | kglget2 2 | 72 | 0 |
Library Cache | kglpnal1 90 | 61 | 0 |
Library Cache | kgllkal1 80 | 44 | 0 |
Cursor Pin | kkslce [KKSCHLPIN2] | 41 | 0 |
Library Cache | kglget1 1 | 22 | 0 |
Cursor Pin | kksLockDelete [KKSCHLPIN6] | 17 | 0 |
Cursor Pin | kksfbc [KKSCHLPIN1] | 15 | 0 |
Cursor Pin | kksfbc [KKSCHLFSP2] | 13 | 0 |
Library Cache | kglhdgh1 64 | 12 | 0 |
Library Cache | kgldtin1 42 | 11 | 0 |
Library Cache | kglrfcl1 79 | 9 | 0 |
Library Cache | kglobpn1 71 | 7 | 0 |
Cursor Parent | kkscsAddChildNode [KKSPRTLOC34] | 3 | 0 |
Cursor Parent | kkscsPruneChild [KKSPRTLOC35] | 3 | 0 |
hash table | kkscsSearchChildList [KKSHBKLOC2] | 1 | 0 |
hash table | kkshGetNextChild [KKSHBKLOC1] | 1 | 0 |
找到blocker:
SELECT SQL_ID, ACTION, BLOCKING_SESSION, BLOCKING_SESSION_STATUS
FROM v$session
WHERE SID=&SID_OF_WAITING_SESSION;
BLOCKING_SESSION列就是mutex x的持有者session
11g及其更高的版本:
10g版本:
SELECT decode(trunc(&&P2/4294967296),
0,trunc(&&P2/65536),
trunc(&&P2/4294967296)) SID_HOLDING_MUTEX
FROM dual;
若是用以上找不到blocker,那就有可能是bug,
请参考:WAITEVENT: "library cache: mutex X" (Doc ID 727400.1)文档中的"Known Bugs"部分.
另外 的参考文章:
Troubleshooting Databases Hang Due to Heavy Contention for 'library cache: mutex X' Waits (Oracle 11.2 and Later) (Doc ID 2051456.1)