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

发布日期 2024年4月14日 · 已更新 2024年4月19日

Tags： Oracle 原创总结版本数高(High Version Count)library cache lock cursor: pin S wait on X version_rpt 解析失败 library cache: mutex X 游标共享游标失效 cursor: pin S

👉 本文共约16946个字，系统预计阅读时间或需64分钟。

相关等待事件介绍
1、library cache lock
2、library cache: mutex X
3、enq: TX - row lock contention
4、cursor: mutex S和cursor: mutex X
5、cursor: pin S wait on X和cursor: pin S
cursor: pin S wait on X
cursor: pin S
游标不能共享（version count过高）的一些原因
游标解析失败
游标失效（INVALIDATE）介绍
其它分析
AWR报告分析
Version Count 、游标失效、游标Reloads示例
library cache: mutex X示例
SQL AREA BUILD
Systemstate dump示例SQL AREA BUILD
CURSOR_SHARING=SIMILAR参数说明
等待事件解决方案
相关SQL
version_rpt3_25.sql脚本内容
相关案例
参考

相关等待事件介绍

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在执行。

- Library Cache statistics 部分显示 reloads 数很高并且 (每小时几千次) invalidations （ Invali- dations）也很高。

- "% SQL with executions>1" 超过 60%, 表明SQL语句共享率较高。

- 确认 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参数进行解析获取内部等待：

-- 对于library cache lock等待事件，这里的p3值一般是5373954，16进制为00520002，前面的4位0052代表namespace,后面的4位0002代表mode，而0052的10进制为82，82对应的NAMESPACE为SQL AREA BUILD
 select  event,p3 , to_char(p3,'xxxxxxxxxxxx') p3_16,count(*) from lhr.ash2
  where event in ('library cache lock')
   group by event,p3 
   order by 4 desc;  

select to_char(5373954,'xxxxxxxxxxxx') p3_hex, to_number('0052','xxxx')from dual; 

SELECT distinct KGLHDNSP,KGLHDNSD FROM X$KGLOB D WHERE KGLHDNSD like '%SQL AREA%'   ORDER BY KGLHDNSP; 
select distinct indx,kglstdsc from x$kglst d where  kglstdsc like '%SQL AREA%'   ORDER BY indx;

 0  SQL AREA
75  SQL AREA STATS
82  SQL AREA BUILD    --  通常是由于大量解析导致，或SQL AREA上的问题，如SQL解析失败

-- 对于library cache lock等待事件，这里的p3值一般是5373954，16进制为00520002，前面的4位0052代表namespace,后面的4位0002代表mode，而0052的10进制为82，82对应的NAMESPACE为SQL AREA BUILD

select event,p3 , to_char(p3,'xxxxxxxxxxxx') p3_16,count(*) from lhr.ash2

where event in ('library cache lock')

group by event,p3

order by 4 desc;

select to_char(5373954,'xxxxxxxxxxxx') p3_hex, to_number('0052','xxxx')from dual;

SELECT distinct KGLHDNSP,KGLHDNSD FROM X$KGLOB D WHERE KGLHDNSD like '%SQL AREA%' ORDER BY KGLHDNSP;

select distinct indx,kglstdsc from x$kglst d where kglstdsc like '%SQL AREA%' ORDER BY indx;

0 SQL AREA

75 SQL AREA STATS

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

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

● 某些已知的 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和cursor: mutex S的常见原因：

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

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

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

•LANGUAGE_MISMATCH原因导致：

a、Database Hang With 'cursor: mutex X' Contention Due To High Version Count Under LANGUAGE_MISMATCH (Doc ID 2542447.1)、数据库挂起由于 LANGUAGE_MISMATCH 的 High Version Count 导致 'cursor：mutex X' 争用 (Doc ID 2577528.1)

• 其它已知bug：

a、cdb和pdb的字符集不一致导致bug25054064，Bug 25054064 - Cursor Has High Version Count In PDB Whose Character Set Is Different From CDB$ROOT (Doc ID 25054064.8)，也会导致LANGUAGE_MISMATCH：

select * from v$nls_parameters where parameter like 'NLS_CHARACTERSET';

1	select * from v$nls_parameters where parameter like 'NLS_CHARACTERSET';

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 语句：

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

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

Use the LOCATION_ID returned above in this SQL:

SELECT MUTEX_TYPE, LOCATION
   FROM x$mutex_sleep
  WHERE mutex_type like 'Cursor Pin%'
    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过高）的一些原因

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

例如：

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)

游标解析失败

参考：https://xmmup.com/oraclejiexicuowudegenzongbanfa.html

游标失效（INVALIDATE）介绍

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

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

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

SELECT SUBSTR(sql_text, 1, 40) "SQL",

invalidations

FROM v$sqlarea

ORDER 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操作导致游标失效实验：

CREATE TABLE lhr_table_temp (a VARCHAR2(4000));
INSERT INTO lhr_table_temp (a)  VALUES ('test data session 1'); 
commit;

create index idx1 on lhr_table_temp(a);

select * from lhr_table_temp;
select sql_text,sql_id,version_count,loads,invalidations,parse_calls from v$sqlarea a where sql_text  like '%select * from lhr_table_temp%';
select 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%';

-- truncate table lhr_table_temp;

alter index idx1 monitoring USAGE;
alter index idx1 nomonitoring USAGE;

SELECT * FROM V$OBJECT_USAGE;

CREATE TABLE lhr_table_temp (a VARCHAR2(4000));

INSERT INTO lhr_table_temp (a) VALUES ('test data session 1');

commit;

create index idx1 on lhr_table_temp(a);

select * from lhr_table_temp;

select sql_text,sql_id,version_count,loads,invalidations,parse_calls from v$sqlarea a where sql_text like '%select * from lhr_table_temp%';

select 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%';

-- truncate table lhr_table_temp;

alter index idx1 monitoring USAGE;

alter index idx1 nomonitoring USAGE;

SELECT * FROM V$OBJECT_USAGE;

loads为3表示加载了3次，即硬解析了3次，分别为建表后第1次查询，做monitoring后的查询，做nomonitoring后的查询
invalidations为3表示游标失效了2次，因为做monitoring失效1次，做nomonitoring失效1次
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示例

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

定位出硬解析和高版本的 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$SQL_SHARED_CURSOR 检查引起 SQL 高版本的原因。

Document 438755.1 Formated V$SQL_SHARED_CURSOR Report by SQLID or Hash Value
Document 296377.1 Troubleshooting: High Version Count Issues

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:


