I'd bet regular readers might have guessed I'd never get back to the stats series, particularly given my extremely limited output this year Well, here goes ...
The theme of this post is already covered in the paper and the presentation, so if you've read either of those, then you might want to skip this. While working on the paper I realised that I'd made yet-another-incorrect-assumption about a copy_table_stats bug. I was right about the bug, which was described in an earlier post in the section titled "ORA-03113 / 07445 while copying list partition statistics". To recap, when copying statistics on multiple list subpartitions, Oracle disconnects the session and core dumps whilst copying the stats for the final OTHERS subpartition which is a DEFAULT list subpartition. I incorrectly assumed that this was because the OTHERS subpartition was the last subpartition that I was copying stats for, which seemed to make sense. But, as I was working on the paper, I thought I would have a dig around on My Oracle Support to see if there had been any reports of this bug.
Which is still a bug in 10.2.0.5 and 220.127.116.11. The real issue occurs when you copy the statistics for a DEFAULT list subpartition and I was able to confirm this by changing the order in which I copied the subpartition statistics.
Doug- This discussion was obviously fantastic, however i am still puzzled.
So if I want to have global stats on a table which has partitions and sub-partitions, I think i can use
GRANULARITY = ALL instead of GLOBAL AND PARTITION and wouldn't this will collect global stats on table, partition and sub-partition level ? obviously this is time consuming but I wouldn't want to do sub-partition exchanges and other complicated methods describe here.
Yes, you can definitely use GRANULARITY => ALL to gather all required stats. In fact, you should probably use GRANULARITY=> AUTO to achieve a similar effect with less overhead.
However, most of the time when people are gathering stats on partitioned objects, they don't always have the time to gather all of the stats, particularly the Globals, which take longer to gather as the table grows.
If you have time and resources to gather more extensive stats then feel free!
Part 1 - Default options - GLOBAL AND PARTITION Part 2 - Estimated Global Stats Part 3 - Stats Aggregation Problems I Part 4 - Stats Aggregation Problems II Part 5 - Minimal Stats Aggregation Part 6a - COPY_TABLE_STATS - Intro Part 6b - COPY_TABLE_STATS - Mistakes Part 6c - COPY_TABLE_STATS - Bugs and Patches Part 6d - COPY_TABLE_STATS - A Light-bulb Moment Part 6e - COPY_TABLE_STATS - Bug 10268597
A couple of posts about Incremental Stats confusion
For the avoidance of any doubt, all views expressed here are my own and not those of past or current employers, clients, friends, Oracle Corporation, my Mum or, indeed, Flatcat. If you want to sue someone, I suggest you pick on Tigger, but I hope you have a good lawyer. Frankly, I doubt any of the former agree with my views or would want to be associated with them in any way.