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?

22 indexes?

Global or local?
What type of partitioning (range, hash, list) Is there an EXPLAIN PLAN anywhere in sight? How about a StatsPack or AWR?
How current are system statistics? table stats? index stats?

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group