PROCESS 154:
----------------------------------------
SO: 0x734bab158, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x734bab158, name=process, file=ksu.h LINE:12616, pg=0
(process) Oracle pid:154, ser:6, calls cur/top: 0x6bf2ee508/0x6bf2ee508
flags : (0x0) -
<...snipped...>
O/S info: user: xxxx, term: UNKNOWN, ospid: 1636
OSD pid info: Unix process pid: 1636, image: oracle@xxxx
Short stack dump:
ksedsts()+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

----------------------------------------
SO: 0x729475520, type: 4, owner: 0x734bab158, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x734bab158, name=session, file=ksu.h LINE:12624, pg=0
(session) sid: 4220 ser: 25 trans: (nil), creator: 0x734bab158
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
DID: , short-term DID:
txn branch: (nil)
oct: 47, prv: 0, sql: 0x74f6bc308, psql: 0x7476122d8, user: 76/xxxxx
ksuxds FALSE at location: 0
service name: xxxxxx
client details:
O/S info: user: xxxxxx, term: xxxxxx, ospid: 6152:13936
machine: xxxx\xxxxprogram: xxxx.exe
application name: xxxx.exe, hash value=1240902255
Current Wait Stack:
0: waiting for 'library cache lock'
handle address=0x743bb3e98, lock address=0x743baf088, 100*mode+namespace=0x520002 ===========> namespace is 0x52 (dec:82), sql area build 
wait_id=9268 seq_num=9913 snap_id=1
wait times: snap=0.000729 sec, exc=0.000729 sec, total=0.000729 sec
wait times: max=infinite, heur=0.000729 sec
wait counts: calls=1 os=1
in_wait=1 iflags=0x5a2

PROCESS 154:

----------------------------------------

SO: 0x734bab158, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3

proc=0x734bab158, name=process, file=ksu.h LINE:12616, pg=0

(process) Oracle pid:154, ser:6, calls cur/top: 0x6bf2ee508/0x6bf2ee508

flags : (0x0) -

<...snipped...>

O/S info: user: xxxx, term: UNKNOWN, ospid: 1636

OSD pid info: Unix process pid: 1636, image: oracle@xxxx

Short stack dump:

ksedsts()+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

----------------------------------------

SO: 0x729475520, type: 4, owner: 0x734bab158, flag: INIT/-/-/0x00 if: 0x3 c: 0x3

proc=0x734bab158, name=session, file=ksu.h LINE:12624, pg=0

(session) sid: 4220 ser: 25 trans: (nil), creator: 0x734bab158

flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

flags2: (0x40009) -/-/INC

DID: , short-term DID:

txn branch: (nil)

oct: 47, prv: 0, sql: 0x74f6bc308, psql: 0x7476122d8, user: 76/xxxxx

ksuxds FALSE at location: 0

service name: xxxxxx

client details:

O/S info: user: xxxxxx, term: xxxxxx, ospid: 6152:13936

machine: xxxx\xxxxprogram: xxxx.exe

application name: xxxx.exe, hash value=1240902255

Current Wait Stack:

0: waiting for 'library cache lock'

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

wait_id=9268 seq_num=9913 snap_id=1

wait times: snap=0.000729 sec, exc=0.000729 sec, total=0.000729 sec

wait times: max=infinite, heur=0.000729 sec

wait counts: calls=1 os=1

in_wait=1 iflags=0x5a2

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)，可以如下配置：

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

1	ALTER 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，这会导致内存分配问题，另外，用完及时关闭。
ALTER session SET EVENTS '10503 trace name context forever, level 4000'; ALTER session SET EVENTS '10503 trace name context off';
1
2
ALTER session SET EVENTS '10503 trace name context forever, level 4000';
ALTER 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，并重启数据库

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

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

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

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

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

version_rpt3_25.sql脚本内容

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


create or replace view SQL_SHARED_CURSOR
as select * from sys.v$sql_shared_cursor;

create or replace view h$pseudo_cursor as
select Pseudo_cursor, sql_id,obj_id hex_obj_id
     ,obj# object_id, u.name owner, o.name object_name
     ,address,hash_value,SHARABLE_MEM,parse_calls,VERSION_COUNT,is_obsolete
from (select distinct 
             KGLNAOBJ Pseudo_cursor,kglobt03 sql_id
        ,KGLHDPAR address,KGLNAHSH hash_value
        ,KGLOBHS0+KGLOBHS1+KGLOBHS2+KGLOBHS3+KGLOBHS4+KGLOBHS5+KGLOBHS6 SHARABLE_MEM
        ,KGLOBT12 parse_calls
        ,KGLHDEXC executions
        ,KGLOBCCC VERSION_COUNT
        ,decode(kglobt33, 1, 'Y', 'N') is_obsolete
        ,substr(KGLNAOBJ
               ,instr(KGLNAOBJ,'_',1,3)+1
               ,instr(KGLNAOBJ,'_',1,4)-instr(KGLNAOBJ,'_',1,3)-1) obj_id 
       ,(case when 
         replace(translate(substr(upper(KGLNAOBJ)
                                 ,instr(KGLNAOBJ,'_',1,3)+1
                                 ,instr(KGLNAOBJ,'_',1,4)
                                  -instr(KGLNAOBJ,'_',1,3)-1)
                          ,'0123456789ABCDEF','................')
                ,'.') is null then 'Y' else 'N' end) is_safe_to_compare
            from x$kglob) k
   , obj$ o, user$ u
where obj#=decode(is_safe_to_compare,'Y',to_number(obj_id,'xxxxxxxxxx'),0)
   and o.owner#=u.user#;

Create or replace view H$PARAMETER
as
select a.ksppinm  NAME,
     a.ksppdesc DESCRIPTION,
     b.ksppstvl SESSION_VALUE,
     c.ksppstvl SYSTEM_VALUE
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx;

create or replace function debug_version_rpt return DBMS_DEBUG_VC2COLL PIPELINED is
v_status number;
v_info varchar2(32767);
begin
 loop
  v_status := dbms_pipe.receive_message('version_rpt',0);
  if v_status = 0 then
   dbms_pipe.unpack_message(v_info);
   pipe row (v_info);
  else
   return;
  end if;
 end loop ;
end;
/

create 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
 type vc_arr is table of varchar2(32767) index by binary_integer;
 type num_arr is table of number index by binary_integer;

 v_version varchar2(100);
 v_instance varchar2(100);
 v_colname vc_arr;
 v_Ycnt num_arr;
 v_count number:=-1;
 v_no number;
 v_all_no number:=-1;

 v_query varchar2(4000);
 v_sql_where varchar2(4000):='';
 v_sql_where2 varchar2(4000):='';
 v_sql_id varchar2(15):=p_sql_id;
 v_addr varchar2(100);
 V_coladdr varchar2(100);
 v_hash number:=p_hash;
 v_mem number;
 v_parses number;
 v_execs number;
 v_value varchar2(100);

 theCursor number;
 columnValue char(1);
 status number;

 v_driver varchar2(1000);
 TYPE cursor_ref IS REF CURSOR;
 vc cursor_ref;

 v_bind_dumped boolean:=false;
 v_auth_dumped boolean:=false;

           v_phv num_arr;
          v_phvc num_arr;

