Re: DBMS_STATS
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 29 Sep 2009 06:17:02 -0700 (PDT)
Message-ID: <7a0fe3ce-ff93-40e4-a5a2-3b838683f25f_at_g31g2000yqc.googlegroups.com>
On Sep 28, 6:47 pm, jimmyb <jimmybr..._at_gmail.com> wrote:
> On Sep 28, 8:42 am, The Magnet <a..._at_unsu.com> wrote:
>
> > Probably a dumb question here, but we're trying to understand
> > DBMS_STATS and all the parameters and such. What a complicated
> > thing. Too many parameters to understand the effects.
>
> > Anyhow, I read that Oracle 10g generates database stats each night.
> > What kind of stats? Does this mean I do not have to compute schema
> > stats?
>
> Just my 2 cents.
>
> I don't rely on the Oracle job that gathers stats each night. If your
> data does not fall within the "baseline" for that job it will not the
> gather stats you need.
>
> It does not take into account skewed data, nor will it gather stats
> for function-based indexes.
>
> Oracle creates a hidden/virtual column for function-based indexes, so
> to gather stats you will need to use the method_opt 'FOR ALL HIDDEN
> COLUMNS SIZE 1'. Assuming your indexing only one value.
>
> You will need to create a historgram for any skewed data, something
> the Oracle will not do.
>
> There are probably lots of gotchas as well.
Date: Tue, 29 Sep 2009 06:17:02 -0700 (PDT)
Message-ID: <7a0fe3ce-ff93-40e4-a5a2-3b838683f25f_at_g31g2000yqc.googlegroups.com>
On Sep 28, 6:47 pm, jimmyb <jimmybr..._at_gmail.com> wrote:
> On Sep 28, 8:42 am, The Magnet <a..._at_unsu.com> wrote:
>
> > Probably a dumb question here, but we're trying to understand
> > DBMS_STATS and all the parameters and such. What a complicated
> > thing. Too many parameters to understand the effects.
>
> > Anyhow, I read that Oracle 10g generates database stats each night.
> > What kind of stats? Does this mean I do not have to compute schema
> > stats?
>
> Just my 2 cents.
>
> I don't rely on the Oracle job that gathers stats each night. If your
> data does not fall within the "baseline" for that job it will not the
> gather stats you need.
>
> It does not take into account skewed data, nor will it gather stats
> for function-based indexes.
>
> Oracle creates a hidden/virtual column for function-based indexes, so
> to gather stats you will need to use the method_opt 'FOR ALL HIDDEN
> COLUMNS SIZE 1'. Assuming your indexing only one value.
>
> You will need to create a historgram for any skewed data, something
> the Oracle will not do.
>
> There are probably lots of gotchas as well.
With 10g the default is to collect histograms so if you use the default set up you get histograms on your indexed columns.
This is one of the potential issues with upgrading to 10g in that shops that did not use histograms get histograms and for some sites this can lead to performance issues.
11g introduces the ability to override the dbms_stats parameters at the username and table level and retains the overrided values for future use. With 10g you need to lock manually created statistics in place to keep them from being replaced with regenerated statistics created using the default parameters again when next the table qualifies to have the statistics regenerated.
HTH -- Mark D Powell -- Received on Tue Sep 29 2009 - 08:17:02 CDT