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