原 Oracle之library cache系列等待事件分析方法总结（持续更新）

相关等待事件介绍

library cache lock和library cache: mutex X常常伴随出现，且会出现少量的cursor: mutex S和cursor: mutex X和cursor: pin S wait on X和cursor: pin S等待事件。

1、library cache lock

library cache lock是Oracle内存结构中的一种内部锁机制，用于保护库缓存（Library Cache）中的共享SQL和PL/SQL代码对象的并发访问。库缓存是Oracle数据库中用于存储已解析过的SQL语句和执行计划、PL/SQL程序单元以及其他可共享的数据库对象的地方。当多个会话试图访问或修改库缓存中的同一对象时，Oracle会使用library cache lock来确保数据的一致性和并发控制。例如，在执行SQL解析、执行计划生成、PL/SQL编译、以及执行计划共享等操作时，会涉及到library cache lock的获取和释放。

产生library cache lock的一些原因：

• 登录密码错误或密码为空尝试过多：对于正常的系统，由于密码的更改，可能存在某些被遗漏的客户端，不断重复尝试使用错误密码登录数据库，从而引起数据库内部长时间的”library cache lock”或”row cache lock”的等待，这种情况主要是由于从Oracle11g开始的密码延迟验证和密码区分大小写等新特性引起的。这种现象在Oracle 10.2和11.1中体现的等待事件为：”row cache lock”，而在Oracle 11.2中体现的等待事件为：”library cache lock”。可以通过审计功能进行查询，参考：https://www.xmmup.com/zaioraclezhongruhechaxunmimashurucuowudedengluyonghu.html、https://www.xmmup.com/oracleyonghumimaxilie.html 。如果遇到这一类问题，可以通过Event 28401关闭这个特性，从而消除此类影响，以下命令将修改设置在参数文件中：

  ALTER SYSTEM SET EVENT = '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE;

  1	ALTER SYSTEM SET EVENT = '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE;

• 核心热表统计信息变化：例如索引重建，分区表全局索引维护，任意DDL语句，任意DCL语句如grant语句，手动或自动收集统计信息，等等

• 过多的子游标，游标version count过高引起，单个 SQL 语句可以生成大量子游标。 在这种情况下，会在生成子游标的会话之间发生对相同资源（latches 或者 mutexes）的争用。

  确认方法：AWR / Statspack 报告; 查看 "SQL ordered by Version Count" 部分. 如果有SQL语句的version数超过了500，则可能引发这个问题。或者，也可以查询 V$SQLAREA 视图确认是否有version_count 大于500的SQL语句。查询 V$SQL_SHARED_CURSOR 视图检查SQL没有共享的原因。

  每次生成child cursor，需要在library cache object中装载新对象，就需要获取相关library cache object handle对象的x lock，latch层面还需要获取latch shared pool和latch library cache cache(在oracle 11g后latch library cache lock被library cache mutex代替)。如果不断产生大量子游标，则会导致在申请新cursor时出现library cache lock等待；当然一般子游标过多肯定也会伴随着latch shared pool和latch library cache或者library cache mutex x等待。

  例如，根据主键进行更新或查询的SQL语句，其执行计划肯定只有1条，所以，完全可以使用绑定执行计划来减少version count。

• 审计被启用：审计由于需要申请 library cache lock 可能会导致产生冲突。尤其是在RAC环境中，library cache lock 是跨所有实例对整个数据库进行的，影响更大。 如果不必要，考虑禁用审计。请参考：https://www.xmmup.com/oracle-12czhongdetongyishenji.html和https://www.xmmup.com/oraclezhongdeshenjiyijidengludengchuddlchufaqijilubiaoshenjideng.html

• RAC环境中的非共享SQL：RAC环境中的非共享SQL语句容易导致 Library cache lock 等待。 在单实例中，非共享SQL更容易发生 library cache 或者 shared pooll latch 的竞争，而在RAC环境中，竞争主要发生在 Library cache lock。 考虑修改SQL为绑定变量方式，或在会话级别配置cursor_sharing=force

• 大量使用行触发器：频繁的触发行触发器会导致比正常情况更多的 Library cache 活动,原因是需要检查是否正在读取发生修改的表。在触发器处理的过程中，可能会引用发生修改的表，即由触发器SQL修改的表。这会让数据库处于不一致的状态，导致ORA-4091的错误。为了检查这一点，每一次查找这些表都会获取 Library cache lock。是否发生取决于触发了多少行触发器，而不是定义了多少行触发器。 拥有一个触发 10000 次的触发器比拥有 100 个仅触发一次的触发器更有可能导致这个问题。

• shared pool对象被频繁的age in和age out：shared pool不足、ASMM动态管理带来的SGA抖动、较大内存的PL/SQL和cursor object存储在shared pool中，每次重新装载进来都需要进行空间整理，此时会导致相关对象被age out

• 对象被编译：编译会对该对象的library cache object handle持有library cache lock x模式和library cache pin x模式，此时如果还有并发的相关SQL涉及到存储过程，执行存储过程需要持有library cache lock null和library cache pin s，则会出现library cache pin等待，如果有并发的编译或者DDL则可能出现library cache lock等待。

• JDBC bug导致：在Oracle 11g 版本中可能出现由于JDBC bug导致sql绑定变量无法共享，过期游标过多的情况，此时如果发生大量并发业务，很有可能造成异常library cache lock等待事件，造成数据库突发性能问题。

• SQL解析问题，p3参数多对应于“SQL AREA BUILD”，有如下几种情况：

  ​ a、存储过程解析错误或某频繁SQL语句解析错误，主要发生在SQL AREA BUILD上，在12.2之前可以通过配置ALTER SYSTEM SET EVENTS '10035 trace name context forever, level 1';进行跟踪解析失败的SQL，从12.2开始对同一条SQL语句默认解析错误超过100（隐含参数_kks_parse_error_warning控制）的话就会在告警日志中显示，然后我们在告警日志中搜索"PARSE ERROR"、“parse errors” 就可以看到相关的SQL解析失败的语句，最后进行错误的SQL处理即可。 可以参考：https://xmmup.com/oraclejiexicuowudegenzongbanfa.html

  ​ b、共享的SQL语句过期：如果共享池太小，共享游标将从library cache中移除，下次使用时需要重新加载。重新加载时需要硬解析，这会导致CPU资源消耗和锁的竞争。解决方法：增加共享池大小或使用ASMM自动调整或将频繁使用的较大的PL/SQL对象或者游标保持在共享池中（Pin）。可以使用 DBMS_SHARED_POOL.KEEP() procedure 将较大的且经常使用的 PL/SQL 对象和 SQL 语句游标保持在共享池中，并防止它们过期。 可以消除重复重新加载相同对象的需求并减少共享池的碎片。

  ​ c、跨越多个会话进行对象编译：一个或者多个会话在编译对象（通常时PL/SQL）的同时，其他会话为了执行或者编译同一个对象，pin住了它，那么这些会话将会以共享模式（执行）或者独占模式（编译或者更改对象）下等待library cache pin。解决方案: 避免在数据库繁忙的时间段或者多个会话同时编译对象，避免同时从多个会话或者业务高峰期编译有依赖关系的对象。

  ​ d、发生大量的硬解析，并发的SQL硬解析则会出现library cache lock竞争(对象在table的library cache object handle的library cache lock) 。可能有如下几点原因：

  ​ ① 没有使用绑定变量，导致shared pool对象被频繁的age in和age out：类似的SQL语句，若只是条件的值不一样，即where条件使用的是常量（Literals），解决办法就是要么修改SQL为绑定变量方式要么在会话级别配置cursor_sharing=force

  ​ ② 由于shared pool不足导致SQL被挤出去。

  ​ ③ Library cache object invalidations失效：当对对象（如表或视图）进行DDL 或收集统计信息时，依赖于它们的游标将失效。这里的DDL包括truncate表、索引重建、monitoring和nomonitoring索引，grant、alter操作等等， 这将导致游标在下一次执行时被硬解析，并会影响 CPU 和发生锁竞争。如果存在并发的DDL操作和DML，而DDL一直未完成，此时DDL会持有该对象的library cache object handle的X Lock，DML会请求该对象的ibrary cache object handle的S lock模式，此时DML就会被hang住。

  此时，AWR 或者 statspack 报告:

  - Library Cache statistics 部分显示 reloads 数很高并且 (每小时几千次) invalidations （ Invali- dations）也很高。 - "% SQL with executions>1" 超过 60%, 表明SQL语句共享率较高。 - 确认 Dictionary Statistics 部分中的“Modification Requests”的值是否为0，这意味着一些对象上有DDL在执行。

  1 2 3

  - Library Cache statistics 部分显示 reloads 数很高并且  (每小时几千次) invalidations （ Invali- dations）也很高。 - "% SQL with executions>1" 超过 60%, 表明SQL语句共享率较高。 - 确认 Dictionary Statistics 部分中的“Modification Requests”的值是否为0，这意味着一些对象上有DDL在执行。

