| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Creating Histograms
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-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
Sent: Wednesday, July 21, 2004 5:19 PM
To: oracle-l_at_freelists.org
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 =
predicates.
> Next, create the histograms on these columns by using =3D
>gather_table_stats
> 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 =
=3D
>'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 =
=3D
>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 =
=3D
>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".
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com=20
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- 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 Thu Jul 22 2004 - 09:36:07 CDT
 |
 |