2. Local Indexes - Stats on the index 'header'’

From investigation of a problem I have come to the following conclusion. The standard dbms_gather_stats package used by Oracle 10g as the out-of-the-box method for keeping stats current was NOT regenerating the stats for the ‘header’ or global part of local indexes on partitioned tables.

i.e. for local indexes you can generate stats for each partition of the index AND also for the index itself (the header).

The effect of these out of date stats was that an update statement that had been working well for some time suddenly ran so slowly it effectively killed the live system. It turned out the Cost based Optimizer had decided to use an incorrect index on a table with 130 Millions rows in it. I regenerated the index headers stats for the incorrect index and the index it should have been using. The optimizer switched to using a (better but still incorrect) third index. Once I had regenerated the stats for this third index the optimizer started using the correct index.

Conclusion we cannot rely just on dbms_gather_stats to keep the stats up to date. I have written a packaged procedure to gather the stats for N indexes a day starting with the most out of date. In the absence of anything better, I would suggest we run this procedure on a daily basis on each database.