Re: Vagaries of the CBO: Out-of-Range Predicates

Anand kindly pointed out to me that he was after density and not cardinality/selectivity calculations for the case of bind variables. So my answer pointed at a useful discussion of the wrong but somewhat related thing.

Niall

On 12/18/06, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
>
> It depends :( On version and type of query (and column stats). For an
> RTFB look at pages 50ff of Jonathan's CBO book (I assume that is the Bible
> to which you refer).
>
> I do like your introduction with 'this is a seemingly simple situation'...
>
>
>
> On 12/18/06, Anand Rao <panandrao_at_gmail.com> wrote:
> >
> > adding one more question,
> >
> > 3) How does CBO calculate the density in cases of bind variables when
> > the value is out of range?
> >
> > or is this a RTFM / RTFB ... ??
> >
> > thanks
> > anand
> >
> > On 18/12/06, Anand Rao <panandrao_at_gmail.com > wrote:
> > >
> > > Hi,
> > >
> > > Here is a seemingly simple situation. Database is 10.2.0.1.0 on AIX
> > > 5.3.
> > >
> > > i have a query. Join of two tables (parent/child). CBO chooses the
> > > wrong index into the child table.
> > >
> > > Parent table had 4 distinct tables.
> > > Child table has 1 distinct value.
> > >
> > > The first execution of this query passes a value that is not present
> > > (out-of-range value) in the child table.
> > >
> > > The next execution passes a value that exists in the table, and as
> > > expected, oracle doesn't re-optimise the query - thanks to Shared SQL and
> > > probably Bind Peeking too.
> > >
> > > there are no histograms on the table. only normal stats.
> > >
> > > I found Bug No: 3663924, 5140413 which are the closest matches.
> > >
> > > It says i need histograms for the column AND also setting the
> > > following event (if it is 9.2.x database),
> > >
> > > SQL> alter session set events '38071 trace name context forever';
> > >
> > > <https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=3663924&p_database_id=BUG>
> > > But my table has an NDV of 1, so an histogram is not going to help.
> > >
> > > So, i hacked the NDV to be greater than 2 and got CBO to use the right
> > > index.
> > >
> > > My question is,
> > >
> > > 1) Is hacking the NDV the only way out in this situation where my NDV
> > > is 1 and there are no histograms?
> > >
> > > Reading Jonathan's CBO bible, i find some good pointers spread across
> > > chapter 4 and 7. still pouring over it...
> > >
> > > Oracle has some smart capability to compare the bind variables with
> > > those used to determine the execution plan. If the bind variables are too
> > > different, a new plan is calculated.
> > >
> > > 2) How does the CBO determine that a bind variable is different to
> > > another for the same query? is it just the datatype and size? there has to
> > > be something more than that.
> > >
> > > thanks,
> > > anand
> > >
> >
> >
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l