BIND_MISMATCH导致过多VERSION COUNT的问题-白红宇

BIND_MISMATCH导致过多VERSION COUNT的问题

阅读量：5897 次

发布时间：2019-06-19

本文共 5835 字，大约阅读时间需要 19 分钟。

并不是用了绑定变量就一定都会游标共享，下面我们介绍的就是一种例子。BIND_MISMATCH导致VERSION COUNT过多的原因解释：

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的时候，使用的是绑定变量的声明类型长度。对于定义的变量在同一级可以共享游标，否则会生成子游标，如下：

SQL> desc t

名称是否为空? 类型

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

X VARCHAR2(30)

SQL> variable v_x varchar2(32)

SQL> exec :v_x:='a';

PL/SQL 过程已成功完成。

SQL> select * from t where x=:v_x;

未选定行

SQL> select sql_id,child_number,executions from v$sql where sql_text='select * from t where x=:v_x';

SQL_ID CHILD_NUMBER EXECUTIONS

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

1pqg8dpwthcp3 1 1

SQL> variable v_x varchar2(33)

SQL> exec :v_x:='a';

PL/SQL 过程已成功完成。

SQL> select * from t where x=:v_x;

未选定行

SQL> select sql_id,child_number,executions from v$sql where sql_text='select * from t where x=:v_x';

SQL_ID CHILD_NUMBER EXECUTIONS

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

1pqg8dpwthcp3 1 1

1pqg8dpwthcp3 2 1

SQL> select child_number,bind_mismatch from v$sql_shared_cursor where sql_id='1pqg8dpwthcp3';

CHILD_NUMBER B

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

1 N

2 Y

SQL> variable v_x varchar2(129)

SQL> exec :v_x:='a';

PL/SQL 过程已成功完成。

SQL> select * from t where x=:v_x;

未选定行

SQL> select sql_id,child_number,executions from v$sql where sql_text='select * from t where x=:v_x';

SQL_ID CHILD_NUMBER EXECUTIONS

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

1pqg8dpwthcp3 1 1

1pqg8dpwthcp3 2 1

1pqg8dpwthcp3 3 1

SQL> select child_number,bind_mismatch from v$sql_shared_cursor where sql_id='1pqg8dpwthcp3';

CHILD_NUMBER B

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

1 N

2 Y

3 Y

SQL> variable v_x varchar2(2001)

SQL> exec :v_x:='a';

PL/SQL 过程已成功完成。

SQL> select * from t where x=:v_x;

未选定行

SQL> select sql_id,child_number,executions from v$sql where sql_text='select * from t where x=:v_x';

SQL_ID CHILD_NUMBER EXECUTIONS

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

1pqg8dpwthcp3 1 1

1pqg8dpwthcp3 2 1

1pqg8dpwthcp3 3 1

1pqg8dpwthcp3 4 1

SQL> select child_number,bind_mismatch from v$sql_shared_cursor where sql_id='1pqg8dpwthcp3';

CHILD_NUMBER B

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

1 N

2 Y

3 Y

4 Y

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

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

(1)查询绑定变量最大长度：假如最大长度为128

select max(max_length) from v$sql_bind_metadata where datatype=1;

(2)在参数文件中设置：

event="10503 trace name context forever, level 128"

或

SQL> alter system set event='10503 trace name context forever, level 128' scope=spfile;

(3)该参数在会话级别虽然能够成功设置，但是无效。

Bug 10274265 - Event 10503 does not work at session level [ID 10274265.8]SQL> select * from v$version;BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter system set events '10503 trace name context forever ,level 4096';

系统已更改。

SQL> variable c varchar2(32);

SQL> exec :c := 'zheng';

SQL> select id from edu.zhhtest where name= :c and rownum=1;

SQL> variable c varchar2(33);

SQL> exec :c := 'zheng';

SQL> select id from edu.zhhtest where name= :c and rownum=1;

SQL> select sql_id,child_number,executions from v$sql where sql_text='select id from edu.zhhtest where name= :c and rownum=1';

SQL_ID CHILD_NUMBER EXECUTIONS

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

grzn6d2ak22j4 0 2

SQL> select s.child_number, m.position, m.max_length,

decode(m.datatype,1,'varchar2',2,'number',m.datatype) as datatype

3 from v$sql s, v$sql_bind_metadata m

4 where s.sql_id = 'grzn6d2ak22j4'

5 and s.child_address = m.address

6 order by 1, 2;

CHILD_NUMBER POSITION MAX_LENGTH DATATYPE

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

0 1 4000 varchar2

Bind Graduation的目的是什么?可能原因有如下两个：

bind peeking缓解，提供多次peeking机会

从效果来看，Oracle bind graduation会增加子游标的数量。如果单就bind peeking而言，在Oracle 11g的ACS（Adaptive Cursor Sharing）出现之前，

Oracle绑定变量使用的子游标数量是很少的。Bind graduation出现之后，我们最直观的感觉是child cursor增多，对应的执行计划增多。

原有的可能只用一个执行计划可以覆盖的绑定变量语句，可能要有多个执行计划才能覆盖。

对绑定变量语句而言，每次生成子游标，就意味着要进行一次hard parse，就意味着要进行一次peeking。生成与Peeking value对应的执行计划。

代码中对变量声明长度的不一致，直接意味着不同的程序模块和功能模块。Oracle也许认为这样出现bind peeking问题的几率较高。于是取巧采用变量声明的方式进行区分管理。

同时，划分区域又不是很多，从而限制了子游标出现的数量。多次peeking，形成多个子游标，配对更合适的执行计划。

2. 绑定变量存储

对执行计划而言，Oracle是需要单独分配内存空间给执行计划进行保存的。如果其中有使用绑定变量，Oracle是会将绑定变量保存在child cursor中的。

在分配varchar2类型的绑定变量大小空间时，使用bind graduation可以分配略小的适当空间。

虽然会存在bind graduation现象，但是我们说实现graduation的分区数量是有限的。也就是说，即使多次生成child cursor，带来version count过多的风险也是有限的。

如果要是很极端的情况，比如项目组希望实现绝对的共享或者说变量数目较多引起version count过多，可以使用10503事件控制bind graduation的出现，或者直接在代码中声明

varchar2(2000)的绑定变量即可。