How far should bind variables go in Oracle?

Question

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.

Answer 1

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 the EXISTS 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 and status values. On the other hand, if the code would only ever pass in a status of 2 for this query, it probably doesn't make sense to use a bind variable. Assuming that status is not evenly distributed, you'd be better off giving the optimizer the information that you're always going to use a status 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 in v$sql or v$sqlarea. In my case, I'll execute the query 10 times

SQL> ed
Wrote file afiedt.buf

  1* select * from v$sql where sql_text like '%jc_bind_test%'
SQL> select /* jc_literal_test */ count(*)
  2    from emp
  3   where ename = 'KING';

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1

And from v$sql or v$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

SQL> ed
Wrote file afiedt.buf

  1* select sql_text, executions, loaded_versions, parse_calls from v$sqlarea where sql_text like '%jc_literal_test%'
SQL> /

SQL_TEXT                                 EXECUTIONS LOADED_VERSIONS PARSE_CALLS
---------------------------------------- ---------- --------------- -----------
select * from v$sql where sql_text like           1               1           1
'%jc_literal_test%'

select sql_text, executions, loaded_vers          5               1           5
ions, parse_calls from v$sql where sql_t
ext like '%jc_literal_test%'

select sql_text, executions, loaded_vers          1               1           1
ions, parse_calls from v$sqlarea where s
ql_text like '%jc_literal_test%'

select /* jc_literal_test */ count(*)            10               1          10
from emp  where ename = 'KING'