Oracle Analyze Question

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Wed, 04 Oct 2006 21:59:18 GMT
Message-ID: <W8WUg.11383$6S3.10721@newssvr25.news.prodigy.net>

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? Received on Wed Oct 04 2006 - 16:59:18 CDT