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

  • From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
  • To: panandrao@xxxxxxxxx
  • Date: Mon, 18 Dec 2006 10:09:15 +0000

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@xxxxxxxxx> 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@xxxxxxxxx> 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

Other related posts: