Apr 22: Statistics on Partitioned Tables - Part 6b - COPY_TABLE_STATS - Mistakes
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 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.
It has got so bad that I noticed the other day that there were a couple of significant errors in the last post 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.
So what were the errors? Possibly more important, why did they appear? The mistakes are often as instructive as the successes.
Error 1
This is the tail-end of the subpartition stats at the end of part 5
Compared to the supposedly same section produced at the start of part 6a :-
Spot the difference? Forget the timestamps for now, although I hope it's clear that :-
- The stats were gathered at different times
- I really need to get my blogging act together
Instead, notice that GLOBAL_STATS is set to NO in part 5 and YES in part 6a. How could that happen?
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 ...)
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.
If I take the alternate approach of exchanging LOAD_TAB1 with the subpartition of TEST_TAB1 and only then 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.
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.
Error 2
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!
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 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.
If you weren't confused already, you have my permission to be utterly confused now
Summary
On a more serious note, let's recap what I'm trying to do here and what does and doesn't work.
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
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 ...
So here's where we are
- 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.
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 in case others can spot some mistake I'm making.
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 explains why this really happened.
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.
Finally, I'll re-emphasise that this is not 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!
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.
It has got so bad that I noticed the other day that there were a couple of significant errors in the last post 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.
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!
So what were the errors? Possibly more important, why did they appear? The mistakes are often as instructive as the successes.
Error 1
This is the tail-end of the subpartition stats at the end of part 5
SQL> select table_name, subpartition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_subpartitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME SUBPARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- <<snipped>> TEST_TAB1 P_20100209_GROT NO 28-FEB-2010 21:41:47 3 TEST_TAB1 P_20100209_HALO NO 28-FEB-2010 21:41:49 3 TEST_TAB1 P_20100209_JUNE NO 28-FEB-2010 21:41:49 3 TEST_TAB1 P_20100209_OTHERS NO 28-FEB-2010 21:41:50 3
Compared to the supposedly same section produced at the start of part 6a :-
TABLE_NAME SUBPARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- <<snipped>> TEST_TAB1 P_20100209_GROT YES 28-MAR-2010 15:38:32 3 TEST_TAB1 P_20100209_HALO YES 28-MAR-2010 15:38:32 3 TEST_TAB1 P_20100209_JUNE YES 28-MAR-2010 15:38:32 3 TEST_TAB1 P_20100209_OTHERS YES 28-MAR-2010 15:38:33 3
Spot the difference? Forget the timestamps for now, although I hope it's clear that :-
- The stats were gathered at different times
- I really need to get my blogging act together
Instead, notice that GLOBAL_STATS is set to NO in part 5 and YES in part 6a. How could that happen?
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 ...)
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.
If I take the alternate approach of exchanging LOAD_TAB1 with the subpartition of TEST_TAB1 and only then 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.
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.
Error 2
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!
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 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.
If you weren't confused already, you have my permission to be utterly confused now
Summary
On a more serious note, let's recap what I'm trying to do here and what does and doesn't work.
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
SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1',
srcpartname => 'P_20100209', dstpartname => 'P_20100210');
PL/SQL procedure successfully completed.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 ...
SQL> select table_name, global_stats, last_analyzed, num_rows 2 from dba_tables 3 where table_name='TEST_TAB1' 4 and owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ --- -------------------- ---------- TEST_TAB1 NO SQL> SQL> select table_name, partition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_partitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME PARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100131 NO TEST_TAB1 P_20100201 NO TEST_TAB1 P_20100202 NO TEST_TAB1 P_20100203 NO TEST_TAB1 P_20100204 NO TEST_TAB1 P_20100205 NO TEST_TAB1 P_20100206 NO TEST_TAB1 P_20100207 NO TEST_TAB1 P_20100209 NO 28-MAR-2010 15:38:38 12 TEST_TAB1 P_20100210 NO 10 rows selected. SQL> select table_name, subpartition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_subpartitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME SUBPARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100131_GROT NO TEST_TAB1 P_20100131_HALO NO <<snipped>> TEST_TAB1 P_20100209_GROT NO 28-MAR-2010 15:38:32 3 TEST_TAB1 P_20100209_HALO NO 28-MAR-2010 15:38:32 3 TEST_TAB1 P_20100209_JUNE NO 28-MAR-2010 15:38:32 3 TEST_TAB1 P_20100209_OTHERS NO 28-MAR-2010 15:38:33 3 TEST_TAB1 P_20100210_GROT NO 28-MAR-2010 15:38:33 3 TEST_TAB1 P_20100210_HALO NO TEST_TAB1 P_20100210_JUNE NO TEST_TAB1 P_20100210_OTHERS NO 40 rows selected.
So here's where we are
- 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.
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 in case others can spot some mistake I'm making.
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 explains why this really happened.
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.
Finally, I'll re-emphasise that this is not 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!
Comments
Display comments as
(Linear | Threaded)
#1 - Dominic Brooks said:
2010-04-22 15:02 - (Reply)
I admit it - I'm a scanner (no, not spanner, scanner).
Otherwise it's just impossible to absorb all the good points that all these people are making "out there".
In general, I just hope that enough info sticks so that when I have a problem with partition stats or whatever, a distant spidey sense kicks in I think "oh yes, I wonder if this is the same as Doug (or whoever) wrote about? Let's go back and find what he was going on about. (And then hmm... was it in part 1, 2, 3.. 6, 6a ..12, etc? )".
How about a policy of deliberately making a mistake in every article?
With a prize for the first spotter.
Even if that prize is merely kudos and peer respect.
#2 - Doug Burns said:
2010-04-22 16:28 - (Reply)
And then hmm... was it in part 1, 2, 3.. 6, 6a ..12, etc?
Let's hope that someone knows, because I certainly won't
Believe me, I'm also a scanner. My point is that I think most people I know or speak to are. Or the slight variation ... scan-and-mail-myself-a-url-to-read-later ...
#4 - Doug Burns said:
2010-04-23 10:44 - (Reply)
I haven't read every word of every article
I knew that already because you would have noticed the discrepancy
I tried using blog aggregators (e.g. Netvibes) as a way of collecting old posts but it hasn't worked too well so I'll think about a document containing 'To Read' items. I could print it off and store it on top of my 'To Read' books 
#5 - Kamus said:
2010-04-23 18:28 - (Reply)
A little suggestion for your stats_5_6a.txt. If you put only SQL statement in the text without any SQL> prompt and returned result, it will be more easier for others to reproduce your test, since your result is very clear by reading your article, no need in your script again. 
[Phew. At last. The first draft of this was dated more than two weeks ago .... One of the problems with blogging about copying stats was the balance between explaining it and pointing out some of the problems I've encountered. So I've broken up this post,
Tracked: Apr 22, 12:25
When Jonathan Lewis decided it was time to post a list of the Partition Stats posts on his blog and Noons suggested I made them easier to track down, I listened. So this post will link to the others and, at least in the short term, I've also included link
Tracked: Apr 22, 12:25
Tracked: May 03, 23:32