bind variables – some notes for the new dba




Read some discussion from the oracle guru Tom on what bind variables are and how not having
bind variables can cause performance problems to sql queries in an OLTP database.

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1163635055580

is one approach, I used a table, but you don’t have to. You could probably rewrite the function as

a regular expression (10g feature) and use WITH instead of filling a temporary table with the

contents of v$sql

http://asktom.oracle.com/pls/asktom/f?p=100:11:3738968223502678::::P11_QUESTION_ID:1163635055580

example of using bind variables.

declare

l_tmp long;

begin

for x in ( select rowid rid, sql_text from t1 )

loop

l_tmp := remove_constants( x.sql_text );

update t1 set sql_text_wo_constants = l_tmp where rowid = x.rid;

end loop;

end;

Hi tom ,

the solution/work around, you have given above, does it

use the bind variables ?

beacause I don’t know yet, after all your explanation in all questions and your book

the difference between the cursor variable and bind variable

I think can we use the bind variable in the following way

will the performance better ?

/********************************************/

declare

l_tmp long;

begin

open x for

‘select rowid rid, sql_text from t1’ –returns 10000 rows

loop

l_tmp := remove_constants( x.sql_text );

execute immediate

‘update t1 set sql_text_wo_constants=:a where rowid :y’

using remove_constants( x.sql_text ),x.rid

end loop;

end;

/

/*********************************************/

could you please explain. Tankans..

NO — that would be a terribly slow, bad way to do it.

It violated my rule #1

a) if you can do it in single SQL statment — DO IT.

All of the code above makes 100% use of bind variables where appropriate. When considering "have I

done this bind variable thing right" you only need look at the sql statement:

update t1 set sql_text_wo_constants = remove_constants(sql_text);

and then ask "will I ever submit a statement that looks ALMOST exactly like that except that some

constant has changed?"

The answer in this case is "definitely NOT, there aren’t even any constants in there".

When you see queries like:

update t set x = 5 where y = 2;

update t set x = 6 where y = 1;

update t set x = 8 where y = 43;

Those statements demand bind variables. That should be a single statment:

update t set x =:x  where y = :y;

that is executed with different inputs. As my update:

update t1 set sql_text_wo_constants = remove_constants(sql_text);

has no constants — there are thusly NO binds to be done.

don’t we have the same situation where

we are saying

update t set x = l_tmp where y = x.rid

isn’t it same as

update t set x = 4 where y = 5

so won’t the sql stmt will distinct at run time

it be executed the # of rows time ?

are we using bind varible ? if yes how ?

if not, then in this case, will it be better to use bind

using the execute immedieate ?

Plese explain..

thanks in advence

No, it is not. I am using host variables. The statement:

update t1 set sql_text_wo_constants = l_tmp where rowid = x.rid;

is really:

update t1 set sql_text_wo_constants = :BV1 where rowid = :BV2;

anywhere there is a PLSQL variable — it is really a host variable, a bind variable. That is part

of the beauty of PLSQL — it makes binding almost MOOT. You have to absolutely go out of your way

to NOT bind in plsql.

Using this great technique, I found and fixed three minor transgressions in my code that were

wasting cache space. However the vast majority of SQL that was duplicated in the cache was

generated by Oracle Sales Analyzer or Oracle Discoverer. Don’t you have any in-house readers, Tom?

😉

I can understand why OSA might not be able to use bind variables because of its dynamic nature, but

Discoverer builds fairly straight forward queries against static tables. Are there any techniques

we can use to shield the database from abuse by query generation tools?

By coincidence, I had read you book up through page 448 last night. I experienced some deja vu when

I resumed my reading this evening on page 449 where you cover this very function.

Your book is excellent. I don’t know why more technical documentation isn’t written in the 1st

person. It’s the natural way to communicate.

Followup August 22, 2002 – 10pm US/Eastern:

Actually, in a data warehouse (such as you would be using these tools) — not using Binds is ok.

You need binds when you are parsing X statements / second.

When are you parsing 1 statement every Y seconds, it is not nearly as important.

OLTP = mandatory binds

Mixed Used = mostly 99% binds

DW = binds if you want to (i cannot believe i said that!)

The plans for the dataware house will be better if you use constants/literals in general. You do

not have the scalability wall since you are doing queries that take a long time (and hence you are

not parsing x queries per second, the latching doesn’t hit you). Also, the usage of the shared

pool is "gentler" so the space problem is not bad at all — we have time to manage the shared pool,

we aren’t in a rush as much.

If I have a query in a PreparedStatement that reads

String sql = "select a,b,c from table where d=? and e=?";

and create a PreparedStatement with it and subsequently fill the ? with the setXXX() method. Does

it work like a query with bind variables or do I need to write the query in a yet different way?

Followup October 9, 2002 – 5pm US/Eastern:

that is how to do bind variables in JDBC, the ? are bind placeholders.

So yes, this is all you need to do to use binds in JDBC

Author: admin