Statistics poll

Thanks to everyone for spreading the link and participating in the poll on statistics. As it was pointed out in the comments, the poll isn’t very specific, I know. My point was to get an idea how many people use the defaults (I considered Oracle version 11g) for stats collection, how many do a very simple and reasonable tweak to get more stable execution plans, and how many people prefer custom statistics management. Well, I think the results will not change much and here are as following (326 votes at the time of writing):

  • 42% use the defaults (if you truncate the value. I can’t put something other than 42 here, sorry guys :-))
  • 16% don’t like unexpected histograms :-D and prefer to take situation under control
  • OMG another 42% (since it’s not Russian elections it has to be 100% in total, you know) use custom statistics management

Nice! Now we know that the defaults are suited to less than a half of Oracle installations. And my opinion is there would be more than a half using the defaults, should Oracle decided to switch the default METHOD_OPT value to ‘FOR ALL COLUMNS SIZE REPEAT’. Consider Tim Hall’s reply “On low priority systems the defaults are fine in many cases”, and think about why defaults are fine for such systems? My humble opinion is: nobody cares about low priority systems. So it doesn’t really matter if it’s reliable or not, whether there are histograms present or not, whether a system is stable or not – it’s low priority anyway. It will survive somehow. For me it means it’s not too much of a problem to go back to the future and modify default METHOD_OPT so that no histograms are gathered initially by default – as all should do manually now /me thinks.
PS. I wouldn’t mind switching GATHER_TEMP back to the FALSE and returning it to the documentation too, as it was in 9i.

About these ads

4 Comments

  1. Posted April 17, 2012 at 01:37 | Permalink | Reply

    “Now we know that the defaults are suited to less than a half of Oracle installations”
    This might be jumping to conclusions. The poll does not contain version numbers with each vote, and I suspect that the 11g defaults would work for far more installations than the 10g ones did. Assuming the defaults don’t work just because an installation is not using them is also a bit of a reach. Nonetheless, interesting data points.

    • Posted April 17, 2012 at 11:33 | Permalink | Reply

      Hi Greg

      thanks for your comment

      This might be jumping to conclusions. The poll does not contain version numbers with each vote, and I suspect that the 11g defaults would work for far more installations than the 10g ones did

      I don’t think this is jumping to conclusion – it’s what poll results report. The fact that defaults would work better in 11g doesn’t change a thing if they are not used. My opinion is if somebody doesn’t use defaults in 10g, chances are high they wouldn’t use them in 11g too. I’m talking from point of view of a big software vendor that uses DBMS/AS as a tool and have to make software work properly, so I might be wrong – my view may be biased towards not relying on Oracle’s out-of-the-box automatic features. Plus I’ve seen far less installations than you.

      • Posted April 17, 2012 at 21:03 | Permalink | Reply

        First, I guess my issue is with wording — “suited” is not equivalent with “poll responders using”, hence my comment about jumping to conclusions. I’ve seen way too many instances of moving to the defaults helping things to assume it’s all being done for valid reasons. The argument of “we’ve been doing it this way since version 7″ comes up a bit too often. :)

        Second, Oracle has worked to address the issues with 10g stats defaults (mainly sample sizes and bind peeking), so the messaging (and my practice) is to re-evaluate and reconsider when going to 11g. Given your poll, there is no way to detect trend to see if that is happening.

        Again, I think it is important to point out that Oracle does not expect the defaults to work for all cases, but there certainly should be more cases that the defaults work for in 11g, than 10g, and it should continue to be that way with future releases.

        If things are not working, then feedback into the product teams is necessary to help advance the product.

        • Posted April 18, 2012 at 12:20 | Permalink | Reply

          Greg

          now I understand that my wording was little bit confusing, thanks. By “suited” I mean they are using them and I consider all parties are happy with what they are using.

          I’ve seen way too many instances of moving to the defaults helping things to assume it’s all being done for valid reasons.

          Lucky you :)

          Oracle has worked to address the issues with 10g stats defaults (mainly sample sizes and bind peeking), so the messaging (and my practice) is to re-evaluate and reconsider when going to 11g.

          I agree that auto sample size in 11g rocks – one of the best improvements in 11g overall I think. However I don’t think bind peeking issues have been worked out on the same quality level. In my view they just help to workaround root cause (sudden histograms), not fix it. Sure they will help in some other cases too, but they are not interesting to me. My opinion is: it’s better not to have a histogram in place, than have some, and Oracle’s default behavior is exactly opposite.

          If things are not working, then feedback into the product teams is necessary to help advance the product.

          Unfortunately many people don’t know that Oracle is using bugfeedback-driven development, so many cases remain unexposed.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 287 other followers