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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling Sent: Wednesday, July 21, 2004 5:19 PM To: oracle-l@xxxxxxxxxxxxx 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 ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------