Re: 9.2.0.6 Optimizer issue

Did the application upgrade touch the tables used in this query such that they would have been dropped and recreated? This would wipe out the predicate usage history on which "size auto" depends when deciding which columns to collect histograms on.

You can validate that by checking table sys.col_usage$. Or simply compare the statistics from before (you DO backup your statistics, don't you) to now and see if some histograms went missing.

Ignizio, Richard wrote:
> Here is the oddity/question, Last weekend we had an application upgrade =
> and I had to re-analyzed the database. The difference in the way I =
> gathered the stats changed as well for the method_opt I changed 'FOR ALL =
> INDEXED COLUMNS SIZE SKEWONLY' to 'FOR ALL INDEXED COLUMNS SIZE AUTO'. =
> Last week the report ran in less than 15 minutes. Would this change be =
> the make that much of a difference?
>
> I normally do not analyze the database often because the database is =
> running optimally and there are no Huge changes to the database.=20
>
> 9.2.0.6 32-bit
> Sun Solaris 2.8
>
>
> Rich
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l