Execution plan flipping randomly after re-gathering statistics on a unchanged table

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

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