Library cache object 失效过多的解决方法：

1. 不要在数据库繁忙的时间段执行DDL或DCL：DDL 或DCL语句使库缓存对象失效，并会涉及到许多依赖对象，比如游标。 对象失效后需要同时进行多次硬解析，对库缓存、共享池、字典缓存、CPU使用产生很大影响。 这里的DDL包括truncate表、索引重建、monitoring和nomonitoring索引，grant操作等等都会引起游标失效。
2. 不要在数据库繁忙的时间段收集统计信息：收集统计信息（ANALYZE或者DBMS_STATS）会使库缓存对象失效，并会涉及到许多依赖对象，比如游标。 对象失效后需要同时进行多次硬解析，对库缓存、共享池、字典缓存、CPU使用产生很大影响。
3. 不要在数据库繁忙的时间段执行 TRUNCATE 操作： 参考：Truncate - Causes Invalidations in the LIBRARY CACHE (Doc ID 123214.1)

library cache lock的P3参数进行解析获取内部等待：

或从ASH的15分钟报告也可以查到等待事件的参数值：

此外，也可以通过Systemstate dump的报告获取到，“handle address=0x743bb3e98, lock address=0x743baf088, 100*mode+namespace=0x520002 ===========> namespace is 0x52 (dec:82), sql area build”

关于library cache lock相关等待可参考

'library cache lock' Waits: Causes and Solutions (Doc ID 1952395.1)

'library cache lock' 等待事件: 原因和解决方案 (Doc ID 2896611.1)

2、library cache: mutex X

库缓存（library cache ）是用来保存解析过的 cursor 相关的内存结构，在 library cache 中有许多内存结构需要 library cache: mutex X 的保护。

library cache: mutex X 表示会话在获取库缓存（Library Cache）中特定资源的互斥锁（Mutex）时遇到了等待。库缓存是Oracle数据库中存储已解析过的SQL语句、执行计划和PL/SQL单元等共享资源的地方，互斥锁用于确保并发访问这些共享资源时的一致性。当多个会话试图同时访问或修改库缓存中的相同对象（例如，SQL语句的执行计划或PL/SQL包体）时，只有一个会话能够获得互斥锁并执行操作，其他会话则会等待互斥锁的释放，这时就会出现 library cache: mutex X 的等待事件。

在以前的 Oracle 版本中，获取 library cache Mutex 与获取 library cache latches 的目的相似。在 10g 中，为 library cache 中的特定操作引入了 Mutex。从 11g 开始，Mutex 取代了 library cache latches。只要某个会话以独占模式持有 library cache mutex 并且其他会话需要等待释放 Mutex，就会出现此等待事件。

