由 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

可以使用如下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 , 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) gy928888@vip.qq.com

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

由 bind_mismatch 引起的 大量 version_count 问题(一)

由 bind_mismatch 引起的大量 version_count 问题(一)