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
