Re: Very Strange Query Access Plan
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
- To: Thomas.Mercadante@xxxxxxxxxxxxxxxxx
- Date: Wed, 3 Oct 2007 21:41:30 -0700
Tom,
> Thanks to Alvaro Jose Fernandez & Ric Van Dyke, this is solved. The DENSITY
> and CLUSTER FACTOR values in the user_tab_columns for my database table had
> bad values. These values are calculated by the DBMS_STATS package. I
> manually set these to a much lower figure and my problem went away.
Keep in mind that collecting histograms on a column can affect the
DENSITY. i.e. when a histogram exists, DENSITY != 1/NDV and that can
cause lots of issues. I think the whole thing is explained in a paper
by Wolfgang (or Alberto or Jonathan - I forgot who!)
In this case, I am guessing that this occurred because you collected
stats using DBMS_STATS with the METHOD_OPT=>'FOR ALL INDEXED COLUMNS
SIZE SKEWONLY' option, and the sample suddenly showed that there was a
skew? (Guessing with apologies to the BAAG party!)
> I'm still trying to determine what my next steps are. One definite step is
> to stop gathering stats for awhile!
As per Dave Ensor as quoted by Wolfgang, "It is only safe to gather
statistics when to do so will make no difference". It is however,
difficult to NOT gather stats. The safest is to backup existing stats
before gathering them, and that is something 10g does automatically.
--
John Kanagaraj <><
DB Soft Inc
http://www.linkedin.com/in/johnkanagaraj
http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
