Under 10.2.0.4 the plan is the same each and every time. I even used no_invalidate=>false to make sure the plan is invalidated. This may have been due to a bug and has since been fixed, after all, you used 10.2.0.1 which is several years old. It is also worth mentioning that dbms_stats.auto_sample_size uses different algorithms in 10g and 11g so that is an extra variable that has changed. I would assert this is more of an optimizer costing issue vs. a stats collection issue, but it could be the latter. On Mon, Dec 1, 2008 at 1:46 PM, jaromir nemec <jaromir@xxxxxxxxxxxx> wrote: > Hi list, > > while preparing some different theme I found a select statement that changes > the execution plan randomly (between NL and HJ) after re-gathering the > statistics. The table remains unchanged; the select has no time dependencies > (such as sysdate); no parameters changed. This is 10.2. > The cause of the randomness is that the literal value used in the access > predicate (x = <some value>) is very rare in the table and therefore > sometimes is considered in the sample used to gather statistics (default > estimate_percent is used) and sometimes not. This leads absence or presence > of this value in the (frequency) histogram and to a big difference in the > estimation of the selectivity of the access predicate. The different > cardinality leads finally to the different execution plan. > There is some more discussion and a set up script in > http://www.db-nemec.com/flip/FlippingExecutionPlan.html > I don't thing this is a big issue – the example is very synthetic. Above all > in practise the statistics are not re-gathered for unchanged tables. The > good question is, if it is safe to use a default sample size or if some care > should be taken in special cases. > Any experience or comments? > > Interesting too is that I was not able to reproduce this behaviour in 11.1. > The flipping in histogram was the same. Apparently a modified algorithm to > compute the cardinality of values missing in (frequency) histogram was > introduced. This makes the difference in cardinality smaller and the plan > remains stable. > > Regards, > > Jaromir D.B. Nemec -- Regards, Greg Rahn http://structureddata.org -- //www.freelists.org/webpage/oracle-l