WAITEVENT: "library cache: mutex X" (文档 ID 727400.1)

2014-01-19 09:56 2367人阅读 评论(0) 收藏 举报

 分类：

 

网络资源_ORACLE_调优（108） 

目录(?)[-]

1. APPLIES TO
2. PURPOSE
3. SCOPE
4. DETAILS
   1. Definition
   2. Individual Waits
   3. Systemwide Waits
   4. Wait Time
   5. Reducing Waits Wait times
   6. Known Bugs
5. REFERENCES
6. 相关内容
   1. 产品
   2. 关键字
   3. 错误

In this Document

 Purpose Scope Details Definition: Individual Waits: Systemwide Waits: Wait Time: Reducing Waits / Wait times: Known Bugs: References

---

APPLIES TO:

Oracle Database - Personal Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Oracle Database - Standard Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.

PURPOSE

This document explains the meaning of the wait event "library cache: mutex" and gives hints on how it may be used to diagnose performance problems.

SCOPE

This is intended for DBAs and developers who are involved with tuning their database. An intermediate level of expertise is required.

DETAILS

This is a reference note for the wait event "library cache: mutex X" which includes the following subsections:

• Brief definition
• Individual wait details (eg: For waits seen in View V$SESSION_WAIT)
• Systemwide wait details (eg: For waits seen in View V$SYSTEM_EVENT)
• Reducing waits / wait times

See Note 61998.1 for an introduction to Wait Events.

Definition:

• @Source Symbol:kglmwexc Versions:11.0 - Documentation:None
• 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 = Mutex "value" = in order to find the mutex value, read note 1298015.1
• P3 = "where" = location in code (internal identifier) where mutex is being waited for 

 

Systemwide Waits:

At a systemwide level, there are two views which will help diagnose this wait:

GV$MUTEX_SLEEP (or V$MUTEX_SLEEP for non-RAC)
and GV$MUTEX_SLEEP_HISTORY (or V$MUTEX_SLEEP_HISTORY for non-RAC)

These views track the instance wide usage of mutexes since instance startup.  Since these views show values that are total values since startup, they are most meaningful when you obtain the difference in values during a short time interval when there was problem.  The easiest way to see this information is through an AWR or statspack report in the "Mutex Sleep Summary" section.



Wait Time:

The waiter waits very briefly before timing out.  It is typical to see 100% timeouts for this wait.



Reducing Waits / Wait times:

The cause for waits will be very specific to a particular library cache object since mutexes are very granular now compared to the old library cache latches. Generally, excessive waits are caused by frequent pinning and unpinning of cursors or objects; this document will be updated as other causes are encountered.



Known Bugs:


Known Bugs

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button: 
           






