Do execution plans stay the same if optimizer statistics are not regathered?

Recently, I’ve again encountered the belief that if optimizer statistics are not regathered, SQL execution plans won’t change. To clarify what I mean: I am talking about the case where statistics are gathered once. All the objects in the database have statistics, but the idea is to not gather them again in the hope that plans will remain fixed.

However, if data changes in the database, this strategy is very likely to fail.

Why?

Imagine that you have a table T1 where COL1 contains values 1 to 1000. If you gather statistics, the optimizer will know that the minimum value for the column is 1 and the maximum value is 1000.

Now, we’ll query the table, setting “bind1” to the value 500:

select * from t1 where col1 = :bind1;   /* bind value is 500 */

The optimizer knows that the value 500 is within the range of values in the database and can use this fact with the number of distinct values and/or a histogram on COL1 to generate an accurate cardinality estimate. If the cardinality estimate is accurate, you are likely to get optimal or near-optimal query performance.

Next, insert some new rows into T1 the where COL1 has the value 2000. Naturally, the database application will want to retrieve these rows at some point, so it will set “bind1” to the value 2000:

select * from t1 where col1 = :bind1;  /* bind value is 2000 */

Now here’s the snag. If statistics have not been regathered, the optimzier will continue to believe that the maximum value of COL1 is 1000. It is therefore natural for the optimizer to assume that the query above (where bind1=2000) will return zero or hardly any rows. If this estimate is low compared to the estimate the optimizer calculates for the value 500, the optimizer might choose a different plan. It’s worse than that; if searching COL1 for 2000 returns many rows rather than hardly any, the plan chosen could perform very poorly (since it was based on matching a few rows). In other words, a failure to gather statistics can cause cardinality misestimates and induce plan changes that deliver poor performance!

In summary, when you see a plan change and think that “nothing has changed,” be careful to consider whether that’s true in the context of the data in the database and the values your SQL is using in predicates to query it. If you want to control SQL execution plans, use a feature that’s intended to do that: SQL plan management.

Check out this demo, where a plan changes even though statistics are not regathered.