Re: Scaling Oracle 8i - Histograms

... same thought here as Jusung.
9i cbo optimizer can however peek at the bind variable, the first time it executes the statement.

Though I don't think the books statement is even remotely referring to the 9i behavior here. I don't have the book in front of me right now.. so all I can say right now is that there is a misunderstanding. Are you sure that he meant optimizer plans can change for queries which use all bind variables ... if the value of bind variable changes. The statement you quoted does not say that!

Anurag

"Ganesh Raja" <ganesh_at_gtfs-gulf.com> wrote in message news:1055210960.585627_at_cswreg.cos.agilent.com...
> Jusang,
>
> Thanks for that... But still ... Let us wait for more responses...
>
> --
> HTH
>
> Regards,
> Ganesh R
>
>
> "Jusung Yang" <JusungYang_at_yahoo.com> wrote in message
> news:130ba93a.0306091406.107a841b_at_posting.google.com...
> > ganesh_at_gtfs-gulf.com (Ganesh Raja) wrote in message
> news:<a8aed4.0306081930.45e2b27_at_posting.google.com>...
> > > Hi,
> > >
> > > I am not sure If James Morle is part of this list but i know people
> > > how are on par with him and who answer here .. so i Shoot ...
> > >
> > > In His Book in Scaling Oracle 8i in Page Number 278. [ He is tlaking
> > > here about the Cursor Head and Cursor Body and how Bind Variables are
> > > handled]
> > > <Quote>
> > > The Optimizer Plan can also change between executions, owing to the
> > > existence of histograms for the value for a column. If a values is
> > > supplied that can be approached in a more effeciaent way as a Reult of
> > > cardinality, then a new plan is created and put into a new body ....
> > > </Quote>
> > >
> > > All this while i was thinking that Bind variables dont use Histograms
> > > and i here i find a diffrent stmt. Since if we use Hard Coded Values
> > > then there will be two Cursor Heads since the Hash Value for them will
> > > be diffrent and hence we will use Histograms since the Optimizer Knows
> > > what value we have passed.
> > >
> > > Pls Enlighten.
> > >
> > > Thanks.
> > >
> > > Regards,
> > > Ganesh R
> >
> >
> > There are probably some misunderstandings somewhere...
> >
> > Histograms helps the optimizer determine or estimate the selectivity
> > of the predicate in the queries. When bind variables are used in the
> > predicates, histograms will not be used and selectivity are estimated
> > as follows:
> >
> > 1. For equality predicates : 1/NDV (# of distinct values)
> > 2. For range predicates : 5%
> > 3. For predicates with like operator : 25%
> >
> >
> > - Jusung Yang
>
>
Received on Mon Jun 09 2003 - 22:20:19 CDT