Re: Very Strange Query Access Plan

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 **
--
http://www.freelists.org/webpage/oracle-l