玉面飞龙的BLOG

Oracle Bind Variable在Varchar2字段上存在一些坎儿，32，128，2000。意味着在应用中(N>2000)，一个Varchar2(N),可以贡献4个不同的子游标。2个Varchar2（N)可以贡献7个子游标。如果一个表上有10个Varchar2(N)的子段，则可以贡献31个游标。就是SQL Version Count = 3*N + 1 如果表很多，有M个，就是SQL Version Count = 3*N*M + M

过多的游标填充Shared Pool，会导致CPU Contention on Library Cache Latch.

解决的终极方法就是麻烦Developer，padding the bind out to the max length and using rtrim to pull it back in

SQL> create table t ( x varchar2(4000) );
Table created.

SQL> declare
2            a   varchar2(4000) := ‘x’;
3            b   varchar2(4000) := rpad(’x',33,’x');
4            c   varchar2(4000) := rpad(’x',129,’x');
5            d   varchar2(4000) := rpad(’x',2001,’x');
6            begin
7            a := rpad(a,4000);
8            insert into t values(rtrim(a));
9            b := rpad(b,4000);
10            insert into t values(rtrim(b));
11            c := rpad(c,4000);
12            insert into t values(rtrim(c));
13            d := rpad(d,4000);
14            insert into t values(rtrim(d));
15            end;
16  /

PL/SQL procedure successfully completed.

SQL> select sql_text,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM from v$sql where sql_text like ‘INSERT INTO T%’;

SQL_TEXT                            SHARABLE_MEM    PERSISTENT_MEM    RUNTIME_MEM
———— ————– ——————— ——————— ——————— ———–
INSERT INTO T VALUES(RTRIM(:B1))               11025                  976                      5976

SQL> drop table t;

Table dropped.

SQL> create table t ( x varchar2(4000) );

Table created.

SQL> declare
2            a   varchar2(4000) := ‘x’;
3            b   varchar2(4000) := rpad(’x',33,’x');
4            c   varchar2(4000) := rpad(’x',129,’x');
5            d   varchar2(4000) := rpad(’x',2001,’x');
6            begin
7            insert into t values((a));
8            insert into t values((b));
9            insert into t values((c));
10            insert into t values((d));
11            end;
12  /

PL/SQL procedure successfully completed.

SQL> select sql_text,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM from v$sql where sql_text like ‘INSERT INTO T%’;

SQL_TEXT                       SHARABLE_MEM      PERSISTENT_MEM      RUNTIME_MEM
———— ————– ———————– ————– ———–
INSERT INTO T VALUES((:B1))     10020               976             1952
INSERT INTO T VALUES((:B1))     10468               976             1952
INSERT INTO T VALUES((:B1))     10796               976             1952
INSERT INTO T VALUES((:B1))     10076               976             1952

不同的API都应该可以使用类似的方法。

Read More here. 

And there.