Re: Oracle Analyze Question

Dereck L. Dietz wrote:
> We're running Oracle Database 10g Enterprise Edition 10.2.0.1.0 - 64 bit
> Production.
>
> We have a very large partitioned table with 22 indexes. Since early
> September the performance against this table has degraded to where queries
> which had run in 40 minutes are taking 4 hours or more to complete if they
> even do.
>
> In the rebuild process the table and it's indexes are analyzed. The table
> has also been manually analyzed a number of times during the month of
> September.
>
> The question/problem I've discovered is that in both the Enterprise Manager
> and when looking in the ALL_TABLES view the last date Oracle has the table
> being analyzed is '31-AUG-2006' even though the indexes show all being
> analyzed last on '03-OCT-2006'.

Did you use dbms_stats to gather table stats on the partitioned table before and then you use analyze later? Analyze after dbms_stats.gather_table_stats will not update a couple of fields in all_tables for a partitioned table, including last_analyzed. A quick test shows that in 9i (9.2.0.5), subsequent dbms_stats can correct this problem, but in 10g (10.1), it does not. (Can anybody test again?) In either case, all_tab_partitions shows the correct info.

Yong Huang Received on Wed Oct 04 2006 - 23:40:15 CDT