Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Creating Histograms

RE: Creating Histograms

From: Freeman, Donald <>
Date: Thu, 22 Jul 2004 10:37:21 -0400
Message-ID: <AFF54B073FF15849B53E32E67EE860760144EFB3@ENHBGPRI11.PA.LCL>

OK, I understand your point about gathering on schedule. I'm moving into = taking over a turn-key contractor developed system. We are doing = stats/computed every day. We only add, at most, a few thousand records = a day. This is much, much less than 10%. We converted a few million = records, about five years worth of records, from four or five other = public health databases but our daily accrual is relatively small. I = probably wouldn't have to run stats once in a month. We also don't = collect system stats. I'm hoping to get enough information here to 'have = a meeting' and get all of that changed, the method and rate of = collection. I think I want to go to monitoring/stale and that our = performance will improve a lot. Right off the top we'll save a couple = hours a day of process time that we collect stats.

-----Original Message-----
[]On Behalf Of Wolfgang Breitling Sent: Wednesday, July 21, 2004 5:19 PM
Subject: RE: Creating Histograms

The russian roulette remark does not have anything to do with the = gathering=20
of histograms but with the widespread practice of gathering statistics = on a=20
schedule - every weekend, or every night, or whatever, without having=20 established a need for it. The new statistics can have unexpected = negative=20
effects on performance (nobody would ever complain about unexpected=20 positive effects), hence my term russian roulette. If you are gathering=20 statistics on a schedule - by whatever method - at least back up the=20 current statistics first ( and have a grandfather-father-son hierarchy = of=20
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: =3D20
> I've identified my target columns that are being used as =
> Next, create the histograms on these columns by using =3D
> The number of buckets I need is determined by the number of=20
> distinct =3D
>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 =3D
>back to one bucket min/max value band the next time I run =3D
>gather_schema_stats, right?=3D20
>But, now you've scared me ~=3D20
> I could be mistaken but 'russian roulette' usually refers to a =
bad =3D
>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=20
>that =3D
>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, =3D
>"That's not the way I would do it." =3D20

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


Wolfgang Breitling
Centrex Consulting Corporation

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Thu Jul 22 2004 - 09:36:07 CDT

Original text of this message