METHOD_OPT

Here is a poll prompted by today twitter talks on the default METHOD_OPT value (which is ‘for all columns size auto’ since 10g) and automatic statistics gathering job (auto-task since 11g). CBO development team suggests to use the default job to gather statistics with default options. Do you use the job exactly this way with real production databases? I’m very interested to see some numbers. Please share the poll link http://poll.fm/3n7pn so that more people vote. Thanks.

About these ads

12 Comments

  1. Posted April 10, 2012 at 11:28 | Permalink | Reply

    Given a sufficient amount of time and a variety of systems being worked on, I would expect even the laziest DBA to have all three methods. On low priority systems the defaults are fine in many cases. Some systems need a little customizing and some edge cases are so extreme that defaults really don’t work well.

    Perhaps the poll should have mentioned a specific version also. I would expect far fewer people to be using the defaults in 10g compared to 11g.

    Cheers

    Tim…

    • Posted April 10, 2012 at 11:43 | Permalink | Reply

      Hi Tim

      thank you for your feedback.

      I would expect far fewer people to be using the defaults in 10g compared to 11g.

      Very good point. I agree 11g defaults are different especially in account to SAMPLE_SIZE.

      Another thing I didn’t thought about thoroughly is how “custom” custom stats management could be. It could be just a matter of time frame (so defaults are still there, but job runs more frequently, for example) – and for me this case is equal to the first poll option. What I mean by “custom stats management”: not gathering statistics (plus maybe copying (sub)partition stats); custom statistics manipulation; DBMS_STATS calls integrated in the application/maintenance scripts – all the ways that do not rely on the job.

      • CJ
        Posted April 10, 2012 at 15:38 | Permalink | Reply

        It is probably worth clarifying whether this includes stats gathering on data dictionary and fixed tables or not.

        I would leave the default job gather the above stats, while creating a custom framework for stats on application objects.

        • Posted April 11, 2012 at 13:58 | Permalink | Reply

          Hi

          Thanks for comment. As far as I know automatic job does not gather fixed objects statistics – but I might be wrong and have to check Maria’s paper :)

  2. Posted April 10, 2012 at 13:55 | Permalink | Reply

    The default is ok with my customer environment in 10gR2 and 11gR1, 11gR2. In 10gR2 we had to increase degault percentage to 30 and had to do some custom statistics gathering for a few tables but most are ok

  3. Posted April 10, 2012 at 16:18 | Permalink | Reply

    I haven’t worked with 11g much, but 10g default options seem adequate in most cases. Although I did have one major incident because of method_opt=>’…size auto’ (a histogram was generated where not appropriate) but I suspect it would have been many more if I were using method_opt=>’… size 1′.

    • Posted April 11, 2012 at 14:07 | Permalink | Reply

      Thank you for your feedback.

      but I suspect it would have been many more if I were using method_opt=>’… size 1′.

      Have you tried it though?

  4. Posted April 10, 2012 at 19:45 | Permalink | Reply

    I’d be curious how the results would change if the poll differentiated between 10g and 11g for this question. Thanks!

    • Posted April 11, 2012 at 14:10 | Permalink | Reply

      Hi Kellyn

      me too :) but at the time of creating the poll I was thinking about the METHOD_OPT mostly, and it’s not much difference from user perspective in this area. You either get histograms when Oracle thinks it is needed or you don’t trust Oracle and use your brains instead.

  5. Posted April 11, 2012 at 18:58 | Permalink | Reply

    Hi Timur,
    I would prefer to use my brain, least as long as it works… :-)
    I’m very curious for this interesting poll.
    I will wait the result…:-)
    thanks
    Ciao

    Alberto

3 Trackbacks

  1. [...] http://timurakhmadeev.wordpress.com/2012/04/09/method_opt/ Share this:TwitterLike this:LikeBe the first to like this post. [...]

  2. By Stats Collection « Oracle Scratchpad on April 10, 2012 at 11:13

    [...] is just a temporary note to point you to a poll set up by Timur Akhmadeev to get an idea of how people are handling stats collection in newer versions of [...]

  3. By Statistics poll « Timur Akhmadeev's blog on April 16, 2012 at 23:55

    [...] 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. [...]

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 291 other followers