Re: Bind Variables...

Mark, et al-

This reply is misleading. I will comment inline.

mark wrote:
>
> If you use bind variables, then the Sql statement gets cached . So the
> next time you execute this , Oracle does not parse the query once
> again. Use bind variables when the same Sql Statement gets executed
> several times and response times have to be fast.

SQL statements are *always* placed in the library cache. Strictly speaking, all SQL is "sharable". It's just that predicates with constant values rather than bound values are less likely to be shared. Using bind variables does not ensure sharing, it only makes it more likely for those applications that use them. Consider:

select * from fred where cust = 12

Compared to:

select * from fred where cust = :1

If the predicate 'cust = 12' is frequently used, that statement will be shared. If not, it just takes up space in the library cache until memory is needed and is then flushed out. The second example enables any valid number to be applied by an application, such that customers 13 and 14 can also be selected with this same statement.

The effort saved by the server can be significant, not having to construct a new cursor each time.

Another example of perfectly sharable sql is "select sysdate from dual", no predicate, no bind value. Just a simple sql statement that can be executed time and again by many users.

In addition, it is not true to say Oracle does not parse the query again once a bind variable is used. Parsing is a phenomenon generated by the application itself. If a cursor is kept open, bind variables or not, it can be re-executed without another parse. On the other hand, if it is closed after each execution, as is common with many applications, it will have to be reparsed - as a "soft parse" - whether there are bind variables involved or not. What is avoided is a "hard parse", in which a new cursor is constructed from scratch, involving numerous steps and memory allocations. "Hard parsing" is expensive, "soft parsing" is less expensive, "no parsing" is cheapest.

>
> Oracle will not know the value of :1 when it determines the best way
> to execute the query. Problem here is that the Oracle optimizer does
> not know the "Full details" when it charts out the execution plan.
> This may cause an execution plan that is usually worse than if it knew
> the value of :1. Usually the cost based optimizer places an arbitary
> selectivity to favor the use of an index.

Actually, this logic applies to range scans where the optimizer, as of Oracle 8i, does not have access to the bind values and cannot use histograms to estimate query selectivity. If you don't use histograms anyway, bind variables may work just as well. In 9i the optimizer is able to peek at the PGA, where the bind values are stored and can make good use of histograms for range scans even with bind variables.

In cases where the predicate is getting an exact match, like a "where column = " type of query, bind variables work fine. No penalty at because the optimizer is able to estimate selectivity with neither histograms nor bind values.

> As a good enough thumb rule, if you have a statement which executes
> once or very few times and response times are good, avoid bind
> variables.

There is no penalty for using bind variables in any case, with the exception noted previously. If you are not using histograms with range scans, there is no reason to avoid bind variables.

> I do not know what placeholders are . sorry.

The place holder is just the syntactic construct that provides for bind value substitution. In "where cust = :1", the ":1" is the place holder.

I think in Thomas Kyte's original reply, when he suggested, "In a datawarehouse, where you have computed histograms and have very skewed data -- skip the bind variable on that column -- sometimes.", he was referring to the non-use of bind values for range scans when histograms are available. And I do believe this issue goes away in 9i.

• ricky