由 bind_mismatch 引起的 大量 version_count 问题

从AWR报告里发现一个SQL存在大量的version_count.

SYS@xezf(qs-xezf-db1)> select sql_id,version_count from v$sqlarea where version_count> 500 order by 2 desc ;

SQL_ID VERSION_COUNT

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

9rwd4wkwm4bsy 3046

cpqsn8zak6sw4 2985

66x4djqka2ppy 976

0z7n7sst85222 617

在v$sqlarea 中保存了SQL的cursor，当有大量的version_count,说明虽然SQL 语句相同，但是Oracle 发现因为某些原因不可重用这些SQL。当这类SQL执行次数很多,就会占用大量的shared pool,引起library cache pin和library cache 的等待事件。

可以使用如下SQL 查看占用内存大小：

/* Formatted on 2011/6/24 21:54:00 (QP5 v5.163.1008.3004) */

SELECT SUM (sharable_mem) / 1024 / 1024 || 'M'

FROM v$sqlarea

WHERE sql_id = 'cpqsn8zak6sw4';

可以通过如下SQL 查看是什么原因导致的不匹配：

SYS@xezf(qs-xezf-db1)> select sql_id,child_number,BIND_MISMATCH from v$sql_shared_cursor where sql_id='9rwd4wkwm4bsy' and BIND_MISMATCH='Y' and rownum<10;

SQL_ID CHILD_NUMBER B

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

9rwd4wkwm4bsy 3 Y

9rwd4wkwm4bsy 24 Y

9rwd4wkwm4bsy 29 Y

9rwd4wkwm4bsy 33 Y

9rwd4wkwm4bsy 35 Y

9rwd4wkwm4bsy 38 Y

9rwd4wkwm4bsy 51 Y

9rwd4wkwm4bsy 55 Y

9rwd4wkwm4bsy 81 Y

我这是过滤之后的信息，当这些信息有Y时，就是表示cursor 不能重用的原因。

SYS@xezf(qs-xezf-db1)> select count(*) from v$sql_shared_cursor where sql_id='9rwd4wkwm4bsy' and BIND_MISMATCH='Y' ;

COUNT(*)

----------

120

bind_mismatch一般是由于bind value的长度不同导致bind buffer无法重用，最终导致cursor无法重用。

例如： 对于字符类型的字段，进行绑定变量的时候，第一次会使用32字节的BUFFER，如果该值小于32字节的话，第二次执行这个SQL的时候，如果小于32字节，那么可以共享这个CURSOR,如果大于，就无法共享，原因就是BIND_MISMATCH，此时会产生一个子CURSOR，同时分配128字节的BIND BUFFER，以此类推。

正常情况不会产生这么大量的子CURSOR。但是由于一些BUG，会导致问题。

如果没有补丁，一个临时性的解决方案，设置一个较大的BUFFER：

SQL>ALTER SESSION SET EVENTS '10503 trace name context level <buffer length>, forever';

通过v$sql_bind_capture 视图查看一下每次绑定变量的值：

SYS@xezf(qs-xezf-db1)> select position,LAST_CAPTURED,datatype_string,value_string from v$sql_bind_capture where sql_id='9rwd4wkwm4bsy' and rownum<50;

POSITION LAST_CAPTURED DATATYPE_STRING VALUE_STRING

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

1 2011-06-24 15:54:22 VARCHAR2(32) cp102328

2 2011-06-24 15:54:22 NUMBER 103

3 2011-06-24 15:54:22 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 15:54:22 VARCHAR2(32) yyyy-mm-dd

1 2011-06-24 16:02:54 VARCHAR2(32) s13791223344

2 2011-06-24 16:02:54 NUMBER 103

3 2011-06-24 16:02:54 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 16:02:54 VARCHAR2(32) yyyy-mm-dd

1 2011-06-24 16:10:41 VARCHAR2(32) 7027976

2 2011-06-24 16:10:41 NUMBER 103

3 2011-06-24 16:10:41 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 16:10:41 VARCHAR2(32) yyyy-mm-dd

1 2011-06-24 17:09:28 VARCHAR2(32) BILLQQ

2 2011-06-24 17:09:28 NUMBER 103

