bind变量的长度不同导致多个chlid cursor

来源：互联网发布：贵阳IBM预测大数据编辑：程序博客网时间：2024/05/01 18:07

bind变量的长度不同导致多个chlid cursor
测试如下：
SQL> create table test (a varchar2(4000));

表已创建。

SQL> declare
2 instring varchar2(4000);
3 begin
4 for i in 1..2001 loop
5 instring := rpad('X',i,'X');
6 insert /*+ bind */ into test values (instring);
7 end loop;
8 end;
9 /

PL/SQL 过程已成功完成。

SQL> commit;

查看sql的版本：sql有4个子游标
SQL> select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_text like '%bind%' and sql_text like 'INSERT%';

SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
-------------------------------------------------------------------------------- ------------- ------------ ---------- ----------- ---------- -------------
INSERT /*+ bind */ INTO TEST VALUES (:B1 ) dthsy6z1ycrhw 0 32 1 1 0
INSERT /*+ bind */ INTO TEST VALUES (:B1 ) dthsy6z1ycrhw 1 96 0 1 0
INSERT /*+ bind */ INTO TEST VALUES (:B1 ) dthsy6z1ycrhw 2 1872 0 1 0
INSERT /*+ bind */ INTO TEST VALUES (:B1 ) dthsy6z1ycrhw 3 1 0 1 0

SQL>

SQL> select s.sql_id,s.child_number,s.sharable_mem,s.executions,s.invalidations from v$sql s where s.sql_id='dthsy6z1ycrhw';

SQL_ID CHILD_NUMBER SHARABLE_MEM EXECUTIONS INVALIDATIONS
------------- ------------ ------------ ---------- -------------
dthsy6z1ycrhw 0 14667 32 0
dthsy6z1ycrhw 1 14667 96 0
dthsy6z1ycrhw 2 14667 1872 0
dthsy6z1ycrhw 3 14667 1 0
SQL>

查看共享游标：是由于绑定变量的长度导致的多个子游标BIND_LENGTH_UPGRADEABLE
SQL> select sql_id,child_number,bind_length_upgradeable from v$sql_shared_cursor where sql_id='dthsy6z1ycrhw';

SQL_ID CHILD_NUMBER BIND_LENGTH_UPGRADEABLE
------------- ------------ -----------------------
dthsy6z1ycrhw 0 N
dthsy6z1ycrhw 1 Y
dthsy6z1ycrhw 2 Y
dthsy6z1ycrhw 3 Y

查看变量的绑定情况：
SQL> select sql_id,child_number,position,datatype,datatype_string,max_length from v$sql_bind_capture where sql_id='dthsy6z1ycrhw';

SQL_ID CHILD_NUMBER POSITION DATATYPE DATATYPE_STRING MAX_LENGTH
------------- ------------ ---------- ---------- ------------------------------ ----------
dthsy6z1ycrhw 3 1 1 VARCHAR2(4000) 4000
dthsy6z1ycrhw 2 1 1 VARCHAR2(2000) 2000
dthsy6z1ycrhw 1 1 1 VARCHAR2(128) 128
dthsy6z1ycrhw 0 1 1 VARCHAR2(32) 32

由此可以推断
产生子光标的原因是BIND_LENGTH_UPGRADEABLE.
字符串长度从1~32的child_number=0
字符串长度从33~128的child_number=1
字符串长度从129~2000的child_number=2
字符串长度从2001~的child_number=3
如果语句中绑定变量很多，就有可能产生许多子游标，导致sql性能下降
对于字符类型的字段，进行绑定变量的时候，第一次会使用32字节的BUFFER，如果该值小于32字节的话，第二次执行这个SQL的时候，如果小于32字节，那么可以共享这个CURSOR,如果大于，就无法共享，原因就是绑定变量长度，此时会产生一个子CURSOR，同时分配128字节的BIND BUFFER，以此类推。

用脚本查看sql 高版本情况
SQL> SELECT * FROM TABLE(version_rpt('dthsy6z1ycrhw'));

COLUMN_VALUE
--------------------------------------------------------------------------------
Note:438755.1 Version Count Report Version 3.2.3 -- Today's Date 01-12月-14 13:00
RDBMS Version :11.2.0.1.0 Host: NOKGAMFVFDI3EDJ Instance 1 : ctw
==================================================================
Addr: 000007FF259BE6F8 Hash_Value: 3286654492 SQL_ID dthsy6z1ycrhw
Sharable_Mem: 44259 bytes Parses: 1 Execs:2001
Stmt:
0 INSERT /*+ bind */ INTO TEST VALUES (:B1 )
1
Versions Summary
----------------
BIND_LENGTH_UPGRADEABLE :3
Total Versions:3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Plan Hash Value Summary

