Re: Scaling Oracle 8i - Histograms and Bind Variables

Hello,

I am also curious about this. A couple of years ago, I was working with an SAP system running on Oracle, either v8.0.6 or 8i (I don't remember which one, and don't have the notes here).

There was one query which returned 0 rows and took up some 50% of CPU time and disk reads.

The query was using an FTS despite the fact that an index existed that would have been perfect. The problem was that the index had no selectivity, so it was not chosen by the CBO.

We ended up creating a histogram, the index was selected, and the query ended up taking some 99.9% fewer resources to execute.

The question I have is that, according to my understanding of Oracle, the histogram should not worked since Oracle, when running SAP, uses bind variables for the queries. I thought that histograms do not work with bind variables until 9i (Luckily, I did not 'know' this at the time).

Thus, how did the histograms work if the query contained bind variables in Oracle v8.x?

Thanks,
SAP BASIS Consultant

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<T7cFa.325$Bm2.67_at_news02.roc.ny.frontiernet.net>...
> ... 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 23 2003 - 13:32:13 CDT