BIND_MISMATCH导致过多VERSION COUNT的问题分析及处理方法

1. 概述

This is due to the bind buffer mismatch of the current child cursor. If oracle is unable to bind the current value to the existing child cursors bind buffer,

Oracle upgrades the existing child cursor with a high bind buffer. This will force the query to do a hard parse and a new child cursor will be created.

对于绑定变量,ORACLE根据变量长度进行了分级，对于VARCHAR2类型共有如下4级：

第一级： 1-32
第二级： 33-128
第三级： 129-2000
第四级： 2000+

Oracle在进行bind graduation的时候，使用的是绑定变量的声明类型长度。对于定义的变量在同一级可以共享游标，否则会生成子游标

2. 开始测试

variable v_x varchar2(12);
exec :v_x:='SMITH';

PL/SQL procedure successfully completed
v_x
---------
SMITH

select * from emp1 e where e.ename=:v_x;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TOP_STATUS
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7369 SMITH CLERK 7902 1980/12/17 800.00 20 0
v_x
---------
SMITH

select sql_id,child_number,executions from v$sql where sql_text='select * from emp1 e where e.ename=:v_x';

SQL_ID        CHILD_NUMBER EXECUTIONS
-------------     ------------     ----------

SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
59pf9402hqanw 0 1

**注释： 增加变量类型的字节长度*

variable v_x varchar2(128);
exec :v_x:='SMITH';

**注释：这里执行了2次*

select * from emp1 e where e.ename=:v_x;
select sql_id,child_number,executions from v$sql where sql_text='select * from emp1 e where e.ename=:v_x';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
59pf9402hqanw 0 1
59pf9402hqanw 1 2 --> 发生硬解析，生成新的游标

**注释： 再次增加变量类型的字节长度*

variable v_x varchar2(129);
exec :v_x:='SMITH';

select * from emp1 e where e.ename=:v_x;

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- -----------------------------
SAL COMM DEPTNO TOP_STATUS
---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-1980 00:00:00
800 20 0


SQL> select sql_id,child_number,executions from v$sql where sql_text='select * from emp1 e where e.ename=:v_x';

SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
59pf9402hqanw 0 1
59pf9402hqanw 1 2
59pf9402hqanw 2 1 --> 发生硬解析，生成新的游标

select s.SQL_ID,

s.CHILD_NUMBER,
s.BIND_MISMATCH,
s.BIND_LENGTH_UPGRADEABLE,
s.REASON
from v$sql_shared_cursor s
where sql_id = '59pf9402hqanw';

SQL_ID CHILD_NUMBER BIND_MISMATCH BIND_LENGTH_UPGRADEABLE REASON
1 59pf9402hqanw 0 N N <CLOB>
2 59pf9402hqanw 1 N Y <CLOB>
3 59pf9402hqanw 2 N Y <CLOB>

<ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind mismatch(22)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>3</original_oacflg>
<original_oacmxl>32</original_oacmxl><upgradeable_new_oacmxl>128</upgradeable_new_oacmxl></ChildNode>

<ChildNode><ChildNumber>1</ChildNumber><ID>40</ID><reason>Bind mismatch(22)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>3</original_oacflg>
<original_oacmxl>128</original_oacmxl><upgradeable_new_oacmxl>32</upgradeable_new_oacmxl></ChildNode>

3. 处理办法

具体可以参考：High Version Count Due To BIND_MISMATCH [ID 336268.1] 

ORACLE文档说可以通过设置10503事件来搞定这个问题 , 10503 => enable user-specified graduated bind lengths

开启/关闭 10503 event的命令：

alter session set events '10503 trace name context forever, level 2000';

alter system set events '10503 trace name context forever ,level 2000'; --动态设置不需要重启实例
alter system set events '10503 trace name context off';

**注释： 这里查询绑定变量最大长度：假如最大长度为2000*

variable v_x varchar2(12);
exec :v_x:='SMITH';
PL/SQL procedure successfully completed
v_x
---------
SMITH
select * from emp1 e where e.ename=:v_x;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TOP_STATUS
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7369 SMITH CLERK 7902 1980/12/17 800.00 20 0
v_x
---------
SMITH
select sql_id,child_number,executions from v$sql where sql_text='select * from emp1 e where e.ename=:v_x';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
59pf9402hqanw 0 1

**注释： 增加字段长度，但不超过10503 设置的level*

variable v_x varchar2(128);
exec :v_x:='SMITH';

**注释：这里执行了1次*

select * from emp1 e where e.ename=:v_x;
select sql_id,child_number,executions from v$sql where sql_text='select * from emp1 e where e.ename=:v_x';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
59pf9402hqanw 0 2

**注释：这里游标可以共享了*