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

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_at_db-nemec.com> 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
--
http://www.freelists.org/webpage/oracle-l