RE: is SIZE AUTO ever wrong? or am I wrong?
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 21 Nov 2009 21:54:23 +0100
Neil,
your question has (at least) two aspects:
1. SIZE AUTO and which columns get histograms applied
Oracle only considers columns used by single-table access predicates for
automatic generation of histograms- columns only used as part of join
predicate as far as I know will not be considered as histogram candidates
2. SIZE AUTO and the generated histogram bucket size
There are cases where the automatically generated histogram bucket size might
be sub-optimal, which means that a column has less than 254 distinct values,
but still a height-balanced histograms gets generated instead of a superior
frequency histogram. You can notice this if the bucket size is smaller than the
number of distinct values of the column. A frequency histogram might help here
to get better estimates.
In general, histograms on join columns not always change the outcome for the
better, even in case of skewed column data. In particular with height-balanced
histograms the results can easily get worse.
For an in-depth discussion, see Alberto Dell'Era's paper:
http://www.adellera.it/investigations/join_over_histograms/index.html
As others have mentioned - can you confirm that the statement performs better
with your suggestions? Run the statement with STATISTICS_LEVEL = ALL and
DBMS_XPLAN.DISPLAY_CURSOR(...'ALLSTATS LAST') option to compare actual and
estimated cardinalities.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the upcoming "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.de/Expert-Oracle-Practices-Database-Administration/dp/1430226684
______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de
--
//www.freelists.org/webpage/oracle-l
Other related posts:
