| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Creating Histograms
I'm trying to improve my knowledge of tuning and the performance of my =
9i Ver 9.2.0.4 data warehouse by creating histograms on columns used in =
where clauses with skewed data values. I have extracted the columns =
included in where clauses, determined the distinct number of values and =
count using a "select distinct (value), count(*), group by statement" =
and think I have identified which are some good candidates for =
histograms. The documentation and articles about this issue I have read =
make it clear that histograms should only be used on columns being used, =
having skewed data. However, the dbms_stats.gather_schema_stats =
procedure with method_opt seems to create histograms whether the column =
is being used or the data is skewed. The 'gather_schema_stats' procedure =
is the only one demonstrated and seems to violate the rule. The =
'gather_table_stats" procedure seems like it would give you more =
selectivity. I'm having a little trouble with this topic. When I =
checked, there are values for all tables in dba_histograms although I =
have never run schema_stats with the method_opt set to anything but =
default. =20
Am I on the right track here? How do I use this feature without = violating the 'rules?' I'm guessing if I run it and screw up my = performance I can reset my histograms by rerunning dbms_stats with the = default method_opt. Any advice useful or otherwise is welcome.
Thanks,
Don Freeman
Database Administrator 1
Pennsylvania Dept of Health
Bureau of Information Technology
 |
 |