Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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'
share|improve this answer
    
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. –  Michael-O Aug 25 '12 at 21:29
    
@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. –  Justin Cave Aug 25 '12 at 21:32
    
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 Aug 25 '12 at 21:36
    
Just an addendum, so truly dynamic values should use binds? –  Michael-O Aug 25 '12 at 21:46
1  
@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. –  Justin Cave Aug 25 '12 at 21:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.