BIND_MISMATCH导致过多VERSION COUNT的问题

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

转载最新推荐文章于 2023-06-21 06:50:33 发布 · 690 阅读

文章标签：

本文解析了 Oracle 数据库中的 Bind Graduation 机制，详细介绍了绑定变量如何影响游标的共享及硬解析次数，并探讨了解决 BIND_MISMATCH 问题的方法。

并不是用了绑定变量就一定都会游标共享，下面我们介绍的就是一种例子。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的目的是什么?可能原因有如下两个：