ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 由 bind_mismatch 引起的 大量 version_count 问题

由 bind_mismatch 引起的 大量 version_count 问题

原创 Linux操作系统 作者:DataKW 时间:2013-06-24 18:34:14 0 删除 编辑

由 bind_mismatch 引起的 大量 version_count 问题

    http://blog.csdn.net/tianlesoftware/article/details/6566658   

 

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 中保存了SQLcursor,当有大量的version_count,说明虽然SQL 语句相同,但是Oracle 发现因为某些原因不可重用这些SQL。当这类SQL执行次数很多,就会占用大量的shared pool,引起library cache pinlibrary 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 , 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

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

         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

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的数量。 为了减少cursorlibrary cache的占用,还是先将cursor_shring 参数改成了默认的exact模式。 这样version_count 会减少很多,但是硬解析的次数也会增加,可能会增加Library Cache Latch等待 现在只能这样修改一下,在找个环境测试一下。

 

Oracle cursor_sharing 参数 详解

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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27573546/viewspace-764727/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-08-12

  • 博文量
    132
  • 访问量
    298633