Re: Analyze gather statistics automated in 10g?

From: John Kanagaraj <>
Date: Mon, 30 Mar 2009 22:06:49 -0700
Message-ID: <>


> After having read some documentation, I am still confused.  Is the process
> of running analyze gathering statistics automated in 10g (specifically

The problem is not that 10g automated the gathering of statistics - it is that Oracle changed the defaults for DBMS_STATS in 10g, and that is where the problem started... For example, the METHOD_OPT changed from "FOR ALL COLUMNS SIZE 1" to "FOR ALL COLUMNS SIZE AUTO". In other words, if you did not specify METHOD_OPT in 10g, you might end up collecting histograms on columns which Oracle thinks has skewed data. In addition, Cursor Invalidation on stats collection and Estimation percentages has also changed. In a 9i -> 10g upgrade, this can thus throw nasty surprises.

In other words, if you previously used to run DBMS_STATS without specifying parameters in 9i, and continue to use default parameters in 10g, you might end up with some issues since (a) the Estimate percent would have previously been 100% and now reduces to a value determined by Oracle (b) Histograms now appear where you did not have them before - and bucket sizes would vary depending on data as well

Hope this helps,

John Kanagaraj <>< (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 **
Received on Tue Mar 31 2009 - 00:06:49 CDT

Original text of this message