Re: Analyze gather statistics automated in 10g?
- From: John Kanagaraj <john.kanagaraj@xxxxxxxxx>
- To: cemail_219@xxxxxxxxxxx
- Date: Mon, 30 Mar 2009 22:06:49 -0700
Dex,
> 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 <><
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:
