Statistics—How and When
When I read Chen Shapira’s latest whodunit “Daylight Saving Time Causes Performance Issues,” I remembered an installment of “Ask the Oracles” in the August 2007 issue of the NoCOUG Journal called “Statistics—How and When.” Here are some quotes from that issue.
“I have advised many customers to stop analyzing, thereby creating a more stable environment overnight.”—Mogens Norgaard
“Oh, and by the way, could you please stop gathering statistics constantly? I don’t know much about databases, but I do think I know the following: small tables tend to stay small, large tables tend to stay large, unique indexes have a tendency to stay unique, and non-unique indexes often stay non-unique.”—Dave Ensor as remembered by Mogens Norgaard
“Monitor the changes in execution plans and/or performance for the individual SQL statements … and perhaps as a consequence re-gather stats. That way, you’d leave stuff alone that works very well, thank you, and you’d put your efforts into exactly the things that have become worse.”—Mogens Norgaard
“It is my firm belief that most scheduled statistics-gathering jobs do not cause much harm only because (most) changes in the statistics were insignificant as far as the optimizer is concerned—meaning that it was an exercise in futility.”—Wolfgang Breitling
“There are some statistics about your data that can be left unchanged for a long time, possibly forever; there are some statistics that need to be changed periodically; and there are some statistics that need to be changed constantly. … The biggest problem is that you need to understand the data.”—Jonathan Lewis
But my all-time favorite quote about statistics is:
“It astonishes me how many shops prohibit any un-approved production changes and yet re-analyze schema stats weekly. Evidently, they do not understand that the [possible consequence] of schema re-analysis is to change their production SQL execution plans, and they act surprised when performance changes!”—Don Burleson
To fully understand what the authors were trying to convey, you have to read the quotes in context. Please click here to read the full opinions (PDF). Also review the Should we stop analyzing? discussion on the Oracle-L mailing list in December 2003 by Mogens Norgaard, Wolfgang Breitling, Jonathan Lewis, Don Burleson, and others. My one-line summary is that there is no free lunch when it comes to optimizer statistics and the whole issue of SQL performance; start by reading the chapter on managing optimizer statistics in the Performance Tuning Guide. The only free lunch that I’ve ever found is at NoCOUG where a modest annual membership fee of $95 covers attendance at four full-day training events plus breakfast, lunch, snacks, and refreshments plus four issues of the NoCOUG Journal. The quote that ties all the other quotes together is the one attributed to Jonathan Lewis; you have to really understand your data in order to create a strategy that works best for your individual situation. Have I mentioned the great snacks and refreshments at NoCOUG conferences? Our next conference is on Thursday, May 20 at the Oracle Conference Center in Redwood Shores, California.