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 )

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 )

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上做的

..[ ]()[ ]()

.#### 我的同类文章

                                oracle_（144）_
                            _•_[centos 设置oracle 开机自启动](http://blog.csdn.net/weiwangsisoftstone/article/details/51788135)2016-06-30_•_[windows 8 安装oracle 12c dbca创建数据库报错创建服务出错](http://blog.csdn.net/weiwangsisoftstone/article/details/50488888)2016-01-09_•_[发现数据库查询表空间使用率语句执行的很慢](http://blog.csdn.net/weiwangsisoftstone/article/details/45557389)2015-05-07_•_[No Write Permission on ACFS Mount Point](http://blog.csdn.net/weiwangsisoftstone/article/details/45368771)2015-04-29_•_[监控oracle系统的性能](http://blog.csdn.net/weiwangsisoftstone/article/details/44750723)2015-03-30_•_[选择和优化索引](http://blog.csdn.net/weiwangsisoftstone/article/details/44590063)2015-03-24_•_[xmanager配置xstart调出oracle图形化界面安装](http://blog.csdn.net/weiwangsisoftstone/article/details/51782739)2016-06-29_•_[sql server ,mysql ,oracle 自增长字段设置以及自增长属性的修改验证](http://blog.csdn.net/weiwangsisoftstone/article/details/50434587)2015-12-30_•_[Goldengate的安装与配置(配置rac到单实例的同步)](http://blog.csdn.net/weiwangsisoftstone/article/details/45536297)2015-05-06_•_[oracle 11g r2修改数据用户密码](http://blog.csdn.net/weiwangsisoftstone/article/details/45242095)2015-04-24_•_[优化实例内存](http://blog.csdn.net/weiwangsisoftstone/article/details/44648757)2015-03-26[更多文章](http://blog.csdn.net/weiwangsisoftstone/article/category/1155618)