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

本文详细解释了Oracle数据库中'librarycache:mutex'等待事件的意义及其诊断性能问题的方法,适用于Oracle数据库11g版本。文章还提供了如何减少此类等待事件的建议,并列举了已知的bug及修复版本。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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:

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
在C++并发编程中,`std::mutex`和`std::shared_timed_mutex`是用于管理共享资源访问的两种互斥锁类型,它们各自适用于不同的场景,并具有不同的功能特性。 ### `std::mutex` `std::mutex`是C++11引入的最基本的互斥锁类型,它提供独占锁(exclusive lock)功能,意味着在任意时刻只能有一个线程能够持有该锁。当一个线程对`std::mutex`进行加锁操作后,其他试图加锁的线程将被阻塞,直到该锁被释放。这种锁适用于需要防止多个线程同时修改共享资源的场景。`std::mutex`通常与`std::lock_guard`或`std::unique_lock`一起使用,以实现RAII风格的锁管理,确保锁在作用域结束时自动释放[^3]。 示例代码: ```cpp #include <mutex> #include <thread> std::mutex mtx; void print_block(int n, char c) { mtx.lock(); for (int i = 0; i < n; ++i) { std::cout << c; } std::cout << '\n'; mtx.unlock(); } int main() { std::thread th1(print_block, 50, '*'); std::thread th2(print_block, 50, '$'); th1.join(); th2.join(); return 0; } ``` ### `std::shared_timed_mutex` `std::shared_timed_mutex`是C++14引入的一种更复杂的互斥锁,它支持两种类型的锁:共享锁(shared lock)和独占锁(exclusive lock)。共享锁允许多个线程同时读取共享资源,而独占锁则保证只有一个线程可以写入资源。这种锁特别适合读多写少的场景,因为它可以提高并发性能,允许多个读线程同时访问资源,而写线程则需要独占访问[^1]。 `std::shared_timed_mutex`还支持定时锁(timed lock)操作,例如`try_lock_for`和`try_lock_until`,允许线程在指定的时间段内尝试获取锁,而不是无限期等待。这在某些场景下可以避免死锁或提高程序的响应性。 示例代码: ```cpp #include <shared_mutex> #include <thread> #include <iostream> std::shared_timed_mutex shared_mtx; int data = 0; void reader(int id) { shared_mtx.lock_shared(); std::cout << "Reader " << id << " sees data = " << data << std::endl; shared_mtx.unlock_shared(); } void writer() { shared_mtx.lock(); data++; std::cout << "Writer updated data to " << data << std::endl; shared_mtx.unlock(); } int main() { std::thread w(writer); std::thread r1(reader, 1); std::thread r2(reader, 2); w.join(); r1.join(); r2.join(); return 0; } ``` ### 主要区别 1. **锁的类型**:`std::mutex`仅支持独占锁,而`std::shared_timed_mutex`支持共享锁和独占锁。 2. **并发性能**:由于`std::shared_timed_mutex`允许多个读线程同时访问资源,因此在读操作远多于写操作的场景中,它通常能提供更好的并发性能。 3. **定时操作**:`std::shared_timed_mutex`支持定时锁操作,而`std::mutex`不支持[^1]。 综上所述,选择`std::mutex`还是`std::shared_timed_mutex`取决于具体的应用场景。如果应用场景主要是写操作或需要简单的互斥机制,则`std::mutex`是合适的选择;而在读多写少的场景中,使用`std::shared_timed_mutex`可以显著提高并发性能。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值