Oracle bind between 0 and 32 bytes - in one bind size. Greater than 32, less than or equal to 128 -into Another.
scott@10G> create table t (x varchar2(4000));
Table created.
scott@10G> select count(*)
2 from v$sql
3 where sql_text like 'INSERT%INTO%T%LOOK_FOR_ME%VALUES%';
COUNT(*)
----------
0
scott@10G> declare
2 i number;
3 l_curr number := 0;
4 l_last number := 0;
5 begin
6 for i in 1..4000
7 loop
8 execute immediate ' declare
9 a varchar2('||i||') := rpad(''*'','||i||',''*'');
10 begin
11 insert into t look_for_me values (a) ;
12 end; ';
13
14 select count(*)
15 into l_curr
16 from v$sql
17 where sql_text like 'INSERT%INTO%T%LOOK_FOR_ME%VALUES%';
18
19 if l_last <> l_curr then
20 dbms_output.put_line (' new version change at '||i);
21 l_last := l_curr;
22 end if;
23 end loop;
24 commit;
25 end;
26 /
new version change at 1
new version change at 33
new version change at 129
new version change at 2001
I dynamically executed the Pl/SQL block for different Values of I.
declare
a varchar2(I) := rpad('*',I,'*');
begin
insert into t look_for_me values (a) ;
end;
with different values of I at runtime, i looked into v$sql for my bind substituted query.
scott@10G> SELECT sql_text
2 FROM v$sql
3 WHERE sql_text LIKE 'INSERT%INTO%T%LOOK_FOR_ME%VALUES%';
SQL_TEXT
--------------------------------------------------------------
INSERT INTO T LOOK_FOR_ME VALUES (:B1 )
INSERT INTO T LOOK_FOR_ME VALUES (:B1 )
INSERT INTO T LOOK_FOR_ME VALUES (:B1 )
INSERT INTO T LOOK_FOR_ME VALUES (:B1 )
Now, that doesn't sounds bad. But what happens with TWO Bind variables?
scott@10G> create table t2(x varchar2(4000),y varchar2(4000));
Table created.
Elapsed: 00:00:00.01
scott@10G> declare
2 type array is table of number;
3 arr array := array(1,33,129,2001);
4 l_curr number := 0;
5 l_last number := 0;
6 i number;
7 k number;
8 begin
9 for i in 1..arr.count
10 loop
11 for k in 1..arr.count
12 loop
13 execute immediate ' declare
14 a varchar2('||arr(i)||') := rpad (''*'',' ||arr(i)||',''*'') ;
15 b varchar2('||arr(k)||') := rpad (''*'',' ||arr(k)||',''*'') ;
16 begin
17 insert into t2 look_for_me values (a,b) ;
18 end; ';
19
20 select count(*)
21 into l_curr
22 from v$sql where sql_text like 'INSERT%INTO%T2%LOOK_FOR_ME%VALUES%';
23
24 if l_curr <> l_last then
25 dbms_output.put_line (' new version change at i ='||i||' , k ='||k);
26 l_last := l_curr;
27 end if;
28 end loop;
29 end loop;
30 commit;
31 end;
32 /
new version change at i =1 , k =1
new version change at i =1 , k =2
new version change at i =1 , k =3
new version change at i =1 , k =4
new version change at i =2 , k =1
new version change at i =3 , k =1
new version change at i =4 , k =1
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
scott@10G> SELECT sql_text
2 FROM v$sql
3 WHERE sql_text LIKE 'INSERT%INTO%T2%LOOK_FOR_ME%VALUES%';
SQL_TEXT
-----------------------------------------------------------------------------------------------------------
INSERT INTO T2 LOOK_FOR_ME VALUES (:B2 ,:B1 )
INSERT INTO T2 LOOK_FOR_ME VALUES (:B2 ,:B1 )
INSERT INTO T2 LOOK_FOR_ME VALUES (:B2 ,:B1 )
INSERT INTO T2 LOOK_FOR_ME VALUES (:B2 ,:B1 )
INSERT INTO T2 LOOK_FOR_ME VALUES (:B2 ,:B1 )
INSERT INTO T2 LOOK_FOR_ME VALUES (:B2 ,:B1 )
INSERT INTO T2 LOOK_FOR_ME VALUES (:B2 ,:B1 )
7 rows selected.
So, with Increase in Bind variables the number of query in the Shared pool keeps increasing for Bind mismatch. So how to prevent this bind mismatch? using By using consistent bind lengths. In 10g this is easy using Pl/sql.
scott@10G> declare
2 type array is table of number;
3 l_data array := array(1,100,500,1000);
4 l_value varchar2(1000);
5 begin
6 for i in 1..l_data.count
7 loop
8 l_value := rpad('*',l_data(i),'*');
9 insert into t3 values (l_value);
10 end loop;
11 commit;
12 end;
13 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
scott@10G> SELECT sql_text
2 FROM v$sql
3 WHERE sql_text LIKE 'INSERT%INTO%T3%VALUES%';
SQL_TEXT
--------------------------------------------------------
INSERT INTO T3 VALUES (:B1 )
Unfortunately, in 9i this won't work.
scott@9iR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
scott@9iR2> create table t3(x varchar2(4000));
Table created.
Elapsed: 00:00:00.07
scott@9iR2> declare
2 a varchar2(2000) := 'x';
3 b varchar2(2000) := rpad('x',100,'x');
4 c varchar2(2000) := rpad('x',500,'x');
5 d varchar2(2000) := rpad('x',1000,'x');
6 e varchar2(2000) := rpad('x',2000,'x');
7 begin
8 insert into t3 values (a);
9 insert into t3 values (b);
10 insert into t3 values (c);
11 insert into t3 values (d);
12 insert into t3 values (e);
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
scott@9iR2> SELECT sql_text
2 FROM v$sql
3 WHERE sql_text LIKE 'INSERT%INTO%T3%VALUES%';
SQL_TEXT
-------------------------------------------------------------------
INSERT INTO T3 VALUES (:B1 )
INSERT INTO T3 VALUES (:B1 )
INSERT INTO T3 VALUES (:B1 )
In, 9i Pl/SQL we need to do some extra work - Pad the Bind out to some maximum lenght and use trim to put it back.
scott@9iR2> SELECT sql_text
2 FROM v$sql
3 WHERE sql_text LIKE 'INSERT%INTO%T3%VALUES%';
no rows selected
Elapsed: 00:00:00.03
scott@9iR2> declare
2 a varchar2(2000) := 'x';
3 b varchar2(2000) := rpad('x',100,'x');
4 c varchar2(2000) := rpad('x',500,'x');
5 d varchar2(2000) := rpad('x',1000,'x');
6 e varchar2(2000) := rpad('x',2000,'x');
7 begin
8 a := rpad(a,2000);
9 b := rpad(b,2000);
10 c := rpad(c,2000);
11 d := rpad(d,2000);
12 e := rpad(e,2000);
13 insert into t3 values (trim(a));
14 insert into t3 values (trim(b));
15 insert into t3 values (trim(c));
16 insert into t3 values (trim(d));
17 insert into t3 values (trim(e));
18 end;
19 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
scott@9iR2> SELECT sql_text
2 FROM v$sql
3 WHERE sql_text LIKE 'INSERT%INTO%T3%VALUES%';
SQL_TEXT
----------------------------------------------------------
INSERT INTO T3 VALUES (TRIM(:B1 ))
Elapsed: 00:00:00.01
So, Bind mismatch is only one of many possible reason for this. So take a look at v$sql_shared_cursor next time you see sql that looks like it should be shared-but isn't.
Importat thing to note here is This optimization is not enabled for VARCHAR2 variables defined as greater than 2000 bytes in length, so the sharing that you are demonstrating will not occur if you define your variables whose length exceeds the maximum for that optimization.
scott@10G> create table t3(x varchar2(4000));
Table created.
scott@10G> declare
2 a varchar2(2000) := 'x';
3 b varchar2(2000) := rpad('x',100,'*');
4 c varchar2(2000) := rpad('x',500,'*');
5 d varchar2(2000) := rpad('x',1000,'*');
6 begin
7 insert into t3 values (a);
8 insert into t3 values (b);
9 insert into t3 values (c);
10 insert into t3 values (d);
11 end;
12 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
scott@10G> SELECT sql_text
2 FROM v$sql
3 WHERE sql_text LIKE 'INSERT%INTO%T3%VALUES%';
SQL_TEXT
----------------------------------------------------------------------------------
INSERT INTO T3 VALUES (:B1 )
INSERT INTO T3 VALUES (:B1 )
INSERT INTO T3 VALUES (:B1 )
Elapsed: 00:00:00.01
Again the Solution is Here.The child cursors are created with "small bind spaces" that can grow by spawning new child cursors - but if the first entry is big and the rest get smaller - that won't happen.
scott@10G> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.20
scott@10G> truncate table t3;
Table truncated.
Elapsed: 00:00:00.60
scott@10G> declare
2 a varchar2(2000) := 'x';
3 b varchar2(2000) := rpad('x',100,'*');
4 c varchar2(2000) := rpad('x',500,'*');
5 d varchar2(2000) := rpad('x',1000,'*');
6 begin
7 insert into t3 values (d);
8 insert into t3 values (c);
9 insert into t3 values (b);
10 insert into t3 values (a);
11 end;
12 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
scott@10G> SELECT sql_text
2 FROM v$sql
3 WHERE sql_text LIKE 'INSERT%INTO%T3%VALUES%';
SQL_TEXT
--------------------------------------------------------------
INSERT INTO T3 VALUES (:B1 )
Elapsed: 00:00:00.01
No comments:
Post a Comment