合 【MOS】High Version Count Due To BIND_MISMATCH (Doc ID 336268.1) -- 10503 events事件介绍
Tags： OracleeventsBIND_MISMATCH10503 events
介绍
修复bug 2450264引入了一个新事件（10503），该事件允许用户指定字符绑定缓冲区长度。根据所使用的长度，子游标中的字符绑定可以全部使用相同的绑定长度创建。
对于绑定变量,ORACLE根据变量长度进行了分级，对于VARCHAR2类型共有如下4级：
第一级： 1-32
第二级： 33-128
第三级： 129-2000
第四级： 2000+
Oracle在进行bind graduation（绑定变量分级）的时候，使用的是绑定变量的声明类型长度。对于定义的变量在同一级可以共享游标，否则会生成子游标。
注意：
1、若是2个节点，则必须同时配置生效，否则可能会触发 ORA-600: [kxspoac : EXL 1] Occuring During Query If Event 10503 Is Set (Doc ID 810194.1)
2、该事件建议在session级别配置，否则可能会导致内存空间不足等问题，若在system级别配置，相当于所有的绑定变量都用2000字节的buffer，这会导致内存分配问题，另外，用完及时关闭。
3、根据 Bug 10274265 - Event 10503 does not work at session level (Doc ID 10274265.8)，若数据库大于Versions >= 10.2 且 BELOW 12.1，则在session级别配置该事件并不起作用。
测试示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | drop table TESTBIND1; create table TESTBIND1 ( aaa VARCHAR2(4000), bbb VARCHAR2(4000), ccc VARCHAR2(4000) ); --会话设置10503事件 ALTER session SET EVENTS '10503 trace name context forever, level 4000'; --执行插入 DECLARE v_AAA VARCHAR2(3000):='A'; v_BBB VARCHAR2(10):='B'; v_CCC VARCHAR2(10):='C'; BEGIN INSERT INTO TESTBIND1 VALUES(v_AAA,v_BBB,v_CCC); COMMIT; END; / --关闭10503事件 ALTER session SET EVENTS '10503 trace name context off'; select * from v$sql a where a.sql_text like '%INSERT INTO TESTBIND1 VALUES%' ; select * from v$sql_bind_capture a where a.sql_id='1fy16h4gd607u'; |
通过上述的SQL代码进行测试，可以看到会话设置了10503事件之后，即使客户端声明的字符变量长度小于4000，语句变量传入数据库时，绑定变量的长度会被固定为4000。
但，如果会话是在SYSTEM级别配置的，那么其它会话的绑定变量的长度也会被分配2000或4000字节，这无疑会增加内存空间的使用量。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | -- 会话1 ALTER system SET EVENTS '10503 trace name context forever, level 4000'; -- 会话2 DROP TABLE T_BG_20240414_LHR; CREATE TABLE T_BG_20240414_LHR(N NUMBER(10),V VARCHAR2(100)); DECLARE N NUMBER(10) :=1;--分配22字节的内存空间 V VARCHAR2(32) :='XIAOMAIMIAO1';--分配32字节的内存空间 BEGIN EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240414_LHR VALUES(:N,:V)' USING N, V; COMMIT; END; / select * from v$sql a where a.sql_text like '%INSERT INTO T_BG_20240414_LHR VALUES%' ; select * from v$sql_bind_capture a where a.sql_id='ch97gwybfg16g'; -- 会话3 drop table TESTBIND2; create table TESTBIND2 ( aaa VARCHAR2(4000), bbb VARCHAR2(4000), ccc VARCHAR2(4000) ); --执行插入 DECLARE v_AAA VARCHAR2(3000):='A'; v_BBB VARCHAR2(10):='B'; v_CCC VARCHAR2(10):='C'; BEGIN INSERT INTO TESTBIND2 VALUES(v_AAA,v_BBB,v_CCC); COMMIT; END; / select * from v$sql a where a.sql_text like '%INSERT INTO TESTBIND2 VALUES%' ; select * from v$sql_bind_capture a where a.sql_id='1fy16h4gd607u'; SELECT B.CHILD_NUMBER,B.CHILD_ADDRESS,D.BIND_NAME,D.POSITION,D.DATATYPE,D.MAX_LENGTH FROM V$SQL_BIND_METADATA D,V$SQL B WHERE D.ADDRESS=B.CHILD_ADDRESS AND B.SQL_ID='1fy16h4gd607u' ORDER BY B.CHILD_NUMBER,D.POSITION; |
会话2：
会话3：
High Version Count Due To BIND_MISMATCH (Doc ID 336268.1)
APPLIES TO:
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 8.1.7.4
SYMPTOMS
Performance is bad due to high version count. V$SQL_SHARED_CURSOR shows new child cursors for the parent one due to BIND_MISMATCH.
CAUSE
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:
Note: 2450264.8 Add event to improve cursor sharability
SOLUTION
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;
1 2 3 4 | ALTER system SET EVENTS '10503 trace name context forever, level 4000';, level <buffer length>'; Eg: ALTER system SET EVENTS '10503 trace name context forever, level 4000'; |
