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 184.108.40.206. 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.
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
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.