Re: DBMS_STATS
From: jimmyb <jimmybrock_at_gmail.com>
Date: Mon, 28 Sep 2009 15:47:23 -0700 (PDT)
Message-ID: <4ad25c2e-c7a3-49d4-9646-bbda352d7c4e_at_d10g2000yqh.googlegroups.com>
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?
Date: Mon, 28 Sep 2009 15:47:23 -0700 (PDT)
Message-ID: <4ad25c2e-c7a3-49d4-9646-bbda352d7c4e_at_d10g2000yqh.googlegroups.com>
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. Received on Mon Sep 28 2009 - 17:47:23 CDT