RE: Creating Histograms
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 21 Jul 2004 15:18:51 -0600
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