procedure debugme(p_info varchar2) is
v_st number;
begin
 if p_debug='Y' then
  dbms_pipe.pack_message(p_info);
  v_st := dbms_pipe.send_message('version_rpt',5);
  if v_st=1 then dbms_pipe.purge('version_rpt'); end if;
 end if;
end;

BEGIN
 if p_debug='Y' then
  status:=DBMS_PIPE.CREATE_PIPE ( pipename=>'version_rpt',maxpipesize=>1024*1024);
  if status<>0 then pipe row ('Cannot debug'); return; end if;
 end if;
 debugme('instance version');

 select version,'Host: '||HOST_NAME||' Instance '||INSTANCE_NUMBER||' : '||INSTANCE_NAME
  into v_version , v_instance from v$instance;

 debugme('build v$sqlarea query for '||v_sql_id||' '||v_hash);

 /* 
    This aggregate query is in the cases where
    1) So many versions of the same SQL that many parents have been obsoleted.
    2) there are more than 1 SQL with the same hash value or sql_id (very rare)
 */

 v_query:='select '|| case when v_version  like '9%' then '(NULL)' else '(sql_id)' end ||'  sql_id,'
        ||  'max(sql_text) query,'
        ||  'max(hash_value) hash,'
        ||  'max(rawtohex(ADDRESS)) addr,'
        ||  'sum(SHARABLE_MEM) SHARABLE_MEM,'
        ||  'sum(PARSE_CALLS) PARSE_CALLS,'
        ||  'sum(EXECUTIONS) EXECUTIONS'
        ||  ' from v$sqlarea where'
        || case when v_sql_id is not null then ' sql_id=:v_sql_id' else ' hash_value=:v_hash' end
        || ' group by '|| (case when v_version like '9%' then 'NULL' else 'sql_id' end);

 debugme(v_query);
 if v_sql_id is not null then
  open vc for v_query using v_sql_id;
 else
  open vc for v_query using v_hash;
 end if;

 debugme('Successful open cursor');

 PIPE ROW('Note:438755.1 Version Count Report Version 3.2.5 -- Today''s Date '||to_char(sysdate,'dd-mon-yy hh24:mi')) ;
 PIPE ROW('RDBMS Version :'||v_version||' '||v_instance);

    debugme('fetch '||v_sql_id||' '||v_hash);
    fetch vc into v_sql_id, v_query,v_hash,v_addr,v_mem,v_parses,v_execs;
    if vc%notfound then
    /* This execption could mean 2 things
       1) The user gave a wrong SQLID
       2) The SQLID belongs to a pseudo cursor.

       if 2) then the info will not be in v$sqlarea so will try h$pseudo_cursor.
       I do not query h$pseudo_cursor from the start to avoid as much as possible to access x$ views directly
       due to their mutex and latch restrictions and to take advantage of any optimizations done in v$sqlarea.
    */   
     debugme('not found - alternate');
     v_query:= replace(v_query,'v$sqlarea','H$PSEUDO_CURSOR');
     v_query:= replace(v_query,'sql_text','Pseudo_cursor||''(PseudoCursor of ''||owner||''.''||object_name||'')''');
     debugme(v_query); 

     close vc;
     if v_sql_id is not null then
      open vc for v_query using v_sql_id;
     else
      open vc for v_query using v_hash;
     end if;
     fetch vc into v_sql_id, v_query,v_hash,v_addr,v_mem,v_parses,v_execs;
     if vc%notfound then
      return; /* Sorry, really is not in the library cache. */
     end if;
    end if;
    close vc;

    debugme('Header');
     v_colname.delete;
     v_Ycnt.delete;
     v_count:=-1;
     v_no:=0;
     v_all_no:=-1;

         PIPE ROW('==================================================================');
         PIPE ROW('Addr: '||v_addr||'  Hash_Value: '||v_hash||'  SQL_ID '||v_sql_id);
         PIPE ROW('Sharable_Mem: '||v_mem||' bytes   Parses: '||v_parses||'   Execs:'||v_execs);
         PIPE ROW('Stmt: ');

         for i in 0 .. trunc(length(v_query)/64)+1 loop
          debugme('Print query line '||i);
          PIPE ROW(i||' '||substr(v_query,1+i*64,64));
         end loop;

          debugme('Fetch SQL_SHARED_CURSOR columns');

          SELECT COLUMN_NAME,0 bulk collect into v_colname,v_Ycnt
           from cols
          where table_name='SQL_SHARED_CURSOR'
            and CHAR_LENGTH=1
         order by column_id;

         v_query:='';
         debugme('Build Select List');
         for i in 1 .. v_colname.count loop
          v_query:= v_query ||','|| v_colname(i);
         end loop;

         debugme('Build Where');

          if v_version like '9%' then
           v_sql_where:=' WHERE ADDRESS=HEXTORAW('''||V_ADDR||''')';
           v_sql_where2:=' WHERE KGLHDPAR=HEXTORAW('''||V_ADDR||''')';
          elsif v_sql_id is not null then
           v_sql_where:=' WHERE SQL_ID='''||v_sql_id||'''';
           v_sql_where2:=v_sql_where;
          else
           v_sql_where:=' WHERE ADDRESS=HEXTORAW('''||V_ADDR||''')';
           v_sql_where2:=v_sql_where;
          end if;

         debugme('Build Query');
         v_query:= 'SELECT '||substr(v_query,2) || ' FROM SQL_SHARED_CURSOR ';

         v_query:=v_query||v_sql_where2;

         debugme(substr(v_sql_where2,-80));

         debugme('Open Query');
         begin
          theCursor := dbms_sql.open_cursor;
          sys.dbms_sys_sql.parse_as_user(C=>theCursor,STATEMENT=> v_Query, LANGUAGE_FLAG=>dbms_sql.native ,USERID=>0);

          for i in 1 .. v_colname.count loop
           dbms_sql.define_column( theCursor, i, columnValue, 8000 );
          end loop;

          status := dbms_sql.execute(theCursor);

          debugme('Initiate Fetch');
          while (dbms_sql.fetch_rows(theCursor) >0) loop
           v_no:=0;
           v_count:=v_count+1;
           debugme('Fetch row '||v_count);
           for i in 1..v_colname.count loop
            dbms_sql.column_value(theCursor, i, columnValue);
--            debugme('Decode row '||v_count||' column '||i);
            if columnValue='Y' then
             v_Ycnt(i):=v_Ycnt(i)+1;
            else
             v_no:=v_no+1;
            end if;
           end loop;

           if v_no=v_colname.count then
            v_all_no:=v_all_no+1;
           end if;
          end loop;
          dbms_sql.close_cursor(theCursor);
         end;

         debugme('Version summary');
         PIPE ROW('');
         PIPE ROW('Versions Summary');
         PIPE ROW('----------------');
         for i in 1 .. v_colname.count loop
          if v_Ycnt(i)>0 then
           PIPE ROW(v_colname(i)||' :'||v_Ycnt(i));
          end if;
         end loop;
         If v_all_no>1 then
          PIPE ROW('Versions with ALL Columns as "N" :'||v_all_no);
         end if;
         PIPE ROW(' ');
         PIPE ROW('Total Versions:'||v_count);

         PIPE ROW(' ');
         PIPE ROW('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ');
         V_value:=NULL;
         v_query:='select max(SYSTEM_VALUE) into :v_value from h$parameter where name=''cursor_sharing'' ';
         execute immediate v_query into v_value;
         if v_value is not null then
          PIPE ROW('cursor_sharing = '||v_value);
         end if;

          V_NO:=NULL;
          v_query:='select max(SYSTEM_VALUE) into :v_no from h$parameter where name=''_cursor_obsolete_threshold'' ';
          execute immediate v_query into v_no;
          if v_no is not null then
           PIPE ROW('_cursor_obsolete_threshold = '||v_no||' (See Note:10187168.8)');
          end if;

         PIPE ROW('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ');

         PIPE ROW(' ');

         v_phv.delete;
         v_phvc.delete;

         debugme('PHV');
         v_query:='select plan_hash_value,count(*) from v$sql '||v_sql_where||' group by plan_hash_value';

         execute immediate v_query bulk collect into  v_phv,v_phvc;

         PIPE ROW('Plan Hash Value Summary');
         PIPE ROW('-----------------------');
         PIPE ROW('Plan Hash Value Count');
         PIPE ROW('=============== =====');
         for i in 1 .. v_phv.count loop
          PIPE ROW(to_char(v_phv(i),'99999999999999')||' '||to_char(v_phvc(i)));
         end loop;
         PIPE ROW(' ');

  for i in 1 .. v_colname.count loop
   debugme('Diag for '||v_colname(i)||' Ycnt:'||v_Ycnt(i));
   if v_Ycnt(i)>0 then

    PIPE ROW('~~~~~~~~~~~~~~'||rpad('~',length(v_colname(i)),'~'));
    PIPE ROW('Details for '||v_colname(i)||' :');
    PIPE ROW('');
    if ( v_colname(i) like '%BIND%')
            or  (v_version like '11.1%' and v_colname(i)='ROW_LEVEL_SEC_MISMATCH') then
     if v_bind_dumped=true then -- Dump only once
      PIPE ROW('Details shown already.');
     else
      v_bind_dumped:=true;
      if v_version like '9%' then
       PIPE ROW('No details for '||v_version);
      else
       PIPE ROW('Consolidated details for BIND* columns:');
       PIPE ROW('BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and');
       PIPE ROW('BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)');
       PIPE ROW('');
       declare
        v_position num_arr;
        v_maxlen num_arr;
        v_minlen num_arr;
        v_dtype num_arr;
        v_prec num_arr;
        v_scale num_arr;
        v_n num_arr;
        v_bg char(3);
        v_bgflag char(1):='N';

       begin
        v_query:='select position,min(max_length),max(max_length),datatype,precision,scale,count(*) n'
               ||' from v$sql_bind_capture where sql_id=:v_sql_id'
               ||' group by sql_id,position,datatype,precision,scale'
               ||' order by sql_id,position,datatype,precision,scale';

        EXECUTE IMMEDIATE v_query
        bulk collect into v_position, v_minlen, v_maxlen , v_dtype ,v_prec ,v_scale , v_n
        using v_sql_id;

        PIPE ROW('from v$sql_bind_capture');
        PIPE ROW('COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)');
        PIPE ROW('======== ======== =============== =============== ======== =============== =================');
        for c in 1 .. v_position.count loop
         v_bg:=(case when v_maxlen(c)>v_minlen(c) then 'Yes' else 'No' end);
         v_bgflag:=(case when v_bg='Yes' and v_n(c)>99 then 'Y' end);
         PIPE ROW( to_char(v_n(c),'9999999')||' '||to_char(v_position(c),'9999999')||' '|| to_char(v_minlen(c),'99999999999999')
                  ||' '|| to_char(v_maxlen(c),'99999999999999')
                  ||' '|| to_char(v_dtype(c),'9999999')
                  ||'     '||v_bg
                  ||'          ('|| v_prec(c)||','||v_scale(c)||')'                 
                 );
        end loop;

        if ( v_bgflag='Y' ) then
     PIPE ROW ('The above table shows binds with 100+ versions in the same position with different MAX_LENGTHs.');
     PIPE ROW ('See "Add event to improve cursor sharability on BIND_LENGTH_UPGRADEABLE" ');
    end if;

        if v_version not like '10%' and v_version not like '9%' then
         v_query:='select sum(decode(IS_OBSOLETE,''Y'', 1, 0)),sum(decode(IS_BIND_SENSITIVE ,''Y'',1, 0))'
                ||',sum(decode(IS_BIND_AWARE,''Y'',1,0)),sum(decode(IS_SHAREABLE,''Y'',1,0))'
                ||' from v$sql where sql_id = :v_sql_id';

         EXECUTE IMMEDIATE v_query
         bulk collect into v_position, v_minlen, v_maxlen , v_dtype
         using v_sql_id;

         PIPE ROW('');
         PIPE ROW('SUM(DECODE(column,Y, 1, 0) FROM V$SQL');
         PIPE ROW('IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE');
         PIPE ROW('=========== ================= ============= ============');
         for c in 1 .. v_position.count loop
          PIPE ROW(to_char(v_position(c),'9999999999')||' '|| to_char(v_minlen(c),'9999999999999999')
                  ||' '|| to_char(v_maxlen(c),'999999999999')
                  ||' '|| to_char(v_dtype(c),'99999999999'));
         end loop;
        end if;
       end;
      end if;
     end if;
    elsif v_colname(i) ='OPTIMIZER_MODE_MISMATCH' then
      for c in (select OPTIMIZER_MODE,count(*) n from v$sql where hash_value=v_hash group by OPTIMIZER_MODE) loop
       PIPE ROW(c.n||' versions with '||c.OPTIMIZER_MODE);
      end loop;
    elsif v_colname(i) ='OPTIMIZER_MISMATCH' then
      if v_version like '9%' then
       PIPE ROW('No details available for '||v_version);
      else
       declare
        v_param vc_arr;
        v_value vc_arr;
        v_n num_arr;
       begin
        v_query:='select o.NAME,o.VALUE ,count(*) n '
                   ||'from V$SQL_OPTIMIZER_ENV o,sql_shared_cursor s '
                   ||'where ISDEFAULT=''NO'' '
                   ||'  and OPTIMIZER_MISMATCH=''Y'' '
                   ||'  and s.sql_id=:v_sql_id '
                   ||'  and o.sql_id=s.sql_id '
                   ||'  and o.CHILD_ADDRESS=s.CHILD_ADDRESS '
                   ||' group by o.NAME,o.VALUE ';
        EXECUTE IMMEDIATE v_query
        bulk collect into v_param,v_value,v_n using v_sql_id ;

        for c in 1 .. v_n.count  loop
         PIPE ROW(v_n(c)||' versions with '||v_param(c)||' = '||v_value(c));
        end loop;
       end;
      end if;
    elsif v_colname(i) ='AUTH_CHECK_MISMATCH' then
       declare
        v_pusr num_arr;
        v_pschid num_arr;
        v_pschname vc_arr;
        v_n num_arr;
       begin

        if v_version like '9%' then
         v_query:='select  PARSING_USER_ID, PARSING_SCHEMA_ID, ''n/a'' ,count(*) n from  v$sql '
                 ||v_sql_where
                 ||' group by PARSING_USER_ID, PARSING_SCHEMA_ID,''n/a''';
        else
         v_query:='select  PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME ,count(*) n from  v$sql '
                 ||v_sql_where
                 ||' group by PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME';
        end if;
        EXECUTE IMMEDIATE v_query
        bulk collect into v_pusr,v_pschid,v_pschname,v_n;

        PIPE ROW('  # of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME');
        PIPE ROW('========== =============== ================= ===================');
        for c in 1 .. v_n.count loop
         PIPE ROW(to_char(v_n(c),'999999999')|| TO_CHAR(v_pusr(c),'9999999999999999')|| to_char(v_pschid(c),'99999999999999999')||' '||v_pschname(c));
        end loop;
       end;
    elsif v_colname(i) = 'TRANSLATION_MISMATCH' then
       declare
        v_objn  num_arr;
        v_objow vc_arr;
        v_objnm vc_arr;
       begin
        v_query:='select distinct p.OBJECT#,p.OBJECT_OWNER,p.OBJECT_NAME'
           ||' from (select OBJECT_NAME ,count(distinct object#) n from v$sql_plan '
                  ||v_sql_where
                  ||' and object_name is not null group by OBJECT_NAME ) d'
           ||' ,v$sql_plan p where d.object_name=p.object_name and d.n>1';

        EXECUTE IMMEDIATE v_query
         bulk collect into v_objn,v_objow,v_objnm;

        If v_objn.count>0 then
         PIPE ROW('Summary of objects probably causing TRANSLATION_MISMATCH');
         PIPE ROW(' ');
         PIPE ROW('     Object# Owner.Object_Name');
         PIPE ROW('============ =================');
         for c in 1 .. v_objn.count loop
          PIPE ROW(to_char(v_objn(c),'99999999999')||' '||v_objow(c)||'.'||v_objnm(c));
         end loop;
        else
         PIPE ROW('No objects in the plans with same name and different owner were found.');
        end if;
       end;
    else
     PIPE ROW('No details available');
    end if;
   end if;
 end loop;
 debugme('cursortrace');
 IF v_version not like '9%' then
  PIPE ROW('####');
  PIPE ROW('To further debug Ask Oracle Support for the appropiate level LLL.');
  if v_version in ('10.2.0.1.0','10.2.0.2.0','10.2.0.3.0') THEN
   PIPE ROW('and read note:457225.1 Cannot turn off Trace after setting CURSORTRACE EVENT');
  end if;
  PIPE ROW('alter session set events ');
  PIPE ROW(' ''immediate trace name cursortrace address '||v_hash||', level LLL'';');
  PIPE ROW('To turn it off do use address 1, level 2147483648');
 end if;
 PIPE ROW('================================================================');
 debugme('End of version_rpt');
 return;
 exception
  when others then
   PIPE ROW('Error :'||sqlerrm);
   PIPE ROW('for Addr: '||v_addr||'  Hash_Value: '||v_hash||'  SQL_ID '||v_sql_id);
   for i in 0 .. trunc(length(v_query)/64) loop
    PIPE ROW(i||' '||substr(v_query,1+i*64,64));
   end loop;
 return;
end;
/

rem select b.* from v$sqlarea a ,table(version_rpt(a.sql_id,null,'Y')) b where loaded_versions >=100;

rem select * from table(debug_version_rpt);

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

create or replace view SQL_SHARED_CURSOR

as select * from sys.v$sql_shared_cursor;

create or replace view h$pseudo_cursor as

select Pseudo_cursor, sql_id,obj_id hex_obj_id

,obj# object_id, u.name owner, o.name object_name

,address,hash_value,SHARABLE_MEM,parse_calls,VERSION_COUNT,is_obsolete

from (select distinct

KGLNAOBJ Pseudo_cursor,kglobt03 sql_id

,KGLHDPAR address,KGLNAHSH hash_value

,KGLOBHS0+KGLOBHS1+KGLOBHS2+KGLOBHS3+KGLOBHS4+KGLOBHS5+KGLOBHS6 SHARABLE_MEM

,KGLOBT12 parse_calls

,KGLHDEXC executions

,KGLOBCCC VERSION_COUNT

,decode(kglobt33, 1, 'Y', 'N') is_obsolete

,substr(KGLNAOBJ

,instr(KGLNAOBJ,'_',1,3)+1

,instr(KGLNAOBJ,'_',1,4)-instr(KGLNAOBJ,'_',1,3)-1) obj_id

,(case when

replace(translate(substr(upper(KGLNAOBJ)

,instr(KGLNAOBJ,'_',1,3)+1

,instr(KGLNAOBJ,'_',1,4)

-instr(KGLNAOBJ,'_',1,3)-1)

,'0123456789ABCDEF','................')

,'.') is null then 'Y' else 'N' end) is_safe_to_compare

from x$kglob) k

, obj$ o, user$ u

where obj#=decode(is_safe_to_compare,'Y',to_number(obj_id,'xxxxxxxxxx'),0)

and o.owner#=u.user#;

Create or replace view H$PARAMETER

select a.ksppinm NAME,

a.ksppdesc DESCRIPTION,

b.ksppstvl SESSION_VALUE,

c.ksppstvl SYSTEM_VALUE

from x$ksppi a, x$ksppcv b, x$ksppsv c

where a.indx = b.indx and a.indx = c.indx;

create or replace function debug_version_rpt return DBMS_DEBUG_VC2COLL PIPELINED is

v_status number;

v_info varchar2(32767);

begin

loop

v_status := dbms_pipe.receive_message('version_rpt',0);

if v_status = 0 then

dbms_pipe.unpack_message(v_info);

pipe row (v_info);

else

return;

end if;

end loop ;

end;

create 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

type vc_arr is table of varchar2(32767) index by binary_integer;

type num_arr is table of number index by binary_integer;

v_version varchar2(100);

v_instance varchar2(100);

v_colname vc_arr;

v_Ycnt num_arr;

v_count number:=-1;

v_no number;

v_all_no number:=-1;

v_query varchar2(4000);

v_sql_where varchar2(4000):='';

v_sql_where2 varchar2(4000):='';

