Re: WHY SQL-Statement without bind-prameters runs 50 times faster as with ??????

I suspect what they told you is that the execution plan generated by bind variables can be less optimal than the plan generated for literals because the optimiser doesn't know ahead of time how the actual value of the bind variable will fit into the distribution of values within the table. Explain plan will still work for a statement with bind variables and *may* produce a different output to the same statement with literals

<NOTE: Following info suspect as I am not at all sure I understand correctly>

This has apparently led to the situation in 8.1.6 where Oracle can both replace literals with psuedo bind variables so that the sql statement doesn't need to be constantly reparsed, and where the optimiser will substitute the values of the bind variables for literals in order to determine an execution plan.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Pat Boivin" <lori.pat_at_ns.sympatico.ca> wrote in message
news:3910A837.C46F87F5_at_ns.sympatico.ca...


> That's funny, I am just repeating what Oracle Worldwide Support told me

> about two months ago.

>

> I'll see if I can dig up the technical note for you, maybe I didn't

 express


> myself well.

>

> Regards,

>

> Pat.

>

> Mark D Powell wrote:

>

> > In article <390F8165.A745A66_at_ns.sympatico.ca>,

> >   lori.pat_at_ns.sympatico.ca wrote:

> > > Yes,  to_date is a function, and when you put a function on the left

 side of


> > > your where clause statements, Oracle does full table scans.

> > >

> > > There is a good book on how to tune SQL, SQL High Performance Tuning

 by Guy


> > > Harrison, he goes into a lot of detail.

> > >

> > > Bind variables are annoying because you can't use explain plan with

 them, you


> > > have to trace the session, see in the trace file what variables were

> > > substituted, and plug them into the statement.  Even then there is no

 guarantee


> > > that Oracle will do what the query's explain plan shows with the

 values placed


> > > directly inside it.

> > >

> > > (I wish I could speak German!)

> > >

> > > Pat.

> > >

> > I do not understand your statement that "Bind variables are annoying

> > because you can't use explain plan with them, you have to trace the

> > session," since this is not true.  You can explain a statement with

> > bind variables in it, and the manner in which Oracle resolves a query

> > with bind variables in it can be very different from the same query

> > written with constants where the bind variables belong even when

> > histograms do not exist.  I agree with you that Guy Harrison's book

> > Oracle SQL High-Performance Tuning is excellent.

> > --

> > Mark D. Powell  -- The only advice that counts is the advice that

> >  you follow so follow your own advice --

> >

> > Sent via Deja.com http://www.deja.com/

> > Before you buy.

>