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