v_sql_id varchar2(15):=p_sql_id;

v_addr varchar2(100);

V_coladdr varchar2(100);

v_hash number:=p_hash;

v_mem number;

v_parses number;

v_execs number;

v_value varchar2(100);

theCursor number;

columnValue char(1);

status number;

v_driver varchar2(1000);

TYPE cursor_ref IS REF CURSOR;

vc cursor_ref;

v_bind_dumped boolean:=false;

v_auth_dumped boolean:=false;

v_phv num_arr;

v_phvc num_arr;

procedure debugme(p_info varchar2) is

v_st number;

begin

if p_debug='Y' then

dbms_pipe.pack_message(p_info);

v_st := dbms_pipe.send_message('version_rpt',5);

if v_st=1 then dbms_pipe.purge('version_rpt'); end if;

end if;

end;

BEGIN

if p_debug='Y' then

status:=DBMS_PIPE.CREATE_PIPE ( pipename=>'version_rpt',maxpipesize=>1024*1024);

if status<>0 then pipe row ('Cannot debug'); return; end if;

end if;

debugme('instance version');

select version,'Host: '||HOST_NAME||' Instance '||INSTANCE_NUMBER||' : '||INSTANCE_NAME

into v_version , v_instance from v$instance;

debugme('build v$sqlarea query for '||v_sql_id||' '||v_hash);

This aggregate query is in the cases where

1) So many versions of the same SQL that many parents have been obsoleted.

2) there are more than 1 SQL with the same hash value or sql_id (very rare)

v_query:='select '|| case when v_version like '9%' then '(NULL)' else '(sql_id)' end ||' sql_id,'

|| 'max(sql_text) query,'

|| 'max(hash_value) hash,'

|| 'max(rawtohex(ADDRESS)) addr,'

|| 'sum(SHARABLE_MEM) SHARABLE_MEM,'

|| 'sum(PARSE_CALLS) PARSE_CALLS,'

|| 'sum(EXECUTIONS) EXECUTIONS'

|| ' from v$sqlarea where'

|| case when v_sql_id is not null then ' sql_id=:v_sql_id' else ' hash_value=:v_hash' end

|| ' group by '|| (case when v_version like '9%' then 'NULL' else 'sql_id' end);

debugme(v_query);

if v_sql_id is not null then

open vc for v_query using v_sql_id;

else

open vc for v_query using v_hash;

end if;

debugme('Successful open cursor');

PIPE ROW('Note:438755.1 Version Count Report Version 3.2.5 -- Today''s Date '||to_char(sysdate,'dd-mon-yy hh24:mi')) ;

PIPE ROW('RDBMS Version :'||v_version||' '||v_instance);

debugme('fetch '||v_sql_id||' '||v_hash);

fetch vc into v_sql_id, v_query,v_hash,v_addr,v_mem,v_parses,v_execs;

if vc%notfound then

/* This execption could mean 2 things

1) The user gave a wrong SQLID

2) The SQLID belongs to a pseudo cursor.

if 2) then the info will not be in v$sqlarea so will try h$pseudo_cursor.

I do not query h$pseudo_cursor from the start to avoid as much as possible to access x$ views directly

due to their mutex and latch restrictions and to take advantage of any optimizations done in v$sqlarea.

debugme('not found - alternate');

v_query:= replace(v_query,'v$sqlarea','H$PSEUDO_CURSOR');

v_query:= replace(v_query,'sql_text','Pseudo_cursor||''(PseudoCursor of ''||owner||''.''||object_name||'')''');

debugme(v_query);

close vc;

if v_sql_id is not null then

open vc for v_query using v_sql_id;

else

open vc for v_query using v_hash;

end if;

fetch vc into v_sql_id, v_query,v_hash,v_addr,v_mem,v_parses,v_execs;

if vc%notfound then

return; /* Sorry, really is not in the library cache. */

end if;

close vc;

debugme('Header');

v_colname.delete;

v_Ycnt.delete;

v_count:=-1;

v_no:=0;

v_all_no:=-1;

PIPE ROW('==================================================================');

PIPE ROW('Addr: '||v_addr||' Hash_Value: '||v_hash||' SQL_ID '||v_sql_id);

PIPE ROW('Sharable_Mem: '||v_mem||' bytes Parses: '||v_parses||' Execs:'||v_execs);

PIPE ROW('Stmt: ');

for i in 0 .. trunc(length(v_query)/64)+1 loop

debugme('Print query line '||i);

PIPE ROW(i||' '||substr(v_query,1+i*64,64));

end loop;

debugme('Fetch SQL_SHARED_CURSOR columns');

SELECT COLUMN_NAME,0 bulk collect into v_colname,v_Ycnt

from cols

where table_name='SQL_SHARED_CURSOR'

and CHAR_LENGTH=1

order by column_id;

v_query:='';

debugme('Build Select List');

for i in 1 .. v_colname.count loop

v_query:= v_query ||','|| v_colname(i);

end loop;

debugme('Build Where');

if v_version like '9%' then

