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关闭这个特性，从而消除此类影响，以下命令将修改设置在参数文件中：

• 1ALTER 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上，可以通过配置ALTER SYSTEM SET EVENTS '10035 trace name context forever, level 1';
进行跟踪解析失败的SQL，然后在告警日志中搜索"PARSE ERROR" 就可以看到相关的SQL解析失败的语句，最后进行错误的SQL处理即可。

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 报告:

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

复制

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

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

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

 1-- 对于library cache lock等待事件，这里的p3值一般是5373954，16进制为00520002，前面的4位0052代表namespace,后面的4位0002代表mode，而0052的10进制为82，82对应的NAMESPACE为SQL AREA BUILD
 2 select  event,p3 , to_char(p3,'xxxxxxxxxxxx') p3_16,count(*) from lhr.ash2
 3  where event in ('library cache lock')
 4   group by event,p3 
 5   order by 4 desc;  
 6
 7
 8select to_char(5373954,'xxxxxxxxxxxx') p3_hex, to_number('0052','xxxx')from dual; 
 9
10
11
12SELECT distinct KGLHDNSP,KGLHDNSD FROM X$KGLOB D WHERE KGLHDNSD like '%SQL AREA%'   ORDER BY KGLHDNSP; 
13select distinct indx,kglstdsc from x$kglst d where  kglstdsc like '%SQL AREA%'   ORDER BY indx;
14
15 0    SQL AREA
1675    SQL AREA STATS
1782    SQL AREA BUILD    --  通常是由于大量解析导致，或SQL AREA上的问题，如SQL解析失败

复制

或从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配置过小

● 某些已知的 Bug，例如：

○ “SELECT ANY TABLE”导致的，Bug 32356628 - Significant increase in library cache: mutex x wait time after upgrading database to 19c (Doc ID 32356628.8)

○ Bug 32219835 - Performance Degraded with Library Cache: Mutex x with Database Vault enabled 19c (Doc ID 32219835.8)

○ Bug 8431767 - High "library cache: mutex X" when using Application Context (Doc ID 8431767.8)

○ Bug 16400122 - Spikes in library cache mutex contention for SQL using SQL Plan Baseline (Doc ID 16400122.8)

3、enq: TX - row lock contention

enq: TX - row lock contention表明在并发事务处理过程中，不同会话因为试图修改（INSERT、UPDATE或DELETE）同一行数据而产生了行级锁争用。在Oracle中，事务（Transaction，TX）为了保证数据一致性，会在修改数据时对相应的行施加行级锁（Row Lock）。当一个会话已经获得了某一行的排他锁（Exclusive Lock），而其他会话也需要对该行进行修改操作时，它们将会进入等待状态，直到持有该行锁的会话提交（COMMIT）或回滚（ROLLBACK）事务，释放该行锁。

产生enq: TX - row lock contention的常见原因：

● 真正的业务逻辑上的行锁冲突，如一条记录被多个人同时修改。这种锁对应的请求模式是6。

● 唯一键冲突，如主键字段相同的多条记录同时插入。这种锁对应的请求模式是4。这也是应用逻辑问题。

● BITMAP索引的更新冲突，就是多个会话同时更新BITMAP索引的同一个数据块。此时会话请求锁的对应的请求模式是4。

行锁冲突案例可以参考：https://www.xmmup.com/oracledengdaishijianduiliedengdaizhitx-row-lock-contention.html

4、cursor: mutex S和cursor: mutex X

cursor: mutex S 和 cursor: mutex X 是Oracle数据库中两种不同类型的等待事件，它们都与游标相关的互斥锁（mutex）有关，主要区别在于锁的粒度和目的：

cursor: mutex S (Shared Mutex):

•目的：cursor: mutex S 等待事件发生在会话试图以共享模式获取游标相关的互斥锁时。这种模式的锁主要用于读取操作，允许多个会话同时以只读方式访问相同的游标资源，而不改变其状态。

•粒度：通常与保护游标状态或元数据（如游标统计信息）的共享访问有关，确保在不修改游标的情况下，多个会话可以并发地执行相同的SQL语句。

•并发性：较高，因为多个会话可以同时持有共享锁，只要没有会话要求排他锁进行写入操作。

cursor: mutex X (Exclusive Mutex):

cursor: mutex X 表示会话在获取游标相关的互斥锁（Mutex）时遇到了等待。互斥锁是用来保护共享资源，确保在同一时间只有一个会话可以访问或修改资源。

在并发环境下，多个会话可能同时尝试访问或修改同一个游标对象，为了避免数据不一致性和并发问题，Oracle使用互斥锁来控制对游标的访问。当多个会话需要获取同一流程或游标的互斥锁时，除第一个成功获取锁的会话外，其他会话会陷入等待状态，表现为等待事件 cursor: mutex X。

•目的：cursor: mutex X 等待事件发生在会话请求对游标资源的排他访问权时。这种模式的锁用于修改游标的状态或执行涉及游标内容的更新操作，如硬解析、执行计划变更、游标重用策略调整等。

•粒度：通常与需要独占控制的游标内部结构或执行计划相关，确保在修改期间不会与其他会话的读取或写入操作冲突。

•并发性：较低，因为一旦一个会话持有排他锁，其他所有会话（无论是请求共享还是排他锁）必须等待该锁被释放才能继续操作。

总结差异：

•访问模式：cursor: mutex S 表示共享访问，适用于读取操作，允许多个会话并发访问；cursor: mutex X 表示排他访问，适用于写入或修改操作，同一时刻仅允许一个会话持有。

•并发影响：cursor: mutex S 有助于提高并发性，因为它允许并发读取；而 cursor: mutex X 可能导致阻塞，因为它阻止了其他会话在同一时间内访问相同游标资源。

•应用场景：cursor: mutex S 通常与游标共享、执行计划共享等读取操作相关；cursor: mutex X 与硬解析、执行计划变更、游标状态更新等写入或修改操作相关。

在数据库性能调优中，高频率的 cursor: mutex S 或 cursor: mutex X 等待事件可能表明存在游标管理或SQL执行计划相关的问题，如过度的硬解析、游标版本过多、并发争用严重等。解决这些问题通常需要分析具体的应用逻辑、SQL语句、会话行为以及数据库配置，采取诸如减少硬解析、使用绑定变量、优化SQL、调整共享池参数等措施来降低锁竞争和改善系统性能。

产生cursor: mutex X的常见原因：

•并发会话在解析、执行或关闭相同SQL语句的游标时。

•应用程序中存在大量的并发打开和关闭游标操作。

•PL/SQL块内部对游标进行了并发访问。

5、cursor: pin S wait on X和cursor: pin S

cursor: pin S wait on X

cursor: pin S wait on X会话等待此事件是在它尝试获取共享模式的 Mutex 锁时，其他会话在相同游标对象上以独占方式持有 Mutex 锁。通常，等待“Cursor: pin S wait on X”是症状而非原因。其中可能需要进行底层的优化或者是已知问题。

游标等待与某种形式的解析相关联。 当会话尝试在共享模式下获取 mutex pin 资源，但另一个会话在同一个游标对象上以独占方式持有该 mutex pin 资源时，就可能会发生此等待事件。通常，等待“cursor: pin S wait on X”等待是一种症状，而不是原因。 可能存在潜在的调优要求或是遭遇了已知问题。

常见原因：

• 首先，要保证 shared pool 的大小设置正确。

  一般来说，如果 shared pool 大小不足或者承受负载的能力不足，就可能表现为“cursor: pin S wait on X”等待。如果使用了自动内存管理模式，那么这通常不是问题，详见：

  Document 443746.1 Automatic Memory Management (AMM) on 11g

• 频繁硬解析
  如果硬解析的频率很高的话，在 mutex pin 上就会发生竞争。

• 子游标版本数过高
  当子游标版本数过高时，需要检查一长串版本，这可能会导致对该事件的争用。

• 已知的 BUG

• 解析错误，详见以下文档：

  Document 1353015.1 How to Identify Hard Parse Failures

How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' (Doc ID 786507.1)

cursor: pin S

会话在申请共享模式下特定游标上的特定 Mutex 时，虽然没有并行的排他持有者，但无法立即获取 Mutex，这时会等待“cursor: pin S”。这看上去有些不合理，因为用户可能会不理解为什么在没有排他模式持有者的情况下会存在这种等待。出现这种等待的原因是，为了在共享模式下获取 Mutex（或释放Mutex），会话必须增加（或减少）Mutex 引用计数，这需要对 Mutex 结构本身进行独占原子更新。如果有并行会话在尝试对 Mutex 进行这样的更新，则一次只有一个会话能够实际增加（或减少）引用计数。如果由于其他并行请求使得某个会话无法立即进行这种原子更新，则会出现等待“cursor: pin S”。
在 RAC 环境中，Mutex 只作用于本地实例。

参数：
 P1 = idn
 P2 = value
 P3 = where（10.2 中为 where|sleeps）

idn 是 Mutex 标识符值，与正在等待以获取 Mutex 的 SQL语句的 HASH_VALUE 相匹配。可以用以下格式的查询找到使用对应 IDN 的 SQL 语句：

1SELECT sql_id, sql_text, version_count
2FROM V$SQLAREA where HASH_VALUE=&IDN;

复制

如果游标显示的 SQL_TEXT 格式为“table_x_x_x_x”，则这是特殊的内部游标，有关将此类游标映射到对象的详细信息，请参阅 Document 1298471.1。

P1RAW 是采用十六进制值的相同值，可用于在跟踪文件中搜索与该 hash（散列）值匹配的 SQL。

• value

  Mutex value (includes details of holder)

This is the mutex value. The value is made up of 2 parts:

• High order bits contain the session id of the session holding the mutex
  (which should be 0 for a "cursor: pin S" wait as if there was an X holder the current session should be waiting on "cursor: pin S wait on X" instead)

• Low order bits contain a reference count
  (ie: the number of other S mode holders)

• where

  Where in the code the mutex is requested from

The high order bits of P3 give a number which represents a location in the Oracle code where the mutex was requested from. In 10.2 the low order bits of P3 gives a sleep value. In 11g the low order bits are all 0.

Warning: In 10.2 the low order sleep value can overflow into the high order bits, especially on 32bit platforms, giving a bad location value

The high order bits of P3 can be mapped to a location name thus:

 1 SELECT decode(trunc(&&P3/4294967296),
 2        0,trunc(&&P3/65536),
 3          trunc(&&P3/4294967296)) LOCATION_ID
 4 FROM dual;
 5
 6Use the LOCATION_ID returned above in this SQL:
 7
 8 SELECT MUTEX_TYPE, LOCATION 
 9   FROM x$mutex_sleep
10  WHERE mutex_type like 'Cursor Pin%' 
11    and location_id=&&LOCATION_ID; 

复制

 

The location names can be quite cryptic. In some cases the location may he helpful when diagnosing the cause of unexpected "cursor: pin S" waits.

可能原因 (B)

I. 某个特定 Mutex 有大量并行操作，特别是在多个 CPU 的系统上。

II. 在高负载情况下，会等待非常多不同的“idn”值。

III. Oracle Bugs
  Bug 10270888 - ORA-600[kgxEndExamine-Bad-State] mutex waits after a self deadlock
  Bug 9591812 - Wrong wait events in 11.2 ("cursor: mutex S" instead of "cursor: mutex X")
  Bug 9499302 - Improve concurrent mutex request handling
  Bug 7441165 - Prevent preemption while holding a mutex (fix only works on Solaris)
  Bug 8575528 - Missing entries in V$MUTEX_SLEEP.location

解决方案 (B)

I. 应用 Bug 10411618 的修复。

II. 对于任何已标识的“热点”SQL，用户可以通过将 SQL 替换为一些由其他会话执行的变体，来减少特定游标上的并行操作。有关详细信息，请查看 WAITEVENT: cursor: pin S Reference (Document 1310764.1)。

III. 应用其他已知 Oracle bug 的修复。获取修复的最有效方法是应用最新 PSU patch（补丁程序）。 Document 756671.1 提供了有关推荐补丁程序的详细信息。

游标不能共享（version count过高）的一些原因

执行计划不能共享导致的高版本的游标。 原因和排查方法参考：

• https://xmmup.com/shenmeshigaobanbenyoubiaohigh-version-countruhepaicha.html

• https://www.xmmup.com/mosguzhangpaichu-banbenshugaohigh-version-countdewenti-doc-id-28969231-sql-banbenshuguoa.html

例如：

1、由于SQL中绑定变量长度或类型不一致导致。  BIND_MISMATCH、BIND_LENGTH_UPGRADEABLE
2、SQL绑定变量输入null值触发BUG  8198150 - High Versioncount with bind_mismatch with passing null value to bind (文档 ID 8198150.8)，导致会产生大量子游标，引发library cache lock等待。

• BIND_MISMATCH：①  绑定元数据与现有的子游标不匹配，如长度（传入变量varchar2长度跨度很大，同一个变量值，传入的长度出现分别为32,128,2000,4000,8192）、类型（如传入TIMESTAMP，但列类型为DATE）等；②  SQL绑定变量输入null值触发BUG  8198150  参考：https://www.xmmup.com/moshigh-version-count-due-to-bind_mismatch-doc-id-3362681-10503-events.html

• BIND_EQUIV_FAILURE  ：① 由于ACS自适应游标的bug导致    ②  表字段为VARCHAR2，但是输入值为NVARCHAR2   ③设置alter session set statistics_level=all;
  导致出现子光标不能共享，在高版本中已经归类到OPTIMIZER_MISMATCH中 了。 ④ 由于bug 28794230导致，**12.2 由于 Bind_equiv_failure 引发 SQL 不能共享进而造成 Cursor Mutex: x (Doc ID 2610645.1) ** 、12.2 Cursor Mutex X Due To Sql Not Shared Because Of BIND_EQUIV_FAILURE  ⑤  由于SQL's Are Not Getting Shared due to BIND_EQUIV_FAILURE in 12.2 (Doc ID 2635456.1)引起的，解决办法：alter system set "_fix_control"='17443547:OFF';
    参考：https://www.xmmup.com/youyubind_equiv_failuredaozhideyoubiaobunenggongxiangwenti.html

• ROLL_INVALID_MISMATCH：主要为统计信息 参数no_invalidate 未设置成 false（立即失效当前游标）. 默认是dbms_stats.auto_invalidate（select DBMS_STATS.GET_PARAM('NO_INVALIDATE') from dual;）。即ORACLE内部缓慢的过期游标。

查看AWR中的Mutex Sleep信息发现：Mutex主要有三个函数的sleep是非常高的，kgllkal3 82、kkshGetNextChild[KKSHBKLOC1]、kglUpgradeLock 119。
函数-kgllkal3 82:kglkal的意思就是kernel generic library cache management library cache lock allocate 82的意思就是SQL AREA BUILD的意思。
函数-kkshGetNextChild [KKSHBKLOC1]:kksh的意思是kernel compile shared objects (cursor) cursor hash table，就是shared cursor的hash链表。持有mutex从library cache 的handle的hash链表上找出可共享的游标。

kksfbc K[Kernel]K[Kompile]S[Shared]F[Find]B[Best]C[Child]  该函数用以在软解析时找寻合适的子游标

Kghfrunp: KGH: Ask client to freeunpinned space

Kghdmp : x$ksmsp
is a fixed table based onkgh metadata. The number of latch sleeps for "kghdmp" will increaseif x$ksmsp
if an installation selectsfrom this fixed table too frequently.

kghupr1 : un-pin recreatable

kghalo       KGH: main allocation entry point

kghgex       KGH: Get a new extent

kghalf       KGH: Non-recoverably allocate afreeable chunk of memory

SQL 版本数过高 – 原因判断脚本 (Doc ID 1985045.1)

High SQL Version Counts - Script to determine reason(s) (Doc ID 438755.1)

Troubleshooting: High Version Count Issues (Doc ID 296377.1)

故障排除: 版本数高(High Version Count)的问题 (Doc ID 2896923.1)

游标解析失败

怎么找到解析失败的 SQL？

在解析SQL的过程中，若碰到SQL语法错误、访问的对象不存在或没有权限，则会导致目标SQL语句解析失败，所以，解析失败的SQL语句是不会生成执行计划的。可以通过如下几种方式找到解析失败的SQL：

1. 通过关联 xkglcursorxkglcursor_child_sqlid 这两个视图是可以找到解析失败的 SQL

2. 通过使用 Oracle 10035 event 事件也是可以找到解析失败的SQL

3. 通过 oracle systemdump 也可以找到解析失败 SQL

Oracle提供了一系列的跟踪事件来帮助定位各种问题，通过10035事件可以诊断解析失败的情况，如下：

1[oracle@rhel6lhr ~]$ oerr ora 10035
2
310035, 00000, "Write parse failures to alert log file"

复制

首先通过“alter system set events '10035 trace name context forever,leve 5';”开启监控，然后使用tail观察告警日志，找到问题SQL之后，使用命令“alter system set events '10035 trace name context off';”关闭10035事件。

游标失效（INVALIDATE）介绍

有些命令会将cursor的状态变成成INVALIDATE。这些命令直接修改cursor相关对象的上下文环境。游标失效后，相对应的cursor会留在SQLAREA中，但是下次被引用时会被完全reload并重新parse，所以会对数据库的整体性能造成影响。

下面的查询可以帮我们找到Invalidation较多的cursor:

1SELECT SUBSTR(sql_text, 1, 40) "SQL",
2invalidations
3FROM v$sqlarea
4ORDER BY invalidations DESC;

复制

常见的可以使游标失效的场景：表的DDL操作，truncate、索引重建、对索引进行monitoring和nomonitoring操作、grant授权核心热表，alter操作，表或索引上的ANALYZE或 DBMS_STATS.GATHER_XXX，关联对象的权限变更

另外，在11g里，由于bug 16400122，若某个SQL执行很频繁，且存在SQL基线，则会导致每6.5天高频率SQL游标被invalidate的问题。

对索引进行monitoring和nomonitoring操作导致游标失效实验：

 1CREATE TABLE lhr_table_temp (a VARCHAR2(4000));
 2INSERT INTO lhr_table_temp (a)  VALUES ('test data session 1'); 
 3commit;
 4
 5create index idx1 on lhr_table_temp(a);
 6
 7select * from lhr_table_temp;
 8select sql_text,sql_id,version_count,loads,invalidations,parse_calls from v$sqlarea a where sql_text  like '%select * from lhr_table_temp%';
 9select a.executions,a.loads,a.sql_text,a.first_load_time,a.last_load_time from v$sql a where sql_text like '%select * from lhr_table_temp%';
10
11-- truncate table lhr_table_temp;
12
13alter index idx1 monitoring USAGE;
14alter index idx1 nomonitoring USAGE;
15
16
17SELECT * FROM V$OBJECT_USAGE;

复制

1. loads为3表示加载了3次，即硬解析了3次，分别为建表后第1次查询，做monitoring后的查询，做nomonitoring后的查询

2. invalidations为3表示游标失效了2次，因为做monitoring失效1次，做nomonitoring失效1次

3. parse_calls可以认为每次硬解析后的执行次数

其它分析

• 告警日志分析，PGA是否内存耗尽

• HANG ANALYSIS的日志

• 在MOS中搜索对应的bug

• 分析DBA_HIST_MUTEX_SLEEP视图

• AWR报告中是否有INSERT INTO sys.aud$
  占用很高

AWR报告分析

搜索如下几个部分的内容进行分析：

• Top 10 Foreground Events by Total Wait Time

• Top SQL with Top Events (Active Session History (ASH) Report)

• Library Cache Activity

• SQL ordered by Version Count

• SQL AREA

• SQL AREA BUILD

• Mutex Sleep Summary

• parse time elapsed

• parse count (failures)

• Memory Dynamic Components

• Instance Activity Stats

Version Count 、游标失效、游标Reloads示例

library cache: mutex X示例

1. 正常情况下，我们可以从 AWR 中看到 library cache: mutex X 是 TOP 事件：

1. 定位出硬解析和高版本的 SQL，点击“Main Report”下的“SQL Statistics”链接

之后点击“SQL ordered by Parse Calls”和“SQL ordered by Version Count”

定位解析比较高的 SQL：

注意比较高的解析比例的 SQL，理想情况下解析和执行的比例应该很低，如果该比例很高说明应用中没有很好的使用游标，游标解析并且打开之后应该保持打开状态，与开发人员确认如何保持游标打开，避免下次执行该 SQL 时重复解析。

检查是否存在较高的硬解析，因为硬解析会引起 SQL AREA 的重新装载，通过 load profile 确定硬解析的数量。

该信息表明每秒会有26.3次的硬解析，大于经验值20，这表明硬解析很高。需要检查应用是否正确使用了绑定变量。

对于 SQL AREA 的重新加载也要进行检查：

如果在 SQL AREA 上的重新加载次数很高，那么需要检查游标是否被有效共享（重新加载的次数是指被缓存在 shared pool 中，但是使用时已经不在 shared pool 中）。

如果游标已经有效共享，那么需要确认 shared pool 和 sga_target 是否足够大，如果 shared pool 有压力而没有足够的空间，那么有些缓存的游标会被从 shared pool 中清除。游标共享充分，但由于 shared pool 空间过小也会引起可重用的游标被清除从而引发硬解析。

如果游标共享不充分，shared pool 会被这些不能被重用的游标占满，从而把那些可以重用的游标挤出 shared pool，进而引起在这些 SQL 重新执行时需要重新加载。不过最常见的情况还是游标无法共享。

下一步检查 SQL 高版本：

通过如上的列表中找到 SQL 版本较高的 SQL，可以通过 V

1不能识别此Latex公式:
2SQL_SHARED_CURSOR 检查引起 SQL 高版本的原因。


3
Document 438755.1 Formated V


复制

SQL AREA BUILD

SQL AREA BUILD通常是由于大量解析导致。

Systemstate dump示例SQL AREA BUILD

100*mode+namespace=0x520002 ===========> namespace is 0x52 (dec:82), sql area build

Systemstate dump shows that the library cache contention happened on SQL AREA BUILD namespace which is relevant to parsing:

 1PROCESS 154:
 2----------------------------------------
 3SO: 0x734bab158, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
 4proc=0x734bab158, name=process, file=ksu.h LINE:12616, pg=0
 5(process) Oracle pid:154, ser:6, calls cur/top: 0x6bf2ee508/0x6bf2ee508
 6flags : (0x0) -
 7<...snipped...>
 8O/S info: user: xxxx, term: UNKNOWN, ospid: 1636
 9OSD pid info: Unix process pid: 1636, image: oracle@xxxx
10Short stack dump:
11ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+1865<-kslwaitctx()+163<-ksfwaitctx()+14<-kgxWait()+700<-kgxSharedExamine()+425<-kxsGetRuntimeLock()+240<-kkscsCheckCursor()+556<-kkscsSearchChildList()+1262<-kksfbc()+12105<-kkspsc0()+1173<-kksParseCursor()+116<-opiosq0()+1588<-kpooprx()+274<-kpoal8()+829<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<-__libc_start_main()+253<-_start()+36
12
13----------------------------------------
14SO: 0x729475520, type: 4, owner: 0x734bab158, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
15proc=0x734bab158, name=session, file=ksu.h LINE:12624, pg=0
16(session) sid: 4220 ser: 25 trans: (nil), creator: 0x734bab158
17flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
18flags2: (0x40009) -/-/INC
19DID: , short-term DID:
20txn branch: (nil)
21oct: 47, prv: 0, sql: 0x74f6bc308, psql: 0x7476122d8, user: 76/xxxxx
22ksuxds FALSE at location: 0
23service name: xxxxxx
24client details:
25O/S info: user: xxxxxx, term: xxxxxx, ospid: 6152:13936
26machine: xxxx\xxxxprogram: xxxx.exe
27application name: xxxx.exe, hash value=1240902255
28Current Wait Stack:
290: waiting for 'library cache lock'
30handle address=0x743bb3e98, lock address=0x743baf088, 100*mode+namespace=0x520002 ===========> namespace is 0x52 (dec:82), sql area build 
31wait_id=9268 seq_num=9913 snap_id=1
32wait times: snap=0.000729 sec, exc=0.000729 sec, total=0.000729 sec
33wait times: max=infinite, heur=0.000729 sec
34wait counts: calls=1 os=1
35in_wait=1 iflags=0x5a2
36

复制

CURSOR_SHARING=SIMILAR参数说明

通过设置 CURSOR_SHARING 参数，将SQL语句中的常量（Literals）部分自动替换为绑定变量。可以为参数设置以下值：

• EXACT: 常量（Literals）部分按原来的方式执行。（默认值）

• FORCE: 将所有定量（Literals）部分替换为绑定变量。（尽可能）

• SIMILAR: 仅当执行计划无法更改时，才用绑定变量替换常量（Literals）部分。（例如：安全的常量替换）

通常,经常使用等价条件的 OLTP 应用程序的执行计划变化不大，但这些参数的效果应该在应用程序中测试。
此参数可以在会话级别设置，建议使用以最大程度地减少影响。

SIMILAR 和 FORCE 的区别在于 SIMILAR 会强制的共享相似的SQL语句，而不会使用不合适的执行计划。而CURSOR_SHARING=FORCE 会导致选择不适合的执行计划。

当 CURSOR_SHARING 为 SIMILAR 的时候，如果有不同的常量值（Literals）替换为绑定变量之后导致执行计划产生波动的情况，只有在被替换为绑定变量之前的常量值为相同值时，子游标才被共享。这可以防止使用不适合的执行计划。例如，如果指定了使用“>”的范围条件，那么指定的范围会根据指定的条件值而改变，并影响成本和执行计划，因此如果指定了不同的值，则会生成一个新的子游标。如果是一个相同的谓词，将会始终共享子游标。

可以考虑在应用程序中更改SQL语句以使用绑定变量（比如Hint,SQL Profile或者Outline），避免使用CURSOR_SHARING=SIMILAR。或者设置CURSOR_SHARING=FORCE,不过有导致使用不合适的执行计划的风险，但是不会增加子游标。

等待事件解决方案

1、绑定执行计划
对于version count高的SQL语句，可以根据情况考虑使用SPM或SQL Profile来绑定执行计划。该操作不需要重启数据库。例如，根据主键进行更新或查询的SQL语句，其执行计划肯定只有1条，所以，完全可以使用绑定执行计划来减少version count

2、配置10503 event，可有效减少version count的数量
根据High Version Count Due To BIND_MISMATCH (Doc ID 336268.1)文档，可以通过event 10503调大会话级别的字符串bind buffer，以确保会话生成的游标buffer长度一致，建议将bind buffer调为2000(接近sql绑定变量的max_length)，可以如下配置：

1ALTER system SET EVENTS '10503 trace name context forever, level 2000';

复制

注意：

1、若是2个节点，则必须同时配置生效，否则可能会触发 ORA-600: [kxspoac : EXL 1] Occuring During Query If Event 10503 Is Set (Doc ID 810194.1)

2、该事件建议在session级别配置，否则可能会导致内存空间不足等问题，若在system级别配置，相当于所有的绑定变量都用2000字节的buffer，这会导致内存分配问题，另外，用完及时关闭。

1ALTER session SET EVENTS '10503 trace name context forever, level 4000';
2ALTER session SET EVENTS '10503 trace name context  off';

复制

 

3、根据 Bug 10274265 - Event 10503 does not work at session level (Doc ID 10274265.8)，若数据库大于Versions >= 10.2 且 BELOW 12.1，则在session级别配置该事件并不起作用。

3、根据 High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance (Doc ID 2431353.1) 的建议，修改隐含参数"_cursor_obsolete_threshold"为1024，并重启数据库

1alter system set "_cursor_obsolete_threshold"=1024 scope=spfile sid='*';

复制

4、统计信息的的收集策略需要修改
根据业务规则，考虑将热点表的统计信息收集规则做变更，例如绑定统计信息，只收集增量统计信息等，需要和业务方讨论决定。

5、增加sga大小和修改Shared pool大小
根据分析，系统剩余内存较大，存在浪费，建议增大sga_target的值，提升系统性能，减少业务等待。

6、升级数据库到最新的RU

相关SQL

  1-- DASH分析
  2
  3enq: TX - row lock contention
  4library cache lock
  5library cache: mutex X
  6cursor: mutex S
  7cursor: mutex X
  8
  9
 10SELECT A.EVENT, COUNT(*) FROM LHR.ASH2 A GROUP BY A.EVENT ORDER BY 2 DESC;
 11
 12
 13SELECT A.EVENT,B.EVENT BLOCKING_EVENT,COUNT(*)
 14  FROM LHR.ASH2 A, LHR.ASH2 B
 15 WHERE 1=1    -- A.EVENT = 'cursor: mutex X'
 16   AND A.BLOCKING_SESSION = B.SESSION_ID
 17   AND A.BLOCKING_SESSION_SERIAL# =B.SESSION_SERIAL#
 18     AND  A.BLOCKING_INST_ID=B.INSTANCE_NUMBER
 19 GROUP BY A.EVENT, B.EVENT
 20 ORDER BY 3 DESC;        
 21
 22
 23 select substr(sample_time, 12, 5), event, count(*)   cnt
 24  from  dba_hist_active_sess_history
 25   where  sample_time between
 26          to_date('2024-04-07  16:47', 'yyyy-mm-dd hh24:mi') and
 27          to_date('2024-04-07  17:00', 'yyyy-mm-dd hh24:mi')
 28 -- and A.SQL_ID = 'cn9fqrd5w0841'    
 29 and event is not null
 30 group by event, substr(sample_time, 12, 5)
 31 having count(*) > 10
 32 order by 1;
 33
 34
 35-- sql_plan_hash_value 分析
 36SELECT A.Sql_Id,  a.sql_plan_hash_value, COUNT(*)
 37  FROM LHR.ASH2 A
 38 where a.sql_id = 'cn9fqrd5w0841'
 39 GROUP BY A.Sql_Id, a.sql_plan_hash_value
 40 ORDER BY 3 DESC;
 41
 42SELECT A.Sql_Id, a.sql_child_number, a.sql_plan_hash_value, COUNT(*)
 43  FROM LHR.ASH2 A
 44 where a.sql_id = 'cn9fqrd5w0841'
 45 GROUP BY A.Sql_Id, a.sql_child_number, a.sql_plan_hash_value
 46 ORDER BY 4 DESC;
 47
 48
 49
 50-- version count  高版本游标
 51select a.version_count,a.loaded_versions,a.executions from v$sqlarea a where a.sql_id='XXXXX';
 52
 53select d.version_count,d.loaded_versions from dba_hist_sqlstat d where d.sql_id='XXXX' and d.snap_id in (36) order by  version_count desc;
 54
 55
 56-- 解析失败的SQL                       
 57select * from v$sysstat a where  name like '%parse%' and a.value>100 order by value desc ;
 58select * from dba_hist_sysstat ;
 59
 60
 61-- P3对应的值
 62select  event,p3 , to_char(p3,'xxxxxxxxxxxx') p3_16,count(*) from lhr.ash2
 63  where event in ('library cache lock','library cache: mutex X')
 64     group by event,p3 
 65     order by 4 desc;
 66
 67 select to_number('0052','xxxx'),to_number('0053','xxxx') from dual; 
 68
 69
 70
 71
 72
 73SELECT distinct KGLHDNSP,KGLHDNSD FROM X$KGLOB D WHERE KGLHDNSD like '%SQL AREA%'   ORDER BY KGLHDNSP; 
 74 0    SQL AREA
 7575    SQL AREA STATS
 7682    SQL AREA BUILD --  通常是由于大量解析导致，如SQL解析失败
 77
 78
 79-- 硬解析
 80select INSTANCE_NUMBER,TOP_LEVEL_SQL_ID,SQL_ID,count(*) 
 81from dba_hist_active_sess_history where IN_HARD_PARSE='Y' 
 82group by INSTANCE_NUMBER,TOP_LEVEL_SQL_ID,SQL_ID 
 83having count(*)>100 order by count(*) desc;
 84
 85
 86-- 重载时间分析
 87select a.first_load_time,a.last_load_time from v$sql a where  sql_id='';
 88
 89
 90 -- SQL 占用内存
 91 SELECT SUM (sharable_mem) / 1024 / 1024 || 'M'
 92  FROM v$sqlarea
 93 WHERE sql_id = 'cpqsn8zak6sw4';
 94
 95
 96
 97
 98
 99-- 查询PGA消耗
100
101select a.time, a.inst_id, a.pgatotaluse, b.inst_id, b.pgatotaluse
102  from (select b.snap_id,
103               to_char(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') time,
104               a.instance_number inst_id,
105               round(a.value / 1024 / 1024) pgatotaluse
106          from dba_hist_pgastat a, dba_hist_snapshot b
107         where a.snap_id = b.snap_id
108           and a.name = 'total PGA allocated'
109           and a.instance_number = 1
110           and b.instance_number = 1) a,
111       (select b.snap_id,
112               to_char(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') time,
113               a.instance_number inst_id,
114               round(a.value / 1024 / 1024) pgatotaluse
115          from dba_hist_pgastat a, dba_hist_snapshot b
116         where a.snap_id = b.snap_id
117           and a.name = 'total PGA allocated'
118           and a.instance_number = 2
119           and b.instance_number = 2) b
120 where a.snap_id = b.snap_id
121 order by 1;

复制

version_rpt3_25.sql脚本内容

可以直接使用sys用户运行：

  1create or replace view SQL_SHARED_CURSOR
  2as select * from sys.v$sql_shared_cursor;
  3
  4
  5create or replace view h$pseudo_cursor as
  6select Pseudo_cursor, sql_id,obj_id hex_obj_id
  7     ,obj# object_id, u.name owner, o.name object_name
  8     ,address,hash_value,SHARABLE_MEM,parse_calls,VERSION_COUNT,is_obsolete
  9from (select distinct 
 10             KGLNAOBJ Pseudo_cursor,kglobt03 sql_id
 11        ,KGLHDPAR address,KGLNAHSH hash_value
 12        ,KGLOBHS0+KGLOBHS1+KGLOBHS2+KGLOBHS3+KGLOBHS4+KGLOBHS5+KGLOBHS6 SHARABLE_MEM
 13        ,KGLOBT12 parse_calls
 14        ,KGLHDEXC executions
 15        ,KGLOBCCC VERSION_COUNT
 16        ,decode(kglobt33, 1, 'Y', 'N') is_obsolete
 17        ,substr(KGLNAOBJ
 18               ,instr(KGLNAOBJ,'_',1,3)+1
 19               ,instr(KGLNAOBJ,'_',1,4)-instr(KGLNAOBJ,'_',1,3)-1) obj_id 
 20       ,(case when 
 21         replace(translate(substr(upper(KGLNAOBJ)
 22                                 ,instr(KGLNAOBJ,'_',1,3)+1
 23                                 ,instr(KGLNAOBJ,'_',1,4)
 24                                  -instr(KGLNAOBJ,'_',1,3)-1)
 25                          ,'0123456789ABCDEF','................')
 26                ,'.') is null then 'Y' else 'N' end) is_safe_to_compare
 27            from x$kglob) k
 28   , obj$ o, user$ u
 29where obj#=decode(is_safe_to_compare,'Y',to_number(obj_id,'xxxxxxxxxx'),0)
 30   and o.owner#=u.user#;
 31
 32Create or replace view H$PARAMETER
 33as
 34select a.ksppinm  NAME,
 35     a.ksppdesc DESCRIPTION,
 36     b.ksppstvl SESSION_VALUE,
 37     c.ksppstvl SYSTEM_VALUE
 38from x$ksppi a, x$ksppcv b, x$ksppsv c
 39where a.indx = b.indx and a.indx = c.indx;
 40
 41
 42create or replace function debug_version_rpt return DBMS_DEBUG_VC2COLL PIPELINED is
 43v_status number;
 44v_info varchar2(32767);
 45begin
 46 loop
 47  v_status := dbms_pipe.receive_message('version_rpt',0);
 48  if v_status = 0 then
 49   dbms_pipe.unpack_message(v_info);
 50   pipe row (v_info);
 51  else
 52   return;
 53  end if;
 54 end loop ;
 55end;
 56/
 57
 58create or replace function version_rpt(p_sql_id varchar2 default null,p_hash number default null,p_debug char default 'N') return DBMS_DEBUG_VC2COLL PIPELINED is
 59 type vc_arr is table of varchar2(32767) index by binary_integer;
 60 type num_arr is table of number index by binary_integer;
 61
 62 v_version varchar2(100);
 63 v_instance varchar2(100);
 64 v_colname vc_arr;
 65 v_Ycnt num_arr;
 66 v_count number:=-1;
 67 v_no number;
 68 v_all_no number:=-1;
 69
 70 v_query varchar2(4000);
 71 v_sql_where varchar2(4000):='';
 72 v_sql_where2 varchar2(4000):='';
 73 v_sql_id varchar2(15):=p_sql_id;
 74 v_addr varchar2(100);
 75 V_coladdr varchar2(100);
 76 v_hash number:=p_hash;
 77 v_mem number;
 78 v_parses number;
 79 v_execs number;
 80 v_value varchar2(100);
 81
 82 theCursor number;
 83 columnValue char(1);
 84 status number;
 85
 86 v_driver varchar2(1000);
 87 TYPE cursor_ref IS REF CURSOR;
 88 vc cursor_ref;
 89
 90 v_bind_dumped boolean:=false;
 91 v_auth_dumped boolean:=false;
 92
 93           v_phv num_arr;
 94          v_phvc num_arr;
 95
 96procedure debugme(p_info varchar2) is
 97v_st number;
 98begin
 99 if p_debug='Y' then
100  dbms_pipe.pack_message(p_info);
101  v_st := dbms_pipe.send_message('version_rpt',5);
102  if v_st=1 then dbms_pipe.purge('version_rpt'); end if;
103 end if;
104end;
105
106BEGIN
107 if p_debug='Y' then
108  status:=DBMS_PIPE.CREATE_PIPE ( pipename=>'version_rpt',maxpipesize=>1024*1024);
109  if status<>0 then pipe row ('Cannot debug'); return; end if;
110 end if;
111 debugme('instance version');
112
113 select version,'Host: '||HOST_NAME||' Instance '||INSTANCE_NUMBER||' : '||INSTANCE_NAME
114  into v_version , v_instance from v$instance;
115
116 debugme('build v$sqlarea query for '||v_sql_id||' '||v_hash);
117
118 /* 
119    This aggregate query is in the cases where
120    1) So many versions of the same SQL that many parents have been obsoleted.
121    2) there are more than 1 SQL with the same hash value or sql_id (very rare)
122 */
123
124 v_query:='select '|| case when v_version  like '9%' then '(NULL)' else '(sql_id)' end ||'  sql_id,'
125        ||  'max(sql_text) query,'
126        ||  'max(hash_value) hash,'
127        ||  'max(rawtohex(ADDRESS)) addr,'
128        ||  'sum(SHARABLE_MEM) SHARABLE_MEM,'
129        ||  'sum(PARSE_CALLS) PARSE_CALLS,'
130        ||  'sum(EXECUTIONS) EXECUTIONS'
131        ||  ' from v$sqlarea where'
132        || case when v_sql_id is not null then ' sql_id=:v_sql_id' else ' hash_value=:v_hash' end
133        || ' group by '|| (case when v_version like '9%' then 'NULL' else 'sql_id' end);
134
135 debugme(v_query);
136 if v_sql_id is not null then
137  open vc for v_query using v_sql_id;
138 else
139  open vc for v_query using v_hash;
140 end if;
141
142 debugme('Successful open cursor');
143
144 PIPE ROW('Note:438755.1 Version Count Report Version 3.2.5 -- Today''s Date '||to_char(sysdate,'dd-mon-yy hh24:mi')) ;
145 PIPE ROW('RDBMS Version :'||v_version||' '||v_instance);
146
147
148    debugme('fetch '||v_sql_id||' '||v_hash);
149    fetch vc into v_sql_id, v_query,v_hash,v_addr,v_mem,v_parses,v_execs;
150    if vc%notfound then
151    /* This execption could mean 2 things
152       1) The user gave a wrong SQLID
153       2) The SQLID belongs to a pseudo cursor.
154
155       if 2) then the info will not be in v$sqlarea so will try h$pseudo_cursor.
156       I do not query h$pseudo_cursor from the start to avoid as much as possible to access x$ views directly
157       due to their mutex and latch restrictions and to take advantage of any optimizations done in v$sqlarea.
158    */   
159     debugme('not found - alternate');
160     v_query:= replace(v_query,'v$sqlarea','H$PSEUDO_CURSOR');
161     v_query:= replace(v_query,'sql_text','Pseudo_cursor||''(PseudoCursor of ''||owner||''.''||object_name||'')''');
162     debugme(v_query); 
163
164     close vc;
165     if v_sql_id is not null then
166      open vc for v_query using v_sql_id;
167     else
168      open vc for v_query using v_hash;
169     end if;
170     fetch vc into v_sql_id, v_query,v_hash,v_addr,v_mem,v_parses,v_execs;
171     if vc%notfound then
172      return; /* Sorry, really is not in the library cache. */
173     end if;
174    end if;
175    close vc;
176
177
178    debugme('Header');
179     v_colname.delete;
180     v_Ycnt.delete;
181     v_count:=-1;
182     v_no:=0;
183     v_all_no:=-1;
184
185         PIPE ROW('==================================================================');
186         PIPE ROW('Addr: '||v_addr||'  Hash_Value: '||v_hash||'  SQL_ID '||v_sql_id);
187         PIPE ROW('Sharable_Mem: '||v_mem||' bytes   Parses: '||v_parses||'   Execs:'||v_execs);
188         PIPE ROW('Stmt: ');
189
190         for i in 0 .. trunc(length(v_query)/64)+1 loop
191          debugme('Print query line '||i);
192          PIPE ROW(i||' '||substr(v_query,1+i*64,64));
193         end loop;
194
195          debugme('Fetch SQL_SHARED_CURSOR columns');
196
197          SELECT COLUMN_NAME,0 bulk collect into v_colname,v_Ycnt
198           from cols
199          where table_name='SQL_SHARED_CURSOR'
200            and CHAR_LENGTH=1
201         order by column_id;
202
203         v_query:='';
204         debugme('Build Select List');
205         for i in 1 .. v_colname.count loop
206          v_query:= v_query ||','|| v_colname(i);
207         end loop;
208
209         debugme('Build Where');
210
211          if v_version like '9%' then
212           v_sql_where:=' WHERE ADDRESS=HEXTORAW('''||V_ADDR||''')';
213           v_sql_where2:=' WHERE KGLHDPAR=HEXTORAW('''||V_ADDR||''')';
214          elsif v_sql_id is not null then
215           v_sql_where:=' WHERE SQL_ID='''||v_sql_id||'''';
216           v_sql_where2:=v_sql_where;
217          else
218           v_sql_where:=' WHERE ADDRESS=HEXTORAW('''||V_ADDR||''')';
219           v_sql_where2:=v_sql_where;
220          end if;
221
222         debugme('Build Query');
223         v_query:= 'SELECT '||substr(v_query,2) || ' FROM SQL_SHARED_CURSOR ';
224
225         v_query:=v_query||v_sql_where2;
226
227         debugme(substr(v_sql_where2,-80));
228
229         debugme('Open Query');
230         begin
231          theCursor := dbms_sql.open_cursor;
232          sys.dbms_sys_sql.parse_as_user(C=>theCursor,STATEMENT=> v_Query, LANGUAGE_FLAG=>dbms_sql.native ,USERID=>0);
233
234          for i in 1 .. v_colname.count loop
235           dbms_sql.define_column( theCursor, i, columnValue, 8000 );
236          end loop;
237
238          status := dbms_sql.execute(theCursor);
239
240          debugme('Initiate Fetch');
241          while (dbms_sql.fetch_rows(theCursor) >0) loop
242           v_no:=0;
243           v_count:=v_count+1;
244           debugme('Fetch row '||v_count);
245           for i in 1..v_colname.count loop
246            dbms_sql.column_value(theCursor, i, columnValue);
247--            debugme('Decode row '||v_count||' column '||i);
248            if columnValue='Y' then
249             v_Ycnt(i):=v_Ycnt(i)+1;
250            else
251             v_no:=v_no+1;
252            end if;
253           end loop;
254
255           if v_no=v_colname.count then
256            v_all_no:=v_all_no+1;
257           end if;
258          end loop;
259          dbms_sql.close_cursor(theCursor);
260         end;
261
262         debugme('Version summary');
263         PIPE ROW('');
264         PIPE ROW('Versions Summary');
265         PIPE ROW('----------------');
266         for i in 1 .. v_colname.count loop
267          if v_Ycnt(i)>0 then
268           PIPE ROW(v_colname(i)||' :'||v_Ycnt(i));
269          end if;
270         end loop;
271         If v_all_no>1 then
272          PIPE ROW('Versions with ALL Columns as "N" :'||v_all_no);
273         end if;
274         PIPE ROW(' ');
275         PIPE ROW('Total Versions:'||v_count);
276
277         PIPE ROW(' ');
278         PIPE ROW('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ');
279         V_value:=NULL;
280         v_query:='select max(SYSTEM_VALUE) into :v_value from h$parameter where name=''cursor_sharing'' ';
281         execute immediate v_query into v_value;
282         if v_value is not null then
283          PIPE ROW('cursor_sharing = '||v_value);
284         end if;
285
286          V_NO:=NULL;
287          v_query:='select max(SYSTEM_VALUE) into :v_no from h$parameter where name=''_cursor_obsolete_threshold'' ';
288          execute immediate v_query into v_no;
289          if v_no is not null then
290           PIPE ROW('_cursor_obsolete_threshold = '||v_no||' (See Note:10187168.8)');
291          end if;
292
293         PIPE ROW('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ');
294
295         PIPE ROW(' ');
296
297         v_phv.delete;
298         v_phvc.delete;
299
300         debugme('PHV');
301         v_query:='select plan_hash_value,count(*) from v$sql '||v_sql_where||' group by plan_hash_value';
302
303         execute immediate v_query bulk collect into  v_phv,v_phvc;
304
305         PIPE ROW('Plan Hash Value Summary');
306         PIPE ROW('-----------------------');
307         PIPE ROW('Plan Hash Value Count');
308         PIPE ROW('=============== =====');
309         for i in 1 .. v_phv.count loop
310          PIPE ROW(to_char(v_phv(i),'99999999999999')||' '||to_char(v_phvc(i)));
311         end loop;
312         PIPE ROW(' ');
313
314
315  for i in 1 .. v_colname.count loop
316   debugme('Diag for '||v_colname(i)||' Ycnt:'||v_Ycnt(i));
317   if v_Ycnt(i)>0 then
318
319    PIPE ROW('~~~~~~~~~~~~~~'||rpad('~',length(v_colname(i)),'~'));
320    PIPE ROW('Details for '||v_colname(i)||' :');
321    PIPE ROW('');
322    if ( v_colname(i) like '%BIND%')
323            or  (v_version like '11.1%' and v_colname(i)='ROW_LEVEL_SEC_MISMATCH') then
324     if v_bind_dumped=true then -- Dump only once
325      PIPE ROW('Details shown already.');
326     else
327      v_bind_dumped:=true;
328      if v_version like '9%' then
329       PIPE ROW('No details for '||v_version);
330      else
331       PIPE ROW('Consolidated details for BIND* columns:');
332       PIPE ROW('BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and');
333       PIPE ROW('BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)');
334       PIPE ROW('');
335       declare
336        v_position num_arr;
337        v_maxlen num_arr;
338        v_minlen num_arr;
339        v_dtype num_arr;
340        v_prec num_arr;
341        v_scale num_arr;
342        v_n num_arr;
343        v_bg char(3);
344        v_bgflag char(1):='N';
345
346       begin
347        v_query:='select position,min(max_length),max(max_length),datatype,precision,scale,count(*) n'
348               ||' from v$sql_bind_capture where sql_id=:v_sql_id'
349               ||' group by sql_id,position,datatype,precision,scale'
350               ||' order by sql_id,position,datatype,precision,scale';
351
352        EXECUTE IMMEDIATE v_query
353        bulk collect into v_position, v_minlen, v_maxlen , v_dtype ,v_prec ,v_scale , v_n
354        using v_sql_id;
355
356        PIPE ROW('from v$sql_bind_capture');
357        PIPE ROW('COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)');
358        PIPE ROW('======== ======== =============== =============== ======== =============== =================');
359        for c in 1 .. v_position.count loop
360         v_bg:=(case when v_maxlen(c)>v_minlen(c) then 'Yes' else 'No' end);
361         v_bgflag:=(case when v_bg='Yes' and v_n(c)>99 then 'Y' end);
362         PIPE ROW( to_char(v_n(c),'9999999')||' '||to_char(v_position(c),'9999999')||' '|| to_char(v_minlen(c),'99999999999999')
363                  ||' '|| to_char(v_maxlen(c),'99999999999999')
364                  ||' '|| to_char(v_dtype(c),'9999999')
365                  ||'     '||v_bg
366                  ||'          ('|| v_prec(c)||','||v_scale(c)||')'                 
367                 );
368        end loop;
369
370        if ( v_bgflag='Y' ) then
371     PIPE ROW ('The above table shows binds with 100+ versions in the same position with different MAX_LENGTHs.');
372     PIPE ROW ('See "Add event to improve cursor sharability on BIND_LENGTH_UPGRADEABLE" ');
373    end if;
374
375        if v_version not like '10%' and v_version not like '9%' then
376         v_query:='select sum(decode(IS_OBSOLETE,''Y'', 1, 0)),sum(decode(IS_BIND_SENSITIVE ,''Y'',1, 0))'
377                ||',sum(decode(IS_BIND_AWARE,''Y'',1,0)),sum(decode(IS_SHAREABLE,''Y'',1,0))'
378                ||' from v$sql where sql_id = :v_sql_id';
379
380         EXECUTE IMMEDIATE v_query
381         bulk collect into v_position, v_minlen, v_maxlen , v_dtype
382         using v_sql_id;
383
384         PIPE ROW('');
385         PIPE ROW('SUM(DECODE(column,Y, 1, 0) FROM V$SQL');
386         PIPE ROW('IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE');
387         PIPE ROW('=========== ================= ============= ============');
388         for c in 1 .. v_position.count loop
389          PIPE ROW(to_char(v_position(c),'9999999999')||' '|| to_char(v_minlen(c),'9999999999999999')
390                  ||' '|| to_char(v_maxlen(c),'999999999999')
391                  ||' '|| to_char(v_dtype(c),'99999999999'));
392         end loop;
393        end if;
394       end;
395      end if;
396     end if;
397    elsif v_colname(i) ='OPTIMIZER_MODE_MISMATCH' then
398      for c in (select OPTIMIZER_MODE,count(*) n from v$sql where hash_value=v_hash group by OPTIMIZER_MODE) loop
399       PIPE ROW(c.n||' versions with '||c.OPTIMIZER_MODE);
400      end loop;
401    elsif v_colname(i) ='OPTIMIZER_MISMATCH' then
402      if v_version like '9%' then
403       PIPE ROW('No details available for '||v_version);
404      else
405       declare
406        v_param vc_arr;
407        v_value vc_arr;
408        v_n num_arr;
409       begin
410        v_query:='select o.NAME,o.VALUE ,count(*) n '
411                   ||'from V$SQL_OPTIMIZER_ENV o,sql_shared_cursor s '
412                   ||'where ISDEFAULT=''NO'' '
413                   ||'  and OPTIMIZER_MISMATCH=''Y'' '
414                   ||'  and s.sql_id=:v_sql_id '
415                   ||'  and o.sql_id=s.sql_id '
416                   ||'  and o.CHILD_ADDRESS=s.CHILD_ADDRESS '
417                   ||' group by o.NAME,o.VALUE ';
418        EXECUTE IMMEDIATE v_query
419        bulk collect into v_param,v_value,v_n using v_sql_id ;
420
421        for c in 1 .. v_n.count  loop
422         PIPE ROW(v_n(c)||' versions with '||v_param(c)||' = '||v_value(c));
423        end loop;
424       end;
425      end if;
426    elsif v_colname(i) ='AUTH_CHECK_MISMATCH' then
427       declare
428        v_pusr num_arr;
429        v_pschid num_arr;
430        v_pschname vc_arr;
431        v_n num_arr;
432       begin
433
434        if v_version like '9%' then
435         v_query:='select  PARSING_USER_ID, PARSING_SCHEMA_ID, ''n/a'' ,count(*) n from  v$sql '
436                 ||v_sql_where
437                 ||' group by PARSING_USER_ID, PARSING_SCHEMA_ID,''n/a''';
438        else
439         v_query:='select  PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME ,count(*) n from  v$sql '
440                 ||v_sql_where
441                 ||' group by PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME';
442        end if;
443        EXECUTE IMMEDIATE v_query
444        bulk collect into v_pusr,v_pschid,v_pschname,v_n;
445
446        PIPE ROW('  # of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME');
447        PIPE ROW('========== =============== ================= ===================');
448        for c in 1 .. v_n.count loop
449         PIPE ROW(to_char(v_n(c),'999999999')|| TO_CHAR(v_pusr(c),'9999999999999999')|| to_char(v_pschid(c),'99999999999999999')||' '||v_pschname(c));
450        end loop;
451       end;
452    elsif v_colname(i) = 'TRANSLATION_MISMATCH' then
453       declare
454        v_objn  num_arr;
455        v_objow vc_arr;
456        v_objnm vc_arr;
457       begin
458        v_query:='select distinct p.OBJECT#,p.OBJECT_OWNER,p.OBJECT_NAME'
459           ||' from (select OBJECT_NAME ,count(distinct object#) n from v$sql_plan '
460                  ||v_sql_where
461                  ||' and object_name is not null group by OBJECT_NAME ) d'
462           ||' ,v$sql_plan p where d.object_name=p.object_name and d.n>1';
463
464        EXECUTE IMMEDIATE v_query
465         bulk collect into v_objn,v_objow,v_objnm;
466
467        If v_objn.count>0 then
468         PIPE ROW('Summary of objects probably causing TRANSLATION_MISMATCH');
469         PIPE ROW(' ');
470         PIPE ROW('     Object# Owner.Object_Name');
471         PIPE ROW('============ =================');
472         for c in 1 .. v_objn.count loop
473          PIPE ROW(to_char(v_objn(c),'99999999999')||' '||v_objow(c)||'.'||v_objnm(c));
474         end loop;
475        else
476         PIPE ROW('No objects in the plans with same name and different owner were found.');
477        end if;
478       end;
479    else
480     PIPE ROW('No details available');
481    end if;
482   end if;
483 end loop;
484 debugme('cursortrace');
485 IF v_version not like '9%' then
486  PIPE ROW('####');
487  PIPE ROW('To further debug Ask Oracle Support for the appropiate level LLL.');
488  if v_version in ('10.2.0.1.0','10.2.0.2.0','10.2.0.3.0') THEN
489   PIPE ROW('and read note:457225.1 Cannot turn off Trace after setting CURSORTRACE EVENT');
490  end if;
491  PIPE ROW('alter session set events ');
492  PIPE ROW(' ''immediate trace name cursortrace address '||v_hash||', level LLL'';');
493  PIPE ROW('To turn it off do use address 1, level 2147483648');
494 end if;
495 PIPE ROW('================================================================');
496 debugme('End of version_rpt');
497 return;
498 exception
499  when others then
500   PIPE ROW('Error :'||sqlerrm);
501   PIPE ROW('for Addr: '||v_addr||'  Hash_Value: '||v_hash||'  SQL_ID '||v_sql_id);
502   for i in 0 .. trunc(length(v_query)/64) loop
503    PIPE ROW(i||' '||substr(v_query,1+i*64,64));
504   end loop;
505 return;
506end;
507/
508
509
510
511
512
513rem select b.* from v$sqlarea a ,table(version_rpt(a.sql_id,null,'Y')) b where loaded_versions >=100;
514
515rem select * from table(debug_version_rpt);
516

复制

参考

●Bug 13588248 - "library cache: mutex X" contention for objects with very many library cache locks (Doc ID 13588248.8)
●Troubleshooting Databases Hang Due to Heavy Contention for 'library cache: mutex X' Waits (Oracle 11.2 and Later) (Doc ID 2051456.1)
●Troubleshooting 'library cache: mutex X' Waits. (Doc ID 1357946.1)
●Troubleshooting: High Version Count Issues (Doc ID 296377.1)  故障排除: 版本数高(High Version Count)的问题 (Doc ID 2896923.1)    
●WAITEVENT: "library cache: mutex X" (Doc ID 727400.1)
●Library Cache Lock on Namespace SQL AREA BUILD(82) Caused by Failed Parse (Doc ID 2294281.1)

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

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

诊断 ’library cache: mutex X’ 等待 (Doc ID 2331144.1)

Troubleshooting 'cursor: pin S wait on X' waits. (Doc ID 1349387.1)

故障排除“cursor: pin S wait on X”等待事件 (Doc ID 2901617.1)  Troubleshooting 'cursor: pin S wait on X' waits. (Doc ID 1349387.1)

Troubleshooting: Understanding and Tuning the Shared Pool (Doc ID 62143.1)   故障排除：Shared Pool优化 (Doc ID 1523934.1)

WAITEVENT: "cursor: pin S" Reference Note (Doc ID 1310764.1)

高版本数（>1024）的SQL语句在升级到12.2及更高版本后会导致数据库性能下降 (Doc ID 2469639.1)  High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance (Doc ID 2431353.1)