RE: Creating Histograms

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 21 Jul 2004 15:18:51 -0600
Message-Id: <6.1.0.6.2.20040721150743.02ec01d8@pop.centrexcc.com>

The russian roulette remark does not have anything to do with the gathering of histograms but with the widespread practice of gathering statistics on a schedule - every weekend, or every night, or whatever, without having established a need for it. The new statistics can have unexpected negative effects on performance (nobody would ever complain about unexpected positive effects), hence my term russian roulette. If you are gathering statistics on a schedule - by whatever method - at least back up the current statistics first ( and have a grandfather-father-son hierarchy of saved statistics ) so that you can restore them if necessary.

Why the restriction to value based histograms (aka frequency histograms in 9i)?

At 02:13 PM 7/21/2004, you wrote:
>Ok, so let's say I've done all that: =20
>
> I've identified my target columns that are being used as predicates.
> Next, create the histograms on these columns by using =
>gather_table_stats
> The number of buckets I need is determined by the number of
> distinct =
>values in the column. I want to have a 'value' histogram instead of =
>'height' so I need at least as many buckets as I have distinct values..
> I now have to use method_opt repeat because if I don't it will
> default =
>back to one bucket min/max value band the next time I run =
>gather_schema_stats, right?=20
>
>But, now you've scared me ~=20
>
> I could be mistaken but 'russian roulette' usually refers to a bad =
>thing<g> but maybe I'm doing it wrong. I'm not aware of any bad things =
>related to the use of gather_schema_stats. Would you elucidate on
>that =
>comment a little? Are there other, better, options for maintaining the =
>histograms once they are created?
>
>I'm thinking you've politely answered my questions but you are thinking, =
>"That's not the way I would do it." =20

That's essentially how I do it, except for the subsequent gather_schema_stats bit. I don't in general analyze tables very often and certainly not the entire schema, only individual tables and only if it is necessary. Otherwise I practice "plan stability" meaning "if the statistics don't change, the plans won't".

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jul 21 2004 - 16:16:10 CDT