NBBugFixedDescription 1758848012.1.0.2, 12.2.0.0library cache mutex/shared pool latch deadlock 1623280311.2.0.4, 12.1.0.2, 12.2.0.0Library cache lock / mutex contention with fix 8508078 enabled 1440173111.2.0.3.BP20, 11.2.0.4, 12.1.0.1Cursor leak using "SET TRANSACTION" within PLSQL 1686404211.2.0.4, 12.2.0.0Unnecessary library cache operations for DDLs on partitions in exadata 1640012212.2.0.0Spikes in library cache mutex contention for SQL using SQL Plan Baseline 1585802211.2.0.4, 12.2.0.0Frequent invalidation of tuning objects with VPD 1381039311.2.0.2.10, 11.2.0.2.BP20, 11.2.0.3.6, 11.2.0.3.BP15, 11.2.0.4, 12.1.0.1Deadlock waiting for 'library cache: mutex x' while producing an ORA-4031 diagnostic dump 1372075311.2.0.4, 12.1.0.1Diagnosability issue - mutex wait "idn" value truncated in session wait output in tracefile 1358824811.2.0.2.BP17, 11.2.0.3.3, 11.2.0.3.BP06, 11.2.0.4, 12.1.0.1"library cache: mutex X" contention for objects with very many library cache locks 1297637611.2.0.2.6, 11.2.0.2.BP16, 11.2.0.3.3, 11.2.0.3.BP05, 12.1.0.1High VERSION_COUNT for SQL with binds, including recursive dictionary SQL - superseded 1279742011.2.0.2.11, 11.2.0.2.BP21, 11.2.0.3.3, 11.2.0.3.BP07, 11.2.0.4, 12.1.0.1"library cache: mutex X" waits on DB instance handle with CURSOR_SHARING 1181833511.1.0.7.9, 11.2.0.2.2, 11.2.0.2.BP06, 11.2.0.3, 12.1.0.1Additional support for bug 10411618 to allow dynamic Mutex wait scheme changes 1171915111.2.0.3, 12.1.0.1SQL Plan Management capture causes slowness 1063211311.2.0.3, 12.1.0.1OLS calls cause mutex contention even with low number of concurrent users 1041771611.2.0.2.5, 11.2.0.2.BP05, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1Mutex X waits in 11G on an instance with heavy JAVA usage 1028484511.2.0.3, 12.1.0.1Need index on SID for X$KGLLK and X$KGLPN based views (V$OPEN_CURSOR etc..) 1028483811.2.0.2.5, 11.2.0.2.BP13, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1Cache line contention while waiting for mutexes 1020450511.2.0.3, 12.1.0.1SGA autotune can cause row cache misses, library cache reloads and parsing 1008684311.2.0.3, 12.1.0.1Recursive SQL cursors not reused - PMON crashes instance with ORA-600 [kglLockOwnersListDelete] 953075011.2.0.2, 12.1.0.1High waits for 'library cache: mutex X' for cursor Build lock 923986311.2.0.1.BP06, 11.2.0.2, 12.1.0.1Excessive "library cache:mutex X" contention on hot objects 898105911.1.0.7.4, 11.2.0.1.2, 11.2.0.1.BP06, 11.2.0.2, 12.1.0.1High Version Count (due to USER_BIND_PEEK_MISMATCH) with bind peeking 886019811.2.0.2, 12.1.0.1"library cache:mutex X" waits using XMLType 735277511.2.0.2, 12.1.0.1Many child cursors when PARALLEL_INSTANCE_GROUP set wrong 1267016511.2.0.2.4, 11.2.0.2.BP12, 11.2.0.3Library cache mutex contention caused by lookup of triggers 1263334011.2.0.2.6, 11.2.0.2.BP13, 11.2.0.3Heavy "library cache lock" and "library cache: mutex X" contention for a "$BUILD$.xx" lock 879349210.2.0.5, 11.2.0.1.BP07, 11.2.0.2Mutex Waits with Resource Manager 1014555811.1.0.7.7, 11.2.0.1.BP12, 11.2.0.2Selects on library cache V$/X$ views cause "library cache: mutex X" waits 939868511.2.0.2High "library cache: mutex X" when using Application Context 928252111.2.0.2Excessive "library cache:mutex X" contention on hot objects 914026211.2.0.2ORA-600 [ksliwat5] followed by cpu spike/"library cache: mutex X" Waits 900314511.2.0.1.BP03, 11.2.0.2Dump (kglIsOwnerVersionable) / "library cache: mutex X" waits 750223711.1.0.7.7, 11.2.0.1Unnecessary "library cache: mutex X" waits using stored Java 730797211.1.0.7.2, 11.2.0.1Excessive waits on 'library cache: mutex x' 8431767 High "library cache: mutex X" when using Application Context 931287911.1.0.7.7, 11.2.0.1"library cache: mutex x" waits after killing sessions / PMON slow to clean up 764840610.2.0.5, 11.1.0.7.4, 11.2.0.1Child cursors not shared for "table_..." cursors (that show as "SQL Text Not Available") when NLS_LENGTH_SEMANTICS = CHAR 715586011.2.0.1Spin on kgllkde causes 'library cache: mutex X' 849904311.1.0.7.2SET_CONTEXT incurs unnecessary DLM overhead in RAC 592827111.1.0.7Excessive waits on "library cache: mutex X" 731711711.2.0.1Unnecessary "library cache: mutex X" waits on LOB operations

• '*' indicates that an alert exists for that issue.
• '+' indicates a particularly notable issue / bug.
• 'I' indicates an install issue / bug included for completeness.
• 'P' indicates a port specific bug.
• Fixed versions use "BPnn" to indicate Exadata bundle nn.
• "OERI:xxxx" may be used as shorthand for ORA-600 [xxxx].




---

Another important fix to keep in mind is:
 bug:10411618  ADD DIFFERENT WAIT SCHEMES FOR MUTEX WAITS

There are 3 parameters to control the mutex waits, which can be set dynamically and are only allowed to be set by "alter system set"

* _mutex_spin_count (Integer) - This sets the number of times to spin before yielding/waiting.
* _mutex_wait_time (Integer) - This sets the amount of time in milli seconds to wait or the maximum amount of time to sleep for exponential backoff
* _mutex_wait_scheme (Integer) - This controls which wait scheme to use. It can be set to one of the three wait schemes described above.
o _mutex_wait_scheme = 0 - Always YIELD
o _mutex_wait_scheme = 1 & _mutex_wait_time = t - Always SLEEP for t ms
o _mutex_wait_scheme = 2 & _mutex_wait_time = t - EXP BACKOFF with maximum sleep time set to t ms.

