Re: Bind Variables...

In article <fa4781e4.0111072116.385abdcc_at_posting.google.com>, mark_brehmen_at_yahoo.com says...
>
>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.
>

well here i have to totally disagree with you and i pray that no one using 4gl tools sees this!

I cannot tell you HOW many times I walk into a place that has an "off the shelf" app that some company put together that works well in a demo that fails MISERABLY in production with say 10 or 20 users -- simply because they failed to use binds

Read chapter 10 in my book -- "debugging" with binds is not hard. Its all in there.

Debugging without binds is trivial since the bug is -- you didn't use BINDS. It is very easy to find that bug, harder to fix it after the fact (since you believed you were done)

>
>
>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

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp