I’m pretty self-concious of the amount of waffle that surrounds any technical content here, so let’s get the technical bit out of the way first, then the waffling can come later …<\/p>\n
I finally tracked down the mistake I didn’t<\/em> make in part 6a<\/a>, but thought<\/em> I’d identified and fixed in part 6b<\/a>! Here are the two sets of subpartitions for the P_20100209 partition that’s the source of the statistics and for the new P_20100210 partition that the stats were copied to. This is how part 6a<\/a> originally looked<\/p>\n and here it is after I’d fixed it.<\/p>\n So originally I had seen no statistics on the P_20100210_GROT partition after the call to COPY_TABLE_STATS but then I did see statistics on a later run so I edited the post to reflect that.<\/p>\n Why the difference? Well it’s not a COPY_TABLE_STATS issue and COPY_TABLE_STATS does not<\/em> copy statistics for one subpartition and not the others. It just doesn’t make sense and never did but I somehow convinced myself it did. Here’s where those subpartition stats actually came from and you can walk through\u00a0 the script and output<\/a> I posted earlier to see this for yourself.<\/p>\n (Note – I actually repeat steps 1 and 2 twice – once with _minimal_stats_aggregation set to it’s default value of TRUE and then with _minimal_stats_aggregation set to FALSE. But both runs have the same error, so you can refer to either)<\/p>\n 1) I create LOAD_TAB1 and re-use it 4 times to load 3 rows into each of the 4 new subpartitions using subpartition exchange. Here’s an example of one of those loads.<\/p>\n 2) The subpartition stats now look like this<\/p>\n 3) Now that I have the P_20100209 subpartition stats, I’ll add a new partition, populate the P_20100210_GROT subpartition with 10 rows using the same LOAD_TAB1 and subpartition exchange and then copy the stats from the previous partition.<\/p>\n 4) The subpartition stats now look like this <\/p>\n so it looks like COPY_TABLE_STATS copied stats for just the subpartition If I had added a gather_stats call on LOAD_TAB1 immediately after populating it with data (as I did in earlier parts of the test), the stats would still have appeared on P_20100210_GROT, but NUM_ROWS of 10 would have highlighted that these were not copied stats, but stats from the load table. Alternatively, if I had dropped and recreated LOAD_TAB1 each time (or used a different load table entirely) and not gathered stats on it (as you wouldn’t if you were using COPY_TABLE_STATS) then I would have seen what I did at the outset – no subpartition stats at all! That would be a much closer version of how you might actually use this in production.<\/p>\n A final alternative would have been to delete the inappropriate stats from LOAD_TAB1 when I reuse it. That’s the approach I’ve used in a very slightly modified version of stats_5_6a.txt that I’ve put here<\/a> (just search for delete_table_stats).<\/p>\n I keep saying this, but one of my favourite aspects of blogging is making and fixing your mistakes in public. It’s often the mistakes that provide the real learning experiences and it’s also an extension of discussing your ideas with others. However, the down-side is that you can end up with an unholy, confusing mess of different, contradictory posts and it can be hard to judge what you should edit as a result. I never edit posts meaningfully without making it pretty clear I’ve done so and, although I’m tempted to tidy up this mess to make all the posts more consistent, I’ve decided I’d rather leave the meandering ‘story’ as is. No promises on when this might happen but, when these posts are all done, I think there’s no avoiding a white paper about managing statistics on partitioned objects that will be coherent and correct. For now, let’s leave the mess as it is 😉<\/p>\n","protected":false},"excerpt":{"rendered":"TEST_TAB1 P_20100209_GROT NO 22-APR-2010 11:24:12 3\nTEST_TAB1 P_20100209_HALO NO 22-APR-2010 11:24:13 3\nTEST_TAB1 P_20100209_JUNE NO 22-APR-2010 11:24:13 3\nTEST_TAB1 P_20100209_OTHERS NO 22-APR-2010 11:24:13 3\nTEST_TAB1 P_20100210_GROT NO\nTEST_TAB1 P_20100210_HALO NO\nTEST_TAB1 P_20100210_JUNE NO\nTEST_TAB1 P_20100210_OTHERS NO\n<\/pre>\n
TEST_TAB1 P_20100209_GROT NO 22-APR-2010 11:24:12 3\nTEST_TAB1 P_20100209_HALO NO 22-APR-2010 11:24:13 3\nTEST_TAB1 P_20100209_JUNE NO 22-APR-2010 11:24:13 3\nTEST_TAB1 P_20100209_OTHERS NO 22-APR-2010 11:24:13 3\nTEST_TAB1 P_20100210_GROT NO 22-APR-2010 11:24:10 3\nTEST_TAB1 P_20100210_HALO NO \nTEST_TAB1 P_20100210_JUNE NO \nTEST_TAB1 P_20100210_OTHERS NO \n<\/pre>\n
SQL> TRUNCATE TABLE LOAD_TAB1;\n\nTable truncated.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 400, 'U');\n\n1 row created.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 600, 'U');\n\n1 row created.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 900, 'U');\n\n1 row created.\n\nSQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');\n\nPL\/SQL procedure successfully completed.\n\nSQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_JUNE WITH TABLE load_tab1;\n\nTable altered.\n\nSQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION\tP_20100209_JUNE REBUILD UNUSABLE LOCAL INDEXES;\n\nTable altered<\/pre>\nTABLE_NAME SUBPARTITION_NAME GLO LAST_ANALYZED NUM_ROWS\n------------------------------ ------------------------------ --- -------------------- ----------\nTEST_TAB1 P_20100209_GROT NO 22-APR-2010 11:24:12 3\nTEST_TAB1 P_20100209_HALO NO 22-APR-2010 11:24:13 3\nTEST_TAB1 P_20100209_JUNE NO 22-APR-2010 11:24:13 3\nTEST_TAB1 P_20100209_OTHERS NO 22-APR-2010 11:24:13 3\n<\/pre>\n
SQL> ALTER TABLE TEST_TAB1\n 2 ADD PARTITION P_20100210 VALUES LESS THAN (20100211);\n\nTable altered.\n\nSQL> TRUNCATE TABLE LOAD_TAB1;\n\nTable truncated.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 1000, 'P');\n\n1 row created.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 30000, 'P');\n\n1 row created.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 2000, 'P');\n\n1 row created.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 10000, 'N');\n\n1 row created.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 2400, 'P');\n\n1 row created.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 500, 'P');\n\n1 row created.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 1200, 'P');\n\n1 row created.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 400, 'P');\n\n1 row created.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 600, 'P');\n\n1 row created.\n\nSQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 700, 'Z');\n\n1 row created.\n\nSQL> COMMIT;\n\nCommit complete.\n\nSQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100210_GROT WITH TABLE load_tab1;\n\nTable altered.\n\nSQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION\tP_20100210_GROT REBUILD UNUSABLE LOCAL INDEXES;\n\nTable altered.\n\nSQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', \n srcpartname => 'P_20100209', dstpartname => 'P_20100210');\n\nPL\/SQL procedure successfully completed.\n<\/pre>\n
TEST_TAB1 P_20100209_GROT NO 22-APR-2010 11:24:12 3\nTEST_TAB1 P_20100209_HALO NO 22-APR-2010 11:24:13 3\nTEST_TAB1 P_20100209_JUNE NO 22-APR-2010 11:24:13 3\nTEST_TAB1 P_20100209_OTHERS NO 22-APR-2010 11:24:13 3\nTEST_TAB1 P_20100210_GROT NO 22-APR-2010 11:24:10 3\nTEST_TAB1 P_20100210_HALO NO\nTEST_TAB1 P_20100210_JUNE NO\nTEST_TAB1 P_20100210_OTHERS NO\n<\/pre>\n
\n I just loaded via subpartition exchange, but why didn’t it copy the
\nstats for the other subpartitions? The answer is that it didn’t<\/em>
\ncopy any stats for subpartitions. The stats for P_20100210_GROT were
\ncreated because we exchanged LOAD_TAB1 with that subpartition and
\nLOAD_TAB1 still had statistics from the previous time it was used<\/em>! <\/p>\n
The Waffle
<\/strong>
As well as being an illustration of how easy it is to screw up tests when you start cutting and pasting sections from other scripts to (ha!) simplify things, this is a poster-child for discussing things with others. It was only when I sat down with my colleague Jari Kuhanen (excellent Oracle guy – looking for work soon 😉) to go over another aspect of these tests and he asked some pertinent questions, that the problem suddenly hit me. He didn’t need to tell me what the problem was – just him asking good questions made me think about it a different way and I suddenly knew what the issue was.<\/p>\n