现象
问题SQL性能变差、执行时间变长、并有大量library cache lock/cussor:mutex S等待事件，问题SQL的version count非常高。
问题分析
version count高是直接原因，导致了SQL性能变差，以及library cache lock/cussor:mutex S等待事件的产生。而产生 high version count高的原因是设置的变量varchar2类型的长度同实际赋值长度不同导致变量需要升级，产生新的子游标。
varchar2变量在以下不同范围是会产生 bind buffer mismatch而产生新的子游标的，字符串长度从1~32、33-128、129-2000、2001-4000是4个不同的变量长度范围。
测试
创建测试表：
create table test_bind (x varchar2(4000));
设置不同变量值范围，进行插入测试：
变量定义长度在1~32
variable v_x varchar2(10);
exec :v_x:=‘a’;
insert into test_bind (x) values (:v_x);
commit;
更改变量定义长度在33~128
variable v_x varchar2(33);
exec :v_x:=‘a’;
insert into test_bind (x) values (:v_x);
commit;
更改变量定义长度在129~2000
variable v_x varchar2(130);
exec :v_x:=‘a’;
insert into test_bind (x) values (:v_x);
commit;
更改变量定义长度在2001~4000
variable v_x varchar2(2100);
exec :v_x:=‘a’;
insert into test_bind (x) values (:v_x);
commit;
查看子游标数量：产生了4个子游标
HR@orcl>select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations,last_load_time from v$sql where sql_text ='insert into test_bind (x) values (:v_x)';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS LAST_LOAD_TIME
------------------------------ ------------- ------------ ---------- ----------- ---------- ------------- --------------------
insert into test_bind (x) valu 92nxkpaj5v3r1 0 1 1 2 1 2021-02-20/10:50:06
es (:v_x)
insert into test_bind (x) valu 92nxkpaj5v3r1 1 1 1 1 0 2021-02-20/10:56:57
es (:v_x)
insert into test_bind (x) valu 92nxkpaj5v3r1 2 1 1 1 0 2021-02-20/10:57:07
es (:v_x)
insert into test_bind (x) valu 92nxkpaj5v3r1 3 1 0 1 0 2021-02-20/10:57:16
es (:v_x)
查看变量：变量的类型是VARCHAR2(32) 、VARCHAR2(128)、VARCHAR2(2000) 、VARCHAR2(4000) 4个
HR@orcl>select sql_id,child_number,name,position,datatype,datatype_string,max_length from v$sql_bind_capture where sql_id='92nxkpaj5v3r1';
SQL_ID CHILD_NUMBER NAME POSITION DATATYPE DATATYPE_STRING MAX_LENGTH
------------- ------------ --------------- ---------- ---------- -------------------- ----------
92nxkpaj5v3r1 3 :V_X 1 1 VARCHAR2(4000) 4000
92nxkpaj5v3r1 2 :V_X 1 1 VARCHAR2(2000) 2000
92nxkpaj5v3r1 1 :V_X 1 1 VARCHAR2(128) 128
92nxkpaj5v3r1 0 :V_X 1 1 VARCHAR2(32) 32
查看游标失效原因，如下子游标1、2、3 的BIND_LENGTH_UPGRADEABLE为Y表示变量长度升级导致
HR@orcl>select sql_id,child_number,bind_length_upgradeable from v$sql_shared_cursor where sql_id='92nxkpaj5v3r1';
SQL_ID CHILD_NUMBER BIND_LENGTH_UPGRADEABLE
------------- ------------ ------------------------------
92nxkpaj5v3r1 0 N
92nxkpaj5v3r1 1 Y
92nxkpaj5v3r1 2 Y
92nxkpaj5v3r1 3 Y
查看mos: High Version Count Due To BIND_MISMATCH (Doc ID 336268.1)
发现bug 2450264 ,10g之后版本已有10503事件修复。
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.
The previous one will be marked ‘dont use’.
These types of cursors are never re-used. As a result, they permanently occupy a slot in the child table which will result in increasing the version count.
This issue has been described in the following bug:2450264.8 Add event to improve cursor sharability。
The fix of the bug 2450264 has introduced a new event (10503) which enables users to specify a character bind buffer length. Depending on the length used, the character binds in the child cursor can all be created using the same bind length;
ALTER SESSION SET EVENTS '10503 trace name context forever, level
Eg:
ALTER SESSION SET EVENTS ‘10503 trace name context forever, level 4000’;
设置 10503事件，再次重复上述测试
ALTER SESSION SET EVENTS ‘10503 trace name context forever, level 4000’;
创建测试表 testbind：
create table testbind (y varchar2(4000));
设置不同变量值范围，进行插入测试：
变量定义长度在1~32
variable v_y varchar2(10);
exec :v_y:=‘a’;
insert into testbind (y) values (:v_y);
commit;
更改变量定义长度在33~128
variable v_y varchar2(33);
exec :v_y:=‘a’;
insert into testbind (y) values (:v_y);
commit;
更改变量定义长度在129~2000
variable v_y varchar2(130);
exec :v_y:=‘a’;
insert into testbind (y) values (:v_y);
commit;
更改变量定义长度在2001~4000
variable v_y varchar2(2100);
exec :v_y:=‘a’;
insert into testbind (y) values (:v_y);
commit;
查看子游标数量：已经只有一个子游标了
HR@orcl>select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations,last_load_time from v$sql where sql_text ='insert into testbind (y) values (:v_y)';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS LAST_LOAD_TIME
------------------------------ ------------- ------------ ---------- ----------- ---------- ------------- --------------------
insert into testbind (y) value 52ga073yqfrkp 0 4 3 2 1 2021-02-20/11:15:53
s (:v_y)
查看变量:可以发现不管声明varchar2变量的长度为多少时，后台都设置为了4000
HR@orcl>select sql_id,child_number,name,position,datatype,datatype_string,max_length from v$sql_bind_capture where sql_id='52ga073yqfrkp';
SQL_ID CHILD_NUMBER NAME POSITION DATATYPE DATATYPE_STRING MAX_LENGTH
------------- ------------ --------------- ---------- ---------- -------------------- ----------
52ga073yqfrkp 0 :V_Y 1 1 VARCHAR2(4000) 4000
