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

From: Greg Rahn <>
Date: Mon, 1 Dec 2008 16:00:41 -0800
Message-ID: <>

Under 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 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 <> 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
> 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

Greg Rahn
Received on Mon Dec 01 2008 - 18:00:41 CST

Original text of this message