v_sql_where:=' WHERE ADDRESS=HEXTORAW('''||V_ADDR||''')';

v_sql_where2:=' WHERE KGLHDPAR=HEXTORAW('''||V_ADDR||''')';

elsif v_sql_id is not null then

v_sql_where:=' WHERE SQL_ID='''||v_sql_id||'''';

v_sql_where2:=v_sql_where;

else

v_sql_where:=' WHERE ADDRESS=HEXTORAW('''||V_ADDR||''')';

v_sql_where2:=v_sql_where;

end if;

debugme('Build Query');

v_query:= 'SELECT '||substr(v_query,2) || ' FROM SQL_SHARED_CURSOR ';

v_query:=v_query||v_sql_where2;

debugme(substr(v_sql_where2,-80));

debugme('Open Query');

begin

theCursor := dbms_sql.open_cursor;

sys.dbms_sys_sql.parse_as_user(C=>theCursor,STATEMENT=> v_Query, LANGUAGE_FLAG=>dbms_sql.native ,USERID=>0);

for i in 1 .. v_colname.count loop

dbms_sql.define_column( theCursor, i, columnValue, 8000 );

end loop;

status := dbms_sql.execute(theCursor);

debugme('Initiate Fetch');

while (dbms_sql.fetch_rows(theCursor) >0) loop

v_no:=0;

v_count:=v_count+1;

debugme('Fetch row '||v_count);

for i in 1..v_colname.count loop

dbms_sql.column_value(theCursor, i, columnValue);

-- debugme('Decode row '||v_count||' column '||i);

if columnValue='Y' then

v_Ycnt(i):=v_Ycnt(i)+1;

else

v_no:=v_no+1;

end if;

end loop;

if v_no=v_colname.count then

v_all_no:=v_all_no+1;

end if;

end loop;

dbms_sql.close_cursor(theCursor);

end;

debugme('Version summary');

PIPE ROW('');

PIPE ROW('Versions Summary');

PIPE ROW('----------------');

for i in 1 .. v_colname.count loop

if v_Ycnt(i)>0 then

PIPE ROW(v_colname(i)||' :'||v_Ycnt(i));

end if;

end loop;

If v_all_no>1 then

PIPE ROW('Versions with ALL Columns as "N" :'||v_all_no);

end if;

PIPE ROW(' ');

PIPE ROW('Total Versions:'||v_count);

PIPE ROW(' ');

PIPE ROW('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ');

V_value:=NULL;

v_query:='select max(SYSTEM_VALUE) into :v_value from h$parameter where name=''cursor_sharing'' ';

execute immediate v_query into v_value;

if v_value is not null then

PIPE ROW('cursor_sharing = '||v_value);

end if;

V_NO:=NULL;

v_query:='select max(SYSTEM_VALUE) into :v_no from h$parameter where name=''_cursor_obsolete_threshold'' ';

execute immediate v_query into v_no;

if v_no is not null then

PIPE ROW('_cursor_obsolete_threshold = '||v_no||' (See Note:10187168.8)');

end if;

PIPE ROW('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ');

PIPE ROW(' ');

v_phv.delete;

v_phvc.delete;

debugme('PHV');

v_query:='select plan_hash_value,count(*) from v$sql '||v_sql_where||' group by plan_hash_value';

execute immediate v_query bulk collect into v_phv,v_phvc;

PIPE ROW('Plan Hash Value Summary');

PIPE ROW('-----------------------');

PIPE ROW('Plan Hash Value Count');

PIPE ROW('=============== =====');

for i in 1 .. v_phv.count loop

PIPE ROW(to_char(v_phv(i),'99999999999999')||' '||to_char(v_phvc(i)));

end loop;

PIPE ROW(' ');

for i in 1 .. v_colname.count loop

debugme('Diag for '||v_colname(i)||' Ycnt:'||v_Ycnt(i));

if v_Ycnt(i)>0 then

PIPE ROW('~~~~~~~~~~~~~~'||rpad('~',length(v_colname(i)),'~'));

PIPE ROW('Details for '||v_colname(i)||' :');

PIPE ROW('');

if ( v_colname(i) like '%BIND%')

or (v_version like '11.1%' and v_colname(i)='ROW_LEVEL_SEC_MISMATCH') then

if v_bind_dumped=true then -- Dump only once

PIPE ROW('Details shown already.');

else

v_bind_dumped:=true;

if v_version like '9%' then

PIPE ROW('No details for '||v_version);

else

PIPE ROW('Consolidated details for BIND* columns:');

PIPE ROW('BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and');

PIPE ROW('BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)');

PIPE ROW('');

declare

v_position num_arr;

v_maxlen num_arr;

v_minlen num_arr;

v_dtype num_arr;

v_prec num_arr;

v_scale num_arr;

v_n num_arr;

v_bg char(3);

v_bgflag char(1):='N';

begin

v_query:='select position,min(max_length),max(max_length),datatype,precision,scale,count(*) n'

||' from v$sql_bind_capture where sql_id=:v_sql_id'

||' group by sql_id,position,datatype,precision,scale'

||' order by sql_id,position,datatype,precision,scale';

EXECUTE IMMEDIATE v_query

bulk collect into v_position, v_minlen, v_maxlen , v_dtype ,v_prec ,v_scale , v_n

using v_sql_id;

PIPE ROW('from v$sql_bind_capture');

PIPE ROW('COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)');

PIPE ROW('======== ======== =============== =============== ======== =============== =================');

for c in 1 .. v_position.count loop

v_bg:=(case when v_maxlen(c)>v_minlen(c) then 'Yes' else 'No' end);

v_bgflag:=(case when v_bg='Yes' and v_n(c)>99 then 'Y' end);

PIPE ROW( to_char(v_n(c),'9999999')||' '||to_char(v_position(c),'9999999')||' '|| to_char(v_minlen(c),'99999999999999')

||' '|| to_char(v_maxlen(c),'99999999999999')

||' '|| to_char(v_dtype(c),'9999999')

||' '||v_bg

||' ('|| v_prec(c)||','||v_scale(c)||')'

);

end loop;

if ( v_bgflag='Y' ) then

PIPE ROW ('The above table shows binds with 100+ versions in the same position with different MAX_LENGTHs.');

PIPE ROW ('See "Add event to improve cursor sharability on BIND_LENGTH_UPGRADEABLE" ');

end if;

if v_version not like '10%' and v_version not like '9%' then

v_query:='select sum(decode(IS_OBSOLETE,''Y'', 1, 0)),sum(decode(IS_BIND_SENSITIVE ,''Y'',1, 0))'

||',sum(decode(IS_BIND_AWARE,''Y'',1,0)),sum(decode(IS_SHAREABLE,''Y'',1,0))'

||' from v$sql where sql_id = :v_sql_id';

EXECUTE IMMEDIATE v_query

bulk collect into v_position, v_minlen, v_maxlen , v_dtype

using v_sql_id;

PIPE ROW('');

PIPE ROW('SUM(DECODE(column,Y, 1, 0) FROM V$SQL');

PIPE ROW('IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE');

PIPE ROW('=========== ================= ============= ============');

for c in 1 .. v_position.count loop

PIPE ROW(to_char(v_position(c),'9999999999')||' '|| to_char(v_minlen(c),'9999999999999999')

||' '|| to_char(v_maxlen(c),'999999999999')

||' '|| to_char(v_dtype(c),'99999999999'));

end loop;

end if;

end;

end if;

elsif v_colname(i) ='OPTIMIZER_MODE_MISMATCH' then

for c in (select OPTIMIZER_MODE,count(*) n from v$sql where hash_value=v_hash group by OPTIMIZER_MODE) loop

PIPE ROW(c.n||' versions with '||c.OPTIMIZER_MODE);

end loop;

elsif v_colname(i) ='OPTIMIZER_MISMATCH' then

if v_version like '9%' then

PIPE ROW('No details available for '||v_version);

else

declare

v_param vc_arr;

v_value vc_arr;

v_n num_arr;

begin

v_query:='select o.NAME,o.VALUE ,count(*) n '

||'from V$SQL_OPTIMIZER_ENV o,sql_shared_cursor s '

||'where ISDEFAULT=''NO'' '

||' and OPTIMIZER_MISMATCH=''Y'' '

||' and s.sql_id=:v_sql_id '

||' and o.sql_id=s.sql_id '

||' and o.CHILD_ADDRESS=s.CHILD_ADDRESS '

||' group by o.NAME,o.VALUE ';

EXECUTE IMMEDIATE v_query

bulk collect into v_param,v_value,v_n using v_sql_id ;

for c in 1 .. v_n.count loop

PIPE ROW(v_n(c)||' versions with '||v_param(c)||' = '||v_value(c));

end loop;

end;

end if;

elsif v_colname(i) ='AUTH_CHECK_MISMATCH' then

declare

v_pusr num_arr;

v_pschid num_arr;

v_pschname vc_arr;

v_n num_arr;

begin

if v_version like '9%' then

v_query:='select PARSING_USER_ID, PARSING_SCHEMA_ID, ''n/a'' ,count(*) n from v$sql '

||v_sql_where

||' group by PARSING_USER_ID, PARSING_SCHEMA_ID,''n/a''';

