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'

• Michael-O: Well, I do not intend to pass another value than 2 but that statement will still be present several times in the SQL cache. Spite that, the database cannot mark both statements as equal. Is my assumption correct? This is what I have found in several asktom threads.
• Justin Cave: @Michael-O - If the status will always be 2, every instance of the query will have the same sql_id and will appear once in the cache (barring cases where Oracle has two different plans for the same SQL statement). There won't be any benefit to converting status to a bind variable in that case.
• Michael-O: So using bind heavily depends on the case, there is no concrete rule? This make the issue more complicated. I'd like to pass a definitive guideline to my users. I guess, I have to very that by testing and examining v$sqlarea.
• Michael-O: Just an addendum, so truly dynamic values should use binds?
• Justin Cave: @Michael-O - The guideline I'd give to users is that is a value might ever change, it should be a bind variable. If it's a simple hard-coded value that will never change, it should be a literal.