由 bind_mismatch 引起的大量 version_count 问题

最新推荐文章于 2023-06-21 06:50:33 发布

iteye_4537

最新推荐文章于 2023-06-21 06:50:33 发布

阅读量94

点赞数

文章标签：数据库

本文探讨了Oracle数据库中SQL语句存在大量version_count的问题，分析了bind_mismatch的原因，并提供了检查和临时解决方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

从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

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

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