Oracle SQL语句子游标暴涨？这几招轻松搞定

在Oracle数据库中，Version Count通常指同一父游标 (Parent Cursor) 下的子游标 (Child Cursor) 数量。每个子游标对应不同的执行环境 (如绑定变量类型、优化器参数等)，高Version Count会导致共享池 (Shared Pool) 压力增大，进而影响数据库性能。

为什么会出现多个Version Count，会带来哪些影响呢？我们从基础的Oracle原理说起。

过多的子游标是怎么产生的

Oracle将解析过的SQL语句存在放在共享池，每条SQL解析前都会到共享池中检索是否已经存在，如果不存在会进行SQL语句的硬解析，生成相应的子游标保存在共享池；如果已经在共享池中存在，则会尝试使用当前已存在的游标来执行该条SQL，这个过程通常称之为软解析。当然在尝试重用之前，Oracle也会基于一系列的条件进行检测是否能够重用已经存在的子游标，如果不能重用则会生成新的子游标。这种情况下，我们能够在V$SQL视图中看到相同的SQ{L}_{I}D存在两条记录，对应的ChildNumber不同；而在V$SQLAREA视图中则会记录该条SQL的VERSION_COUNT字段值为2，这就是所谓的多个Version Count。

导致SQL语句多个子游标的因素非常多，举个简单的例子。大家都知道SQL_ID是基于SQL语句的文字值计算出来的，比如下面的SQL，计算出来是SQL_ID是f34thrbt8rjt5。如果在HR和SCOTT用户下都有一张employees表，分别在这两个用户下执行这条SQL时，生成的SQL_ID是相同的，但对应的employees表并不是同一张，这种情况下显然不能使用同一个子游标。因此Oracle会为这条SQL再生成一个子游标，从而使得这条SQL的Version Count变为2。

select * from employees;

事实上，并没有一个绝对的值来界定到底多少Version Count算是高。不过子游标数量大于20的SQL语句会记录到AWR的"SQL ordered by Version Count"章节中。如果你怀疑数据库遇到类似的问题，可以从这部分内容中找出候选的SQL以进一步的分析。此外还有一个视图 v$sql_shared_cursor 来标记和区分游标不能共享的原因，该视图中记录的各种原因多达60+种，较为常见的原因有：OPTIMIZER_MISMATCH、BIND_MISMATCH、BIND_EQUIV_FAILURE等。

Oracle共享池采用Latch锁进行并发管理，SQL解析时需要基于一定的算法拿到相应的Latch进行SQL命中的检索，当一个会话持有这个Latch时其他的会话如果也要访问相同的Latch，只能等待前面的会话释放。如果某个SQL的Version Count数量过多，必然导致检索的时间会增加。当相同SQL的执行频率比较高时，则会因为前面的会话没有及时释放Latch而导致会话排队，极端情况下会导致大量的会话拥堵，出现严重的性能问题。

为什么子游标会导致cursor: mutex X

近期有客户生产系统上遭遇了较为严重的cursor: mutex X等待，mutex是Oracle数据库中的轻量级锁，用于保护共享内存结构。当出现"cursor: mutex X"等待事件时，表示某个会话正在以独占模式 (Exclusive Mode) 持有游标相关的mutex，导致其他会话需要等待该mutex释放。

而在"SQL ordered by Version Count"中显示有部分SQL的Version Count高达4000+，是导致mutex争用的罪魁祸首。

Oracle的字符串绑定变量分为4个档次，分别是：

• 第一档，32 Bytes，存放小于32个字节的字符串；
• 第二档，128 Bytes，存放小于128个字节的字符串；
• 第三档，2000 Bytes，存放小于2000个字节的字符串；
• 第四档，4000 Bytes，存放小于4000个字节的字符串。

如果Oracle无法将当前值绑定到现有的子游标，会使用更高档位的绑定缓冲区升级现有的子游标，这将强制SQL语句进行硬解析，并创建一个新的子游标。因此在一个varchar类型列非常多的表上，如果插入数据的长度经常发生变化，就会因为各种不同的排列组合生成出非常多的子游标来。

方案总结

• 字符串变长导致的游标不能共享

而像Java等高级语言中，并没有严格限定字符串的长度，比较难从应用程序的角度控制类似问题的发生。为此在Bug 2450264 - Add event to improve cursor sharability on BIND_LENGTH_UPGRADEABLE中，Oracle引入了Event 10503，通过该事件可以将字符串设置为固定的长度，如2000，避免绑定变量带入的不同长度，导致多个子游标的出现。

alter system set evets '10503 trace name context forever, level 2000';

• 万能的"治标"方案

绑定变量mismatch导致的结果是子游标数量急剧增加，为了避免因为过多的子游标影响SQL解析效率带来其他的副作用，Oracle也提供了一个"治标"的隐含参数 – “_cursor_obsolete_threshold”，通过这个参数设定内存中保存的子游标数量。在短时间内不能找到确切的原因来解决问题之前，使用这个参数也不失为一种很好的临时解决方案。

alter system set "_cursor_obsolete_threshold"=1024 scope=both;

• 时间精度导致的游标不能共享

此外，时间精度的不同也会导致游标不能共享，v$sql_shared_cursor视图中的Reason列显示的原因是Bind mismatch(14)。导致这个问题的其中一个原因是，JDBC在发送带有纳秒时间精度的时间戳时，会将绑定变量设置为Null，而亚秒级的绑定变量则会设置为9。

reason=<ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind mismatch(14)
</reason><size>4x4</size><bind_position>2</bind_position>
<original_oacflg>3</original_oacflg>
<original_oacscl>0</original_oacscl><new_oacscl>9</new_oacscl></ChildNode>

该问题会发生在JDBC 11.2.0.3以上版本，建议打上相应的JDBC补丁。

大家在数据库运维过程中还遇到哪些问题呢，欢迎关注公众号“数据最前线”，留言讨论。