The default in MAIN & the backports is set to exponential backoff with the maximum wait time set to 1 ms. The old default behaviour was mostly yield. So after applying the patch if we need to revert to old behaviour, _mutex_wait_scheme should be set to 0.

REFERENCES

NOTE:5928271.8 - Bug 5928271 - Excessive waits on "library cache: mutex X"

NOTE:7155860.8 - Bug 7155860 - Spin on kgllkde causes 'library cache: mutex X'
NOTE:7307972.8 - Bug 7307972 - Excessive waits on 'library cache: mutex x'
NOTE:7317117.8 - Bug 7317117 - Unnecessary "library cache: mutex X" waits on LOB operations
NOTE:7648406.8 - Bug 7648406 - Child cursors not shared for "table_..." cursors (that show as "SQL Text Not Available") when NLS_LENGTH_SEMANTICS = CHAR
NOTE:8431767.8 - Bug 8431767 - High "library cache: mutex X" when using Application Context
NOTE:8499043.8 - Bug 8499043 - SET_CONTEXT incurs unnecessary DLM overhead in RAC
NOTE:8793492.8 - Bug 8793492 - Mutex Waits with Resource Manager
NOTE:8860198.8 - Bug 8860198 - "library cache:mutex X" waits using XMLType
NOTE:8981059.8 - Bug 8981059 - High Version Count (due to USER_BIND_PEEK_MISMATCH) with bind peeking
NOTE:9003145.8 - Bug 9003145 - Dump (kglIsOwnerVersionable) / "library cache: mutex X" waits
NOTE:9140262.8 - Bug 9140262 - ORA-600 [ksliwat5] followed by cpu spike/"library cache: mutex X" Waits
NOTE:9239863.8 - Bug 9239863 - Excessive "library cache:mutex X" contention on hot objects
NOTE:9282521.8 - Bug 9282521 - Excessive "library cache:mutex X" contention on hot objects
NOTE:9312879.8 - Bug 9312879 - "library cache: mutex x" waits after killing sessions / PMON slow to clean up
NOTE:9398685.8 - Bug 9398685 - High "library cache: mutex X" when using Application Context
NOTE:9530750.8 - Bug 9530750 - High waits for 'library cache: mutex X' for cursor Build lock
BUG:10411618 - ADD DIFFERENT WAIT SCHEMES FOR MUTEX WAITS





NOTE:7502237.8 - Bug 7502237 - Unnecessary "library cache: mutex X" waits using stored Java
NOTE:11719151.8 - Bug 11719151 - SQL Plan Management capture causes slowness
NOTE:11818335.8 - Bug 11818335 - Additional support for bug 10411618 to allow dynamic Mutex wait scheme changes
NOTE:12633340.8 - Bug 12633340 - Heavy "library cache lock" and "library cache: mutex X" contention for a "$BUILD$.xx" lock
NOTE:12670165.8 - Bug 12670165 - Library cache mutex contention caused by lookup of triggers
NOTE:12797420.8 - Bug 12797420 - "library cache: mutex X" waits on DB instance handle with CURSOR_SHARING
NOTE:12976376.8 - Bug 12976376 - High VERSION_COUNT for SQL with binds, including recursive dictionary SQL - superseded
NOTE:13588248.8 - Bug 13588248 - "library cache: mutex X" contention for objects with very many library cache locks
NOTE:10086843.8 - Bug 10086843 - Recursive SQL cursors not reused - PMON crashes instance with ORA-600 [kglLockOwnersListDelete]
NOTE:10145558.8 - Bug 10145558 - Selects on library cache V$/X$ views cause "library cache: mutex X" waits
NOTE:10204505.8 - Bug 10204505 - SGA autotune can cause row cache misses, library cache reloads and parsing
NOTE:7352775.8 - Bug 7352775 - Many child cursors when PARALLEL_INSTANCE_GROUP set wrong
NOTE:10284845.8 - Bug 10284845 - Need index on SID for X$KGLLK and X$KGLPN based views (V$OPEN_CURSOR etc..)
NOTE:10417716.8 - Bug 10417716 - Mutex X waits in 11G on an instance with heavy JAVA usage
NOTE:10632113.8 - Bug 10632113 - OLS calls cause mutex contention even with low number of concurrent users

  

相关内容

   

 

产品

   

• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Personal Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Standard Edition > Generic RDBMS > Database Level Performance Issues (not SQL Tuning)
• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)

 

关键字

   

BUGTAG_ADDBUGS

 

错误

   

KGLLOCKOWNERSLISTDELETE;KSLIWAT5;ORA-600 [KGLLOCKOWNERSLISTDELETE];ORA-600 [KSLIWAT5];ORA-600 [NNNNN];ORA-600 [POOL];ORA-600 [XXXX]