Re: Bind Variables...

Hello Ricky

I do not use Oracle 9i and so i cant comment on it. But this statement of yours reproduced in the end <<seems>> to be incorrect. I remember reading some manuals and Thomas Kytes first chapter( which is free ;-)) where he had mentioned that not using bind variables might cause the application to grind to a halt. This is because it compiles(parses) it again. I think Howard Rogers book downloadable from tripod again says it.

We are NOT talking about application parsing here. I do know that we can open cursors in the application in memory, reuse it again and again. I do not think Oracle reparses the ones parsed again , if the exact SQL can be found in memory.

Again my Oracle brain is not the brightest ;-), but still the general rule remains. Avoid binds if you have a Sql that executes once or a very few times and the time of execution is not a problem. Use binds for repetitive operations.

One of the **biggest** advantage of not using binds is that they can be debugged more easily. I am referring to 4GL tools which can be used to connect to Oracle. It is a nightmareto debug applications which have already been sold to the client if they have binds?. Oracle (previous to 9i) has some undocumented features to write the value of the binds to the trace file. But i do not know much about this.

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.

Ricky Sanchez <rsanchez_at_more.net> wrote in message news:<3BE97742.BE601624_at_more.net>...
> 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
Received on Wed Nov 07 2001 - 23:16:19 CST