Monday, April 23, 2007

Sharing SQL...

A question that pops up from time to time is:

I'm using binds, just like you said, but there are still multiple copies of my SQL in the shared pool.  Why is that?

There could be many reasons for that and if you peek at the documentation for V$SQL_SHARED_CURSOR - you'll see many of the reasons.

For example, this morning - I received an email that said in part:

 

For my post at AskTom, I am confused, after changing the code, it is not shared 100%, see this
SQL_TEXT                                                     PARSE_CALLS EXECUTIONS ADDRESS
------------------------------------------------------------ ----------- ---------- --------
SELECT ROWID,TITLE_NO,MATERIAL_NO,EDITION_NO,TITLE_NAME,AUTH         424        423 B040A9B0
OR,SUBJECTS,ED_PUB,CLASS_NO,MATERIAL_NAME,PUB_DATE,LANGUAGE_
NAME,PAGES,LOCATIONS FROM VM_LIB_MASTER WHERE TITLE_NAME LIK
E :1
SELECT ROWID,TITLE_NO,MATERIAL_NO,EDITION_NO,TITLE_NAME,AUTH         174        176 B040A9B0
OR,SUBJECTS,ED_PUB,CLASS_NO,MATERIAL_NAME,PUB_DATE,LANGUAGE_
NAME,PAGES,LOCATIONS FROM VM_LIB_MASTER WHERE TITLE_NAME LIK
E :1


The same statement was repeated.

What I think this will almost certainly be will be a "BIND MISMATCH".  They are binding :1, it is probably a string - and Oracle will reserve cursor space for the bind variables with the cursor.  Oracle does this in "steps" - so if you bind between 0 and 32 bytes - that is one bind size.  Greater than 32, less than or equal to 128 - another.  You can observe this behavior (while trashing your shared pool of course :) do not do this on your real system please!)

ops$tkyte%ORA10GR2> create table t ( x varchar2(4000) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
2 l_last number := 0;
3 l_curr number := 0;
4 begin
5 for i in 1 .. 4000
6 loop
7 execute immediate
8 'declare
9 a varchar2('||i||') := rpad(1,'||i||',1);
10 begin
11 insert into t look_for_me values(a);
12 end;';
13
14 select count(*) into l_curr
15 from v$sql
16 where sql_text like 'INSERT%INTO%T%LOOK_FOR_ME%VALUES(%:B1%)';
17
18 if ( l_last <> l_curr )
19 then
20 dbms_output.put_line( 'New version at byte: ' || i );
21 l_last := l_curr;
22 end if;
23 end loop;
24 end;
25 /
New version at byte: 1
New version at byte: 33
New version at byte: 129
New version at byte: 2001

PL/SQL procedure successfully completed.

 So, I dynamically executed a PLSQL snippet that would read:

declare
a varchar2(I) := rpad(1,I,1);
begin
insert into t look_for_me values (a);
end;

with different values for I at runtime.  I looked in v$sql to see when a new version would pop up - and just report that out. So, for that on insert statement - that will always appear the same in V$SQL - there could be four versions:

ops$tkyte%ORA10GR2> select sql_text from v$sql
2 where sql_text like 'INSERT%INTO%T%LOOK_FOR_ME%VALUES(%:B1%)';

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 )

ops$tkyte%ORA10GR2>

Now, that doesn't sound so bad - but - what happens when there are TWO binds, how many possible SQL statements could we end up with then?

ops$tkyte%ORA10GR2> create table t2 ( x varchar2(4000), y varchar2(4000) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
2 type array is table of number;
3 l_data array := array(1,33,129,2001);
4 l_last number := 0;
5 l_curr number := 0;
6 begin
7 for i in 1..l_data.count
8 loop
9 for j in 1..l_data.count
10 loop
11 execute immediate
12 'declare
13 a varchar2('||l_data(i)||') := rpad(1,'||l_data(i)||',1);
14 b varchar2('||l_data(j)||') := rpad(1,'||l_data(j)||',1);
15 begin
16 insert into t2 look_for_me values(a,b);
17 end;';
18 dbms_output.put_line( l_data(i) || ', ' || l_data(j) );
19 select count(*)
20 into l_curr
21 from v$sql
22 where sql_text like 'INSERT%INTO%T2%LOOK_FOR_ME%VALUES(%)';
23 if ( l_last <> l_curr )
24 then
25 dbms_output.put_line( 'change at ' || l_data(i) || ', ' || l_data(j) );
26 l_last := l_curr;
27 end if;
28 end loop;
29 end loop;
30
31 end;
32 /
1, 1
change at 1, 1
1, 33
change at 1, 33
1, 129
change at 1, 129
1, 2001
change at 1, 2001
33, 1
change at 33, 1
33, 33
33, 129
33, 2001
129, 1
change at 129, 1
129, 33
129, 129
129, 2001
2001, 1
change at 2001, 1
2001, 33
2001, 129
2001, 2001

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> 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.

Sort of interesting and maybe not what you would expect - you might have expected 16 (or 4).  But, it is seven.


So, how to prevent this type of bind mismatch?  By using consistent bind lengths.  In 10g - this is easy, just use PL/SQL and a consistent variable length:

ops$tkyte%ORA10GR2> create table t ( x varchar2(2000) );

Table created.

ops$tkyte%ORA10GR2> declare
2 a varchar2(1000) := 'x';
3 b varchar2(1000) := rpad('x',100,'x');
4 c varchar2(1000) := rpad('x',500,'x');
5 d varchar2(1000) := rpad('x',1000,'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.

ops$tkyte%ORA10GR2> column address new_val ADDR
ops$tkyte%ORA10GR2> select parsing_user_id puid, parsing_schema_id psid,
2 sql_text, address, child_address
3 from v$sql
4 where sql_text like 'INSERT%INTO%T%VALUES(%:B1%)'
5 /

PUID PSID SQL_TEXT ADDRESS CHILD_AD
---------- ---------- ------------------------------ -------- --------
125 125 INSERT INTO T VALUES(:B1 ) 40D926A8 40D925C4


Unfortunately, in 9i - that won't work the same:

ops$tkyte%ORA9IR2> select parsing_user_id puid, parsing_schema_id psid,
2 sql_text, address, child_address
3 from v$sql
4 where sql_text like 'INSERT%INTO%T%VALUES(%:B1%)'
5 /

PUID PSID SQL_TEXT ADDRESS CHILD_AD
---------- ---------- ------------------------------ -------- --------
72 72 INSERT INTO T VALUES(:B1 ) 5BAD9938 5BE36514
72 72 INSERT INTO T VALUES(:B1 ) 5BAD9938 5BB81798
72 72 INSERT INTO T VALUES(:B1 ) 5BAD9938 5BC2E21C


In 9i PLSQL, we have to do a bit more work - padding the bind out to the max length and using rtrim to pull it back in (so, you would do this ONLY if you had big bind mismatch problems - any varchar2 less than 128 would never need this treatment and any varchar2 that is "almost always between 200 and 500 characters" would probably not need this either

ops$tkyte%ORA9IR2> create table t ( x varchar2(2000) );

Table created.

ops$tkyte%ORA9IR2> declare
2 a varchar2(1000) := 'x';
3 b varchar2(1000) := rpad('x',100,'x');
4 c varchar2(1000) := rpad('x',500,'x');
5 d varchar2(1000) := rpad('x',1000,'x');
6 begin
7 a := rpad(a,1000);
8 insert into t values(rtrim(a));
9 b := rpad(b,1000);
10 insert into t values(rtrim(b));
11 c := rpad(c,1000);
12 insert into t values(rtrim(c));
13 d := rpad(d,1000);
14 insert into t values(rtrim(d));
15 end;
16 /

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> column address new_val ADDR
ops$tkyte%ORA9IR2> select parsing_user_id puid, parsing_schema_id psid,
2 sql_text, address, child_address
3 from v$sql
4 where sql_text like 'INSERT%INTO%T%VALUES(%:B1%)'
5 /

PUID PSID SQL_TEXT ADDRESS CHILD_AD
---------- ---------- --------------------------------- -------- --------
72 72 INSERT INTO T VALUES(RTRIM(:B1 )) 5BAEDDEC 5BAED6B8

Now, that said - bind mismatch is only one of many possible reasons for this.  So, take a peek at V$SQL_SHARED_CURSOR next time you see sql that looks like it should be shared - but isn't - to see why...

POST A COMMENT

13 Comments:

Blogger Heath Sheehan said....

There is a subtle, yet important thing to understand for your 10g example. It is leveraging the built-in PL/SQL memory optimization that, for VARCHAR2 variables defined as less than 2000 bytes in length, a fixed amount of memory is allocated for the variable, regardless of the amount of memory required to store its contents. 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.

Building on your previous example, with the insert SQL changed to not conflict, you'll see:

me@ORA10GR2>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 begin
7 insert into t(x) values(a);
8 insert into t(x) values(b);
9 insert into t(x) values(c);
10 insert into t(x) values(d);
11 end;
12 /

PL/SQL procedure successfully completed.

me@ORA10GR2>select parsing_user_id puid, parsing_schema_id psid,
2 sql_text, address, child_address
3 from v$sql
4 where sql_text like 'INSERT%INTO%T%VALUES(%:B1%)'
5 /

PUID PSID SQL_TEXT ADDRESS CHILD_AD
---------- ---------- -------------------------------------------------- -------- --------
62 62 INSERT INTO T(X) VALUES(:B1 ) 1C3E1068 1B209818
62 62 INSERT INTO T(X) VALUES(:B1 ) 1C3E1068 1D6AD0F4
62 62 INSERT INTO T(X) VALUES(:B1 ) 1C3E1068 1A966D30

3 rows selected.

Mon Apr 23, 12:51:00 PM EDT  

Blogger Thomas Kyte said....

Heath -

very nice, thanks for that. I should have thought of that - I know that magic 1999 to 2000 byte feature of plsql...

Case in point why publishing examples, along with saying "this is what is true", is really important :)

Mon Apr 23, 12:56:00 PM EDT  

Blogger Alberto Dell'Era said....

You might also insert first with the largest bind variable - building on Heath's test case that builds on yours, in 10.2.0.3:

SQL> 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 begin
7 insert into t(x) values(d); -- note this
8 insert into t(x) values(a);
9 insert into t(x) values(b);
10 insert into t(x) values(c);
11 end;
12 /

PL/SQL procedure successfully completed.

SQL> select parsing_user_id puid, parsing_schema_id psid,
2 sql_text, address, child_address
3 from v$sql
4 where sql_text like 'INSERT%INTO%T%VALUES(%:B1%)';

PUID PSID SQL_TEXT ADDRESS CHILD_AD
---------- ---------- ---------------------------------------- -------- --------
58 58 INSERT INTO T(X) VALUES(:B1 ) 32B47DBC 32FE051C

This idea is not mine but is by Alexander Fatkulin in this thread:

http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/

I remember building a test case (ctrl-f to "logically obsolete" below) that shows that all the child cursors but the last are ignored: the runtime engine does not use the child with the "best matching bind size"; hence there's no downside in doing this (even if I'm not an expert in this library cache scenarios, beware).

Mon Apr 23, 05:15:00 PM EDT  

Anonymous Adam said....

try 1001 bytes, in 10.2.0.3:
SQL> create table t (a varchar2(2000));

Table created.

SQL> declare
2 a varchar2(1001) := 'x';
3 b varchar2(1001) := rpad('x',100,'x');
4 c varchar2(1001) := rpad('x',500,'x');
5 d varchar2(1001) := rpad('x',1000,'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, address, child_address
2 from v$sql
3 where sql_text like 'INSERT%INTO%T%VALUES(%:B1%)';

SQL_TEXT ADDRESS CHILD_ADDRESS
--------------------------------------------- ---------------- ----------------
INSERT INTO T VALUES(:B1 ) 000000015EAB2CC0 000000015084BE20
INSERT INTO T VALUES(:B1 ) 000000015EAB2CC0 0000000150D04558
INSERT INTO T VALUES(:B1 ) 000000015EAB2CC0 000000015E531938

Tue Apr 24, 03:56:00 AM EDT  

Blogger Sandro said....

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered
SQL> 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 begin
7 insert into t(x) values(d); -- note this
8 insert into t(x) values(a);
9 insert into t(x) values(b);
10 insert into t(x) values(c);
11 end;
12 /

PL/SQL procedure successfully completed
SQL> select parsing_user_id puid, parsing_schema_id psid,
2 sql_text, address, child_address
3 from v$sql
4 where sql_text like 'INSERT%INTO%T%VALUES(%:B1%)';

PUID PSID SQL_TEXT ADDRESS CHILD_ADDRESS
---------- ---------- -------------------------------------------------------------------------------- -------- -------------
31 31 INSERT INTO T(X) VALUES(:B1 ) 1C4F9974 1CF2E6E0

Tue May 08, 12:44:00 PM EDT  

Blogger Keith said....

Hi,

I have a similar issue of variable length strings causing BIND MISMATCH and therefore multiple versions of my query plans.

The difference is I am using ADO to create the bind varaibles (via ADO::Parameter). Also, it seems that ADO/Oracle map a null value onto an empty string.

So, if I put a NULL in a NUMERIC parameter, ADO/Oracle seems to create a VARCHAR2(32) to store it. Given this is different to NUMERIC, that gets me another query plan. The permutations/combinations of NULL distributed causes thousands of plans and so on to latch contention and poor scalability.

I've tried passing everything as a string, and having Oracle coerce them back to the INTEGER or FLOAT and this works ok, except than I now get a mixture of VARCHAR(32) and VARCHAR(128).

I could understand that if my strings were sometimes longer than 32 chars, but they are not (they are just strings containing integers and doubles).

It seems that either ADO or Oracle is creating VARCHAR2(128) for any string longer than 8 characters.

Does anyone know why I am getting these larger strings? Or any other ideas?

Wed May 28, 05:48:00 AM EDT  

Anonymous Anonymous said....

Hi Tom,

Thanks for this nice article.

How to get rid of bind mismatch with jdbc prepared statements?

-- Deepak

Tue Jun 03, 05:17:00 AM EDT  

Blogger Thomas Kyte said....

@deepak -

the easiest way to get rid of bind mismatches is to centralize the sql in plsql so there is precisely one place where it is done and it is done consistently :)

The best java code has no hint of select, insert, update, delete or merge in it...

Tue Jun 03, 09:51:00 AM EDT  

Blogger ybhandarkar said....

Hello Tom,

I read couple of documents, which talks about setting events 10503 trace with buffer size, to overcome varchar2 problem.

I've tried couple of combinations

alter system set events '10503 trace name context forever, level 128';
alter system set events '10503 trace name context forever, level 4000';

But still I see the child cursor getting created, based on the length mismatch of the data passed to the prepared statements


VARCHAR2(32)
VARCHAR2(128)
VARCHAR2(2000)

Any thoughts?

Even if this works, is it advisable to use from performance perspective?

Fri Aug 01, 08:49:00 PM EDT  

Blogger Thomas Kyte said....

@ybhandarkar

why not just *bind consistently*, as in *do it in one place*

then you need no fancy undocumented tricks at all.

Sat Aug 02, 08:07:00 AM EDT  

Blogger ybhandarkar said....

Hello Tom,

I fully agree with your *bind consistently* approach. I'm changing JDBC prepared statement to PL/SQL.

In the mean time, I was trying to find a temporary fix.

Thanks for your comments.

Mon Aug 04, 09:26:00 AM EDT  

Anonymous shervin said....

Hi Tom,

Please let me know if there is any solution for Java (specifically for Hibernate).

Fri Jan 15, 01:50:00 PM EST  

Blogger Thomas Kyte said....

@Shervin

best: use stored procedures for database access, then it will be done consistently.

everything else: do it consistently, bind using consistent inputs, use a plsql block if you cannot achieve that in your language/framework.

Fri Jan 15, 06:47:00 PM EST  

POST A COMMENT

<< Home