Re: 9.2.0.6 Optimizer issue
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: richard.ignizio@xxxxxxxxxx
- Date: Fri, 06 May 2005 09:20:49 -0600
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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
