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: Wed, 21 Jul 2004 16:13:56 -0400
Message-ID: <AFF54B073FF15849B53E32E67EE860763A7D24@ENHBGPRI11.PA.LCL>

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 =
	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

-----Original Message-----

[]On Behalf Of Wolfgang Breitling Sent: Wednesday, July 21, 2004 3:40 PM
Subject: Re: Creating Histograms

You create histograms on individual columns with gather_table_stats.=20 Gathering histograms is a very selective process - both, on which = column(s)=20
and with what number of buckets. It is not something you ought to do = with a=20
broad stroke like gather_schema_stats. One size (pun intended) certainly =

does not fit all.

Once you gathered the histograms in this way for the columns where it=20 benefits performance, you can use method_opt=3D>'for all columns size = repeat'=20
in gather_schema_stats to re-gather the histograms - according to the=20 documentation, I have not verified that myself.

All that provided you insist on doing the regular gather_schema_stats=20 russian roulette.

The values for all (analyzed) tables in dba_histograms are OK. Capturing =

min and max column values can be viewed as a 1-bucket histogram (bounded = by=20
lowest and highest column value), which is what the default=20 method_opt=3D>'for all columns size 1' also implies - a histogram of = size one=20
for all columns..

PS. An easy way to find which columns are used in predicates, and one = which=20
Oracle uses when you use method_opt=3D>'... size auto', is to query=20 sys.col_usage$.


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 Wed Jul 21 2004 - 15:17:48 CDT

Original text of this message