| 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
You create histograms on individual columns with gather_table_stats.
Gathering histograms is a very selective process - both, on which column(s)
and with what number of buckets. It is not something you ought to do with a
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 benefits performance, you can use method_opt=>'for all columns size repeat' in gather_schema_stats to re-gather the histograms - according to the documentation, I have not verified that myself.
All that provided you insist on doing the regular gather_schema_stats 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 lowest and highest column value), which is what the default method_opt=>'for all columns size 1' also implies - a histogram of size one for all columns..
PS. An easy way to find which columns are used in predicates, and one which Oracle uses when you use method_opt=>'... size auto', is to query sys.col_usage$.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
 |
 |