else

v_query:='select PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME ,count(*) n from v$sql '

||v_sql_where

||' group by PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME';

end if;

EXECUTE IMMEDIATE v_query

bulk collect into v_pusr,v_pschid,v_pschname,v_n;

PIPE ROW(' # of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME');

PIPE ROW('========== =============== ================= ===================');

for c in 1 .. v_n.count loop

PIPE ROW(to_char(v_n(c),'999999999')|| TO_CHAR(v_pusr(c),'9999999999999999')|| to_char(v_pschid(c),'99999999999999999')||' '||v_pschname(c));

end loop;

end;

elsif v_colname(i) = 'TRANSLATION_MISMATCH' then

declare

v_objn num_arr;

v_objow vc_arr;

v_objnm vc_arr;

begin

v_query:='select distinct p.OBJECT#,p.OBJECT_OWNER,p.OBJECT_NAME'

||' from (select OBJECT_NAME ,count(distinct object#) n from v$sql_plan '

||v_sql_where

||' and object_name is not null group by OBJECT_NAME ) d'

||' ,v$sql_plan p where d.object_name=p.object_name and d.n>1';

EXECUTE IMMEDIATE v_query

bulk collect into v_objn,v_objow,v_objnm;

If v_objn.count>0 then

PIPE ROW('Summary of objects probably causing TRANSLATION_MISMATCH');

PIPE ROW(' ');

PIPE ROW(' Object# Owner.Object_Name');

PIPE ROW('============ =================');

for c in 1 .. v_objn.count loop

PIPE ROW(to_char(v_objn(c),'99999999999')||' '||v_objow(c)||'.'||v_objnm(c));

end loop;

else

PIPE ROW('No objects in the plans with same name and different owner were found.');

end if;

end;

else

PIPE ROW('No details available');

end if;

end loop;

debugme('cursortrace');

IF v_version not like '9%' then

PIPE ROW('####');

PIPE ROW('To further debug Ask Oracle Support for the appropiate level LLL.');

if v_version in ('10.2.0.1.0','10.2.0.2.0','10.2.0.3.0') THEN

PIPE ROW('and read note:457225.1 Cannot turn off Trace after setting CURSORTRACE EVENT');

end if;

PIPE ROW('alter session set events ');

PIPE ROW(' ''immediate trace name cursortrace address '||v_hash||', level LLL'';');

PIPE ROW('To turn it off do use address 1, level 2147483648');

end if;

PIPE ROW('================================================================');

debugme('End of version_rpt');

return;

exception

when others then

PIPE ROW('Error :'||sqlerrm);

PIPE ROW('for Addr: '||v_addr||' Hash_Value: '||v_hash||' SQL_ID '||v_sql_id);

for i in 0 .. trunc(length(v_query)/64) loop

PIPE ROW(i||' '||substr(v_query,1+i*64,64));

end loop;

return;

end;

rem select b.* from v$sqlarea a ,table(version_rpt(a.sql_id,null,'Y')) b where loaded_versions >=100;

rem select * from table(debug_version_rpt);

参考

●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)

打赏赞(1)

标签： Oracle 原创总结版本数高(High Version Count)library cache lock cursor: pin S wait on X version_rpt 解析失败 library cache: mutex X 游标共享游标失效 cursor: pin S

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2024年 4月
一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

嘿，我是小麦，需要帮助随时找我哦。

18509239930
个人微信
DB宝
个人邮箱
点击加入QQ群
个人微店
回到顶部

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

相关等待事件介绍

1、library cache lock

2、library cache: mutex X

3、enq: TX - row lock contention

4、cursor: mutex S和cursor: mutex X

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

cursor: pin S wait on X

cursor: pin S

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

游标解析失败

游标失效（INVALIDATE）介绍

其它分析

AWR报告分析

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

library cache: mutex X示例

SQL AREA BUILD

Systemstate dump示例SQL AREA BUILD

CURSOR_SHARING=SIMILAR参数说明

等待事件解决方案

相关SQL

version_rpt3_25.sql脚本内容

相关案例

参考

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

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

相关等待事件介绍

1、library cache lock

2、library cache: mutex X

3、enq: TX - row lock contention

4、cursor: mutex S和cursor: mutex X

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

cursor: pin S wait on X

cursor: pin S

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

游标解析失败

游标失效（INVALIDATE）介绍

其它分析

AWR报告分析

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

library cache: mutex X示例

SQL AREA BUILD

Systemstate dump示例SQL AREA BUILD

CURSOR_SHARING=SIMILAR参数说明

等待事件解决方案

相关SQL

version_rpt3_25.sql脚本内容

相关案例

参考

相关文章

您可能还喜欢...

Oracle中的行转列

Oracle 如何查看和下载BLOB类型的数据

PostgreSQL培训认证证书查询网址

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复