{"id":1598,"date":"2010-04-22T12:00:00","date_gmt":"2010-04-22T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1598"},"modified":"2010-04-22T12:00:00","modified_gmt":"2010-04-22T12:00:00","slug":"statistics-on-partitioned-tables-part-6b-copy_table_stats-mistakes","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2010\/04\/22\/statistics-on-partitioned-tables-part-6b-copy_table_stats-mistakes\/","title":{"rendered":"Statistics on Partitioned Tables – Part 6b – COPY_TABLE_STATS – Mistakes"},"content":{"rendered":"

Sigh … these posts have become a bit of a mess. <\/p>\n

There are so many different bits and pieces I want to illustrate and I’ve been trying to squeeze them in around normal work. Worse still, because I keep leaving them then coming back to them and re-running tests it’s easy to lose track of where I was, despite using more or less the same test scripts each time (any new scripts tend to be sections of the main test script). I suspect my decision to only pull out the more interesting parts of the output has contributed to the difficulties too, but with around 18.5 thousand lines of output, I decided that was more or less essential.<\/p>\n

It has got so bad that I noticed the other day that there were a couple of significant errors in the last post<\/a> which are easy to miss when you’re looking at detailed output and must be even less obvious if you’re looking at it for the first time. <\/p>\n

The fact no-one said much about these errors reinforces my argument with several bloggers that less people read and truly absorb the more technical stuff than they think. They just pick up the messages they need and take more on trust than you might imagine!
<\/em><\/p><\/blockquote>\n

So what were the errors? Possibly more important, why did they appear? The mistakes are often as instructive as the successes.<\/p>\n

Error 1<\/strong><\/p>\n

This is the tail-end of the subpartition stats at the end of part 5<\/a><\/p>\n

SQL> select\u00a0 table_name, subpartition_name, global_stats, last_analyzed, num_rows\n\u00a0 2\u00a0 from dba_tab_subpartitions\n\u00a0 3\u00a0 where table_name='TEST_TAB1'\n\u00a0 4\u00a0 and table_owner='TESTUSER'\n\u00a0 5\u00a0 order by 1, 2, 4 desc nulls last;\n\nTABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUBPARTITION_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GLO LAST_ANALYZED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUM_ROWS\n------------------------------ ------------------------------ --- -------------------- ----------\n<<snipped>>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n \nTEST_TAB1                      P_20100209_GROT                NO  28-FEB-2010 21:41:47          3\nTEST_TAB1                      P_20100209_HALO                NO  28-FEB-2010 21:41:49          3\nTEST_TAB1                      P_20100209_JUNE                NO  28-FEB-2010 21:41:49          3\nTEST_TAB1                      P_20100209_OTHERS              NO  28-FEB-2010 21:41:50          3\n\n<\/pre>\n
Compared to the supposedly same section produced at the start of part 6a<\/a> :-<\/p>\n
TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUBPARTITION_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GLO LAST_ANALYZED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUM_ROWS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n------------------------------ ------------------------------ --- -------------------- ----------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n<<snipped>>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n\nTEST_TAB1                      P_20100209_GROT                YES 28-MAR-2010 15:38:32          3           \nTEST_TAB1                      P_20100209_HALO                YES 28-MAR-2010 15:38:32          3           \nTEST_TAB1                      P_20100209_JUNE                YES 28-MAR-2010 15:38:32          3           \nTEST_TAB1                      P_20100209_OTHERS              YES 28-MAR-2010 15:38:33          3           \n<\/pre>\n
Spot the difference? Forget the timestamps for now, although I hope it’s clear that :-<\/p>\n
– The stats were gathered at different times
– I really need to get my blogging act together 😉<\/p>\n
Instead, notice that GLOBAL_STATS is set to NO in part 5 and YES in part 6a. How could that happen?<\/p>\n
The first thing to note is that it’s probably not as significant as it first appears because what does it mean for Subpartition stats to be Global when there are no underlying sub-components of a Subpartition? In fact I’d argue that all Subpartition stats are Global by implication but I may be missing something important. (Comments welcome …)<\/p>\n
Instead I’ll focus on how you can manage to get the two different results. The output from part 5 was the result of gathering statistics on a load table (LOAD_TAB1) and then exchanging it with the relevant subpartition of TEST_TAB1 (as shown in part 5). When you do that, the GLOBAL_STATS flag will be set to NO. <\/p>\n
If I take the alternate approach of exchanging LOAD_TAB1 with the subpartition of TEST_TAB1 and only then<\/em> gathering statistics on the subpartition of TEST_TAB1 then GLOBAL_STATS will be YES for that subpartition. That’s the most obvious reason I can think of for the discrepancy but I can’t be certain because the log files that I took the output from are history now.<\/p>\n
At some point when ripping parts of a master script to run in isolation for each blog post I’ve changed the stats gathering approach from gather-then-exchange to exchange-then-gather. The output shown in part 5 was correct so I’ve updated part 6a to reflect that and added a note.<\/p>\n
Error 2
<\/strong>
I think this one is worse, because it’s down to me mixing up some pastes because the original results looked wrong when they were, in fact right. It’s extremely rare for me to edit results and I regret doing it here. Whenever you start tampering with the evidence, you’re asking for trouble!<\/p>\n
When I’d been pasting in the final example output, showing that subpartition stats had been copied for the new P_20100210_GROT subpartition, I saw another example when the subpartition stats hadn’t<\/em> been copied, so I decided I was mistaken and fixed the post. But the original was correct so I’ve put it back the way it should be and added a further note.<\/p>\n
If you weren’t confused already, you have my permission to be utterly confused now 😉<\/p>\n
Summary<\/strong><\/p>\n
On a more serious note, let’s recap what I’m trying to do here and what does and doesn’t work.<\/p>\n
I’ve added a new partition for the 20100210 reporting date and I tried to copy the partition and subpartition stats from the previous partition (P_20100209) to the new partition. I attempted a Partition-level copy<\/em> <\/p>\n
SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', \n                                      srcpartname => 'P_20100209', dstpartname => 'P_20100210');\n\nPL\/SQL procedure successfully completed.<\/pre>\n
in the expectation that DBMS_STATS would copy the partition stats as well as all of the subpartition stats from the P_20100209 partition. I’ll repeat how the stats looked here …<\/p>\n
SQL> select\u00a0 table_name, global_stats, last_analyzed, num_rows\n\u00a0 2\u00a0 from dba_tables\n\u00a0 3\u00a0 where table_name='TEST_TAB1'\n\u00a0 4\u00a0 and owner='TESTUSER'\n\u00a0 5\u00a0 order by 1, 2, 4 desc nulls last;\n\nTABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GLO LAST_ANALYZED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUM_ROWS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n------------------------------ --- -------------------- ----------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n\nSQL>\nSQL> select\u00a0 table_name, partition_name, global_stats, last_analyzed, num_rows\n\u00a0 2\u00a0 from dba_tab_partitions\n\u00a0 3\u00a0 where table_name='TEST_TAB1'\n\u00a0 4\u00a0 and table_owner='TESTUSER'\n\u00a0 5\u00a0 order by 1, 2, 4 desc nulls last;\n\nTABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARTITION_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GLO LAST_ANALYZED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUM_ROWS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n------------------------------ ------------------------------ --- -------------------- ----------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100131\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100201\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100202\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100203\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100204\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100205\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100206\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100207\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100209\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0 28-MAR-2010 15:38:38\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100210\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n\n10 rows selected.\n\nSQL> select\u00a0 table_name, subpartition_name, global_stats, last_analyzed, num_rows\n\u00a0 2\u00a0 from dba_tab_subpartitions\n\u00a0 3\u00a0 where table_name='TEST_TAB1'\n\u00a0 4\u00a0 and table_owner='TESTUSER'\n\u00a0 5\u00a0 order by 1, 2, 4 desc nulls last;\n\nTABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUBPARTITION_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GLO LAST_ANALYZED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUM_ROWS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n------------------------------ ------------------------------ --- -------------------- ----------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100131_GROT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100131_HALO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n\n<<snipped>>\n\nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100209_GROT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO  28-MAR-2010 15:38:32\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100209_HALO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO  28-MAR-2010 15:38:32\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100209_JUNE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO  28-MAR-2010 15:38:32\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100209_OTHERS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO  28-MAR-2010 15:38:33\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100210_GROT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0 28-MAR-2010 15:38:33\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100210_HALO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100210_JUNE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \nTEST_TAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_20100210_OTHERS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n\n40 rows selected.<\/pre>\n
So here’s where we are<\/p>\n
– No sign of partition statistics for P_20100210, despite the P_20100209 ‘source’ partition having valid stats.
– The subpartition stats have been copied from P_20100209_GROT to P_20100210_GROT.
– The subpartition stats have not been copied for the other three P_20100210 partitions. <\/p>\n
Weird, right? I’ve checked this over and over and I’m pretty sure I’m right, but decided to upload the entire script and output here<\/a> in case others can spot some mistake I’m making.<\/p>\n
Updated on 07\/05\/2010 – Yes, it is weird. It also happens to be nothing to do with DBMS_STATS.COPY_TABLE_STATS. This post<\/a> explains why this really happened.<\/strong><\/p>\n
Getting back down to earth, though, this isn’t the end of the world. It just means that for our particular stats collection strategy of loading data into load tables, exchanging them with subpartitions and then copying earlier stats, we just need to make sure we’re working at the subpartition level all the time and that’s what I’ll look at next.<\/p>\n
Finally, I’ll re-emphasise that this is not<\/em> the only strategy and it’s fair to say it’s flushing out some unusual effects that you might never see if you work primarily with Table and Partition-level stats!<\/p>\n","protected":false},"excerpt":{"rendered":"
Sigh … these posts have become a bit of a mess. There are so many different bits and pieces I want to illustrate and I’ve been trying to squeeze them in around normal work. Worse still, because I keep leaving them then coming back to them and re-running tests it’s easy to lose track of… Continue reading Statistics on Partitioned Tables – Part 6b – COPY_TABLE_STATS – Mistakes<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1598","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1398,"url":"http:\/\/orcldoug.com\/blog\/2008\/04\/05\/bstatestat-fun\/","url_meta":{"origin":1598,"position":0},"title":"bstat\/estat Fun","date":"April 5, 2008","format":false,"excerpt":"I've been playing around with utlbstat.sql\/utlestat.sql, only briefly I should add, for a short section on the history of Oracle's performance tuning utilities and I noticed a couple of things that tickled me enough to blog about them.First, as the scripts output the comments, I noticed that quite a few\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1319,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/23\/parallel-query-and-11g\/","url_meta":{"origin":1598,"position":1},"title":"Parallel Query and 11g","date":"September 23, 2007","format":false,"excerpt":"I've been playing around with 11g on and off this week, re-running some of the tests in this document (PDF file).The main edits required to the scripts were simple ones to correct the locations of trace files, for example (where $ORACLE_BASE is \/ora on the 10g system) rm \/ora\/admin\/TEST1020\/udump\/*.trc and\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":840,"url":"http:\/\/orcldoug.com\/blog\/2006\/03\/08\/how-many-slaves\/","url_meta":{"origin":1598,"position":2},"title":"How Many Slaves?","date":"March 8, 2006","format":false,"excerpt":"Parallel Execution and the Magic of 2That's my second presentation, at 2:15 today. I've thought long and hard about this one and hope I've made the right decision. I was concerned about how to encapsulate a 50+ page paper into a one hour slot. I hate presentations which are full\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":856,"url":"http:\/\/orcldoug.com\/blog\/2006\/02\/25\/mistakes\/","url_meta":{"origin":1598,"position":3},"title":"Mistakes","date":"February 25, 2006","format":false,"excerpt":"It's the lessons you learn or the things you discover when you make mistakes that can be the most useful. Here's an example of that and also of why you shouldn't try to take too many shortcuts. (It might be an interesting exercise for you to see how long you\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1403,"url":"http:\/\/orcldoug.com\/blog\/2008\/04\/17\/moving-awr-data\/","url_meta":{"origin":1598,"position":4},"title":"Moving AWR data","date":"April 17, 2008","format":false,"excerpt":"Note - features in this post require the Diagnostics Pack license[I originally had the first section at the end of the blog post, but then realised I might as well get the bad news out of the way to save you wasting your time if you're not interested]A small section\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":957,"url":"http:\/\/orcldoug.com\/blog\/2005\/08\/10\/a-shortcut-for-oracle_home\/","url_meta":{"origin":1598,"position":5},"title":"A shortcut for ORACLE_HOME","date":"August 10, 2005","format":false,"excerpt":"I've enjoyed quite a few of the tips in other people's Oracle-related blogs (see the links in the right hand pane) so thought I'd chuck in a few as well. This one's an old one, so many of you will know about it already, but for those that don't ...In\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1598","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1598"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1598\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1598"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1598"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1598"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}