| 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, 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
-----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 3:40 PM
To: oracle-l_at_freelists.org
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$.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com=20
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |