Execution plan flipping randomly after re-gathering statistics on a unchanged table
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 1 Dec 2008 22:46:11 +0100
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
Other related posts:
