RSS Feed

Method R Discussion Group

I have heard that I shouldn't gather stats in a production environment for fear of alter explain plans.  Is this true, and if so, when do I gather stats?

You are missing out on some good SQL perforamance if you adopt this strategy. As your data grows and changes, the plans that you have now may perform poorly and you may not see the impact because of the capacity head room available.

The best things to do is to look at our Laredo Product and know ahead of time what explain plans of SQL statements are going to change and decide whether it is a good thing or not.

My impulse after reading the original question was, "Why else would you gather stats unless you wanted to change some plans? ...That's what gathering stats is FOR!"

As I talk to people, I feel like the dominant attitude toward the Oracle query optimizer is resentment that it doesn't just pick a plan and leave it along. That's of course what RBO did--it considered only your SQL and your schema STRUCTURE and decided upon a plan. If you didn't change your SQL or your schema structure (or your optimizer!), you could rest assured that your plans wouldn't change.

But that's not a very smart strategy when you consider that the plan you SHOULD be using DOES change sometimes when the character of your data changes. And that's what the CBO takes into account. CBO can hop from one plan one day to another plan the next day if the character of your data (as expressed through the values you've stored in the statistics about that data) changes.

So it's funny: people need for their plans to change when a plan change would give better performance; but a lot of people want for their plans never to change. Contradictory requirements, when you think about it that way.

The trick is to understand more about how your CBO works, so that you can exploit the good plan changes while avoiding the bad ones.

I believe the reason why people refuse to gather new statistics on a regular basis is the fact that the CBO sometimes produces very inefficient plans with the new statistics.
The situation is: you gather new statistics and you *hope* that this will produce better plans, but there is always a risk that some sql's will perform much worse than before. The problem is: you don't know which sql's this will be, and you don't know when they will be executed for the first time.
The situation gets worse if you have automatic gathering enabled: then Oracle will from time to time gather new statistics during the maintenance period at night. In some cases you will get support calls at 3 am because of the new stats.
So far I have not seen a satisfying solution for this dilemma. That's why we gather new statistics in critical production systems only after having identified a performance problem. We are then prepared to revert to the old statistics in cas of problems. That means: we prefer to run on less than optimal, but acceptable, performance instead of having the best performance but taking the risk of a dramatic performance problem.