I am currently examining all database users's SQL code in our department for not using bind variables. We do experience an ORA-04031 error within a constant period of time.
Now, I have a quite firm understanding where to use them but I am not sure how far this should go. Now, do I have to replace all literals with bind vars?
For instance:
select 1 from t1 ; => select :one from table t;
or
select c1, c2, from t2 where id = :id and status = 2 =>
select c1, c2, from t2 where id = :id and status = :two
or
select * from t3 where c3 > :value and exists (select 1 from t4 where <some condition>) =>
select * from t3 where c3 > :value and exists (select :one from t4 where <some condition>)
Same applies to DML.
My intuition says that everything has to be a bind variable to achieve optimal performance.
Best Answer
Use a bind variable anywhere that you might actually change the value that is passed in.
It probably doesn't make sense, for example, to use a bind variable in the
EXISTS
clause in your third example because it seems extremely unlikely that anyone would ever want to pass in a different constant for theEXISTS
clause.In your second example, it probably does make sense to use a bind variable rather than a hard-coded status assuming that the application(s) are likely to want to run this query for different
id
andstatus
values. On the other hand, if the code would only ever pass in astatus
of 2 for this query, it probably doesn't make sense to use a bind variable. Assuming thatstatus
is not evenly distributed, you'd be better off giving the optimizer the information that you're always going to use astatus
of 2 rather than trying to rely on bind variable peeking to give the optimizer that information.If the value is never going to change-- i.e. if the
status
in your second query will always be 2-- you can execute the query as many times as you'd like and it will only appear once inv$sql
orv$sqlarea
. In my case, I'll execute the query 10 timesAnd from
v$sql
orv$sqlarea
, you'll see only one row for the query that has the hard-coded literal. That row will show that the query was executed 10 times