RE: Creating Histograms
- From: "Freeman, Donald" <dofreeman@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Jul 2004 16:13:56 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: Wednesday, July 21, 2004 3:40 PM
To: oracle-l@xxxxxxxxxxxxx
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
----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
