Home > NoCOUG, Oracle, SQL > Statistics—How and When

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.

Deja Vu: Five Dangerous Beliefs About SQL Performance

Categories: NoCOUG, Oracle, SQL
  1. prodlife
    April 4, 2010 at 1:06 pm

    I agree 100%.

    Oracle doesn’t help though – Databases arrive with a statistics gathering job running each night by default.

    In 11g they went one step farther by completely changing the way this job runs and not documenting how to disable it.

  2. April 5, 2010 at 7:36 am

    Hi Iggy,

    >> But my all-time favorite quote about statistics is . . .

    There is a name for it. We call it “Monday Morning Mayhem”!

    Donald K. Burlesson

  3. April 5, 2010 at 7:47 pm

    And the amazing thing is the auto stats gathering is part of the dba2.0 way of being a dba.

    We all know that’s the creme de la creme of being a dba, right?

    More like creme de la creppe…

  4. Iggy Fernandez
    April 6, 2010 at 4:14 pm

    Here’s my own Monday Morning Mayhem (MMM) story. It concerns an Ephipany database for a large French fashion house. One morning the French came in to find database performance in the crapper. The French yelled bloody murder and lots of Monday Morning Quarterbacking (MMQ) ensued.

    Statistics had done us in. It turned out that the database had enjoyed excellent performance for exactly 365 days with stale statistics. But there was a cron job that was timed to go off on a specific day of the year and collect fresh statistics for the entire application schema. My guess is that somebody had set up a statistics gathering job exactly one year previously and had forgotten to disable it afterwards.

  1. July 19, 2010 at 6:41 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: