Yes, but the cardinality data in user_tab_columns and user_ind_columns *is*
used.
Regards
--
Sybrand Bakker
Senior Oracle DBA
to reply remove '-verwijderdit' from my e-mail address
"SAP BASIS Consultant" <basis_consultant_at_hotmail.com> wrote in message
news:dd2036f3.0306231032.2468e7e3_at_posting.google.com...
> 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 - 14:19:43 CDT
