Welcome 微信登录

首页 / 数据库 / MySQL / 由 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        3046cpqsn8zak6sw4          298566x4djqka2ppy          9760z7n7sst85222           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 Y9rwd4wkwm4bsy           24 Y9rwd4wkwm4bsy           29 Y9rwd4wkwm4bsy           33 Y9rwd4wkwm4bsy           35 Y9rwd4wkwm4bsy           38 Y9rwd4wkwm4bsy           51 Y9rwd4wkwm4bsy           55 Y9rwd4wkwm4bsy           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）         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 affectedVersions BELOW 12.1
Versions confirmed as being affected
  • 11.2.0.1
  • 11.1.0.7
  • 10.2.0.4
Platforms affectedGeneric （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等待。 现在只能这样修改一下，在找个环境测试一下。 Shared Pool 原理Oracle cursor_sharing 参数详解相关资讯      Oracle教程 
  • Oracle中纯数字的varchar2类型和  （07/29/2015 07:20:43）
  • Oracle教程：Oracle中查看DBLink密  （07/29/2015 07:16:55）
  • [Oracle] SQL*Loader 详细使用教程  （08/11/2013 21:30:36）
  • Oracle教程：Oracle中kill死锁进程  （07/29/2015 07:18:28）
  • Oracle教程：ORA-25153 临时表空间  （07/29/2015 07:13:37）
  • Oracle教程之管理安全和资源  （04/08/2013 11:39:32）
本文评论 查看全部评论 （0）
表情： 姓名： 字数