由 bind_mismatch 引起的 大量 version_count 问题

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

SYS@xezf(qs-xezf-db1)> select sql_id,version_count fromv$sqlareawhere version_count> 500 order by 2 desc ;

SQL_IDVERSION_COUNT

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

9rwd4wkwm4bsy3046

cpqsn8zak6sw42985

66x4djqka2ppy976

0z7n7sst85222617

在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) */

SELECTSUM(sharable_mem)/1024/1024||'M'

FROMv$sqlarea

WHEREsql_id='cpqsn8zak6sw4';

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

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

SQL_IDCHILD_NUMBER B

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

9rwd4wkwm4bsy3 Y

9rwd4wkwm4bsy24 Y

9rwd4wkwm4bsy29 Y

9rwd4wkwm4bsy33 Y

9rwd4wkwm4bsy35 Y

9rwd4wkwm4bsy38 Y

9rwd4wkwm4bsy51 Y

9rwd4wkwm4bsy55 Y

9rwd4wkwm4bsy81 Y

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

SYS@xezf(qs-xezf-db1)> select count(*) fromv$sql_shared_cursorwhere sql_id='9rwd4wkwm4bsy' andBIND_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 wheresql_id='9rwd4wkwm4bsy'and rownum<50;

POSITIONLAST_CAPTUREDDATATYPE_STRINGVALUE_STRING

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

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

2 2011-06-24 15:54:22 NUMBER103

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 NUMBER103

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 NUMBER103

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 NUMBER103

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 NUMBER103

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)gy928888@vip.qq.com

2 2011-06-24 16:59:10 NUMBER103

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 NUMBER103

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 NUMBER103

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 NUMBER103

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 NUMBER103

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)708888718@qq.com

2 2011-06-24 16:58:34 NUMBER209

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 NUMBER103

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 toBIND_MISMATCH.

Bug:6981690:
- Non sharability of cursors due toPQ_SLAVE_MISMATCH

Bug:8981059:
- Non sharability of cursors due toUSER_BIND_PEEK_MISMATCH.

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

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versionsbelievedto 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.youkuaiyun.com/xujinyang/article/details/6855009

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