3 2011-06-24 17:09:28 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 17:09:28 VARCHAR2(32) yyyy-mm-dd

1 2011-06-24 16:59:16 VARCHAR2(32) wantai1472888

2 2011-06-24 16:59:16 NUMBER 103

3 2011-06-24 16:59:16 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 16:59:16 VARCHAR2(32) yyyy-mm-dd

1 2011-06-24 16:59:10 varchar2(32) [e-mail]

2 2011-06-24 16:59:10 NUMBER 103

3 2011-06-24 16:59:10 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 16:59:10 VARCHAR2(32) yyyy-mm-dd

1 2011-06-24 16:59:09 VARCHAR2(32) 22501165422

2 2011-06-24 16:59:09 NUMBER 103

3 2011-06-24 16:59:09 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 16:59:09 VARCHAR2(32) yyyy-mm-dd

1 2011-06-24 16:59:07 VARCHAR2(32) 12801165830

2 2011-06-24 16:59:07 NUMBER 103

3 2011-06-24 16:59:07 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 16:59:07 VARCHAR2(32) yyyy-mm-dd

1 2011-06-24 16:59:00 VARCHAR2(32) 235896734

2 2011-06-24 16:59:00 NUMBER 103

3 2011-06-24 16:59:00 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 16:59:00 VARCHAR2(32) yyyy-mm-dd

1 2011-06-24 16:58:56 varchar2(32) 978a62e0bbb767d99bda

2 2011-06-24 16:58:56 NUMBER 103

3 2011-06-24 16:58:56 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 16:58:56 VARCHAR2(32) yyyy-mm-dd

1 2011-06-24 16:58:34 VARCHAR2(32) [e-mail]

2 2011-06-24 16:58:34 NUMBER 209

3 2011-06-24 16:58:34 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 16:58:34 VARCHAR2(32) yyyy-mm-dd

1 2011-06-24 16:57:51 varchar2(32) syyxQS20110624000364

2 2011-06-24 16:57:51 NUMBER 103

3 2011-06-24 16:57:51 VARCHAR2(32) yyyy-mm-dd

4 2011-06-24 16:57:51 VARCHAR2(32) yyyy-mm-dd

通过以上的查询结果，我们可以肯定是sql_id='9rwd4wkwm4bsy' SQL的第一绑定变量值的长度不同造成bind_mismatch, 从而产生大量的version_counts.

相关的bug信息如下：
Bug:9689310:
- Non sharability of cursors due to BIND_MISMATCH.

Bug:6981690:
- Non sharability of cursors due to PQ_SLAVE_MISMATCH

Bug:8981059:
- Non sharability of cursors due to USER_BIND_PEEK_MISMATCH.

对于Bug 9689310，在MOS上搜了一下，该bug存在的版本如下：

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 12.1
Versions confirmed as being affected	11.2.0.1 11.1.0.7 10.2.0.4
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

12.1 (Future Release) 11.2.0.2 (Server Patch Set) 11.2.0.1 Bundle Patch 8 for Exadata Database 11.1.0.7.7 Patch Set Update 11.1.0.7 Patch 31 on Windows Platforms

MOS 上给了一个变通的解决方法：Workaround

Alter the client application code so that it uses constant sizes for the MAX bind lengths.

我的库是10.2.0.5的，这个没说修复，也没说存在bug，还真不好确定，看来还是需要测试一下。

不过我这个库上的cursor_sharing 参数是设置为similar的，这样会将SQL 中的谓词值自动用变量来代替。 这样会增加cursor的数量。 为了减少cursor对library cache的占用，还是先将cursor_shring 参数改成了默认的exact模式。 这样version_count 会减少很多，但是硬解析的次数也会增加，可能会增加Library Cache Latch等待。 现在只能这样修改一下，在找个环境测试一下。

Oracle cursor_sharing 参数 详解

http://blog.csdn.net/tianlesoftware/archive/2011/06/17/6551723.aspx

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

Blog： http://blog.csdn.net/tianlesoftware

Email: [e-mail]

DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群： 83829929 DBA5群： 142216823

DBA6 群：158654907 聊天 群：40132017 聊天2群：69087192

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请