COLUMN_VALUE
--------------------------------------------------------------------------------
-----------------------
Plan Hash Value Count
=============== =====
0 4
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_LENGTH_UPGRADEABLE :
Consolidated details for BIND* columns:
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)
from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PREC
======== ======== =============== =============== ======== =============== =====
4 1 32 4000 1 Yes (,)
SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
=========== ================= ============= ============
0 0 0 0

COLUMN_VALUE
--------------------------------------------------------------------------------
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
'immediate trace name cursortrace address 3286654492, level LLL';
To turn it off do use address 1, level 2147483648
================================================================
47 rows selected

SQL>

修改插入语句字符长的先插入
SQL> alter system flush shared_pool;

系统已更改。

SQL> declare
2 instring varchar2(4000);
3 begin
4 for i in 1..2001 loop
5 instring := rpad('X',i,'X');
6 instring := rpad(instring,4000);
7 insert /*+ bind */ into test values (instring);
8 end loop;
9 end;
10 /

PL/SQL 过程已成功完成。

SQL> commit;
提交完成。
SQL>

查看游标，发现就一个游标
SQL> select s.sql_id,s.child_number,s.sharable_mem,s.executions,s.invalidations from v$sql s where s.sql_id='dthsy6z1ycrhw';

SQL_ID CHILD_NUMBER SHARABLE_MEM EXECUTIONS INVALIDATIONS
------------- ------------ ------------ ---------- -------------
dthsy6z1ycrhw 0 14667 2001 1

SQL> select sql_id,child_number,bind_length_upgradeable from v$sql_shared_cursor where sql_id='dthsy6z1ycrhw'
2 ;

SQL_ID CHILD_NUMBER BIND_LENGTH_UPGRADEABLE
------------- ------------ -----------------------
dthsy6z1ycrhw 0 N
dthsy6z1ycrhw 6 Y

SQL> select sql_id,child_number,position,datatype,datatype_string,max_length from v$sql_bind_capture where sql_id='dthsy6z1ycrhw';

SQL_ID CHILD_NUMBER POSITION DATATYPE DATATYPE_STRING MAX_LENGTH
------------- ------------ ---------- ---------- ------------------------------ ----------
dthsy6z1ycrhw 6 1 1 VARCHAR2(4000) 4000
dthsy6z1ycrhw 0 1 1 VARCHAR2(4000) 4000

SQL> SELECT * FROM TABLE(version_rpt('dthsy6z1ycrhw'));

COLUMN_VALUE
--------------------------------------------------------------------------------
Note:438755.1 Version Count Report Version 3.2.3 -- Today's Date 01-12月-14 13:30
RDBMS Version :11.2.0.1.0 Host: NOKGAMFVFDI3EDJ Instance 1 : ctw
==================================================================
Addr: 000007FF259BE6F8 Hash_Value: 3286654492 SQL_ID dthsy6z1ycrhw
Sharable_Mem: 14667 bytes Parses: 1 Execs:2001
Stmt:
0 INSERT /*+ bind */ INTO TEST VALUES (:B1 )
1
Versions Summary
----------------
BIND_LENGTH_UPGRADEABLE :1
Total Versions:1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Plan Hash Value Summary

COLUMN_VALUE
--------------------------------------------------------------------------------
-----------------------
Plan Hash Value Count
=============== =====
0 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_LENGTH_UPGRADEABLE :
Consolidated details for BIND* columns:
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)
from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PREC
======== ======== =============== =============== ======== =============== =====
2 1 4000 4000 1 No (,)
SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
=========== ================= ============= ============
0 0 0 1

COLUMN_VALUE
--------------------------------------------------------------------------------
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
'immediate trace name cursortrace address 3286654492, level LLL';
To turn it off do use address 1, level 2147483648
================================================================

47 rows selected

SQL>

怎样避免由于BIND_LENGTH_UPGRADEABLE 问题导致的子游标过多呢？
oracle给出方法：
Alter the client application code so that it uses constant sizes for the MAX bind lengths.

以上操作在Oracle Database 11g Enterprise Edition Release 11.2.0.1.0上做的

0 0