12c 以后该等待又被细分为如下：

​ ● library cache: mutex X – 用于保护 handle。

​ ● library cache: bucket mutex X – 用于保护 library cache 中的 hash buckets。

​ ● library cache: dependency mutex X – 用于保护依赖。

等待 library cache: mutex X 与之前版本的 latch:library cache 等待相同。library cache: mutex X 可以被很多因素引起，例如：（包括应用问题，执行计划不能共享导致的高版本的游标等），本质上都是某个进程持有 library cache: mutex X 太长时间，导致后续的进程必须等待该资源。如果在 library cache 的 latch 或者 mutex 上有等待，说明解析时有很大的压力，解析 SQL 的时间变长（由于 library cache 的 latch 或者 mutex 的等待）会使整个数据库的性能下降。

由于引起 library cache: mutex X 的原因多种多样，因此找到引起问题的根本原因很重要，才能使用正确的解决方案。

3个参数的值：
P1 = "idn" = 唯一的Mutex标识符
P2 = "value" = 持有Mutex的会话ID
P3 = "where" = 等待 Mutex 的代码中的位置（内部标识符）

系统范围等待：
在系统范围级别，有两个视图可用于帮助诊断此等待：

GV$MUTEX_SLEEP（对于非 RAC 为 V$MUTEX_SLEEPS）和 GV$MUTEX_SLEEP_HISTORY（对于非 RAC 为 V$MUTEX_SLEEP_HISTORY）

在实例启动后，这些视图在实例范围内跟踪 Mutex 的使用情况。由于这些视图显示了自启动以来的总数，在出现问题时，您可以获取短时间间隔内值的差异，因此这些数据是非常有意义的。了解这些信息最简单的方法是查看 AWR 或 statspack 报告的“Mutex Sleep Summary”部分。

产生library cache: mutex X的常见原因：

​ ● 大量的硬解析：过于频繁的硬解析，会导致该等待。

​ ● 高版本的游标：当发生 High version count 时，大量的子游标需要检索，从而会引起该等待。由于SQL的子游标过多引起SQL解析时遍历library cache object handle链表需要很长时间，造成了library cache: mutex x等待。

​ ● 游标失效：游标失效是指，保存在 library cache 中的游标由于某些改变导致不可用，而从 library cache 中删除。例如：游标相关对象的统计信息收集；游标关联表，视图等对象的修改等。发生游标失效会导致接下来的进程需要重新载入该游标。当游标失效过多时，会导致 'library cache: mutex X' 等待。在11g里，由于bug 16400122，若某个SQL执行很频繁，且存在SQL基线，则会导致每6.5天高频率SQL游标被invalidate的问题。

​ ● 游标重载：游标重新载入是指本来已经存在于 library cache 中，但是当再次查找时已经被移出 library cache（例如：由于内存压力），这时就需要重新解析并且载入该游标。游标重新载入操作不是一件好事，它表明您正在做一件本来不需要做的事情，如果您设置的 library cache 大小适当，是可以避免游标重新载入的。游标重新载入的时候是不可以被进程使用的，这种情况会导致 library cache: mutex X 等待。

​ ● cursor_sharing=similar和session_cached_cursors配置不当。对于 11G，确认 cursor_sharing 不是 similar，因为该值已经不建议使用，并且会引起 mutex X 等待。可以参考：Document 1169017.1 ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting

​ ● 如果数据库从 10G 升级到 11G 后，遇到 mutex 的问题，请考虑升级到 11.2.0.2.2 以上的 PSU 来修复未发布的 Bug12431716，很多关于 mutex 的修复已经包含在该 Bug 中。诊断 11G 之后的 library cache: mutex X 问题诊断，参照如下文档：Document 2051456.1 Troubleshooting Databases Hang Due to Heavy Contention for 'library cache: mutex X' Waits (Oracle 11.2 and Later)

​ ● shared pool配置过小

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

验证码：

请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。