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

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Mon, 1 Dec 2008 22:46:11 +0100
Message-ID: <005201c953fe$3a73dc80$3d02a8c0@ADLA>

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.


Jaromir D.B. Nemec

Received on Mon Dec 01 2008 - 15:46:11 CST

Original text of this message