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'.
>
> To my knowledge there has been no error messages when analyzed manually nor
> are there error messages in the logs which say the analyze didn't finish.
> From what I can see, however, the table analyze either hasn't fully
> completed or somehow the statistics from the analyze are not being stored in
> our database.
>
> Would anybody have any idea why this could be happening?

How exactly did you gather statistics, what were dbms_stats.gather_table_stats parameters? Can it be you got stats at partitions level only, not global?

Regards,
Igor Received on Thu Oct 05 2006 - 01:22:48 CDT