Statistics on Partitioned Tables - Part 4

Doug's Oracle Blog

In the last post I illustrated the problems you can run into when you rely on Oracle to aggregate statistics on partitions or subpartitions to generate estimated Global Statistics at higher levels of the table. Until there are statistics for all of the relevant structures then aggregation won't take place so, for example, if you have statistics for three out of four subpartitions, there won't be any aggregated global statistics on the related partition until you gather statistics on the fourth subpartition. Randolf Geist left a comment describing how you might avoid problems with this.

"In order to solve the issue of adding partitions with initially missing
statistics screwing up the aggregated statistics it was taken care that
newly added subpartitions got their statistics immediately updated (with
0 rows in that case) - which didn't take a lot of time since the
subpartitions were empty and it solved the issue with the aggregated
statistics."

That's what our system does, but we introduced a change in the last release that caused the problems that inspired this series of posts ...

First let's start with an empty table (definition hasn't changed since the first post). Now, because we are so paranoid about partitions without stats, we'll gather statistics at the PARTITION level even though the table is empty at the moment. I'm not going to specify a partition name here to cut the text back a bit, but on the real system we would have. Regardless, we'll still see the same problematic end result.

OK, so the table statistics aren't true Global Statistics but that's ok, we know about that. We also know that there's no data in the table at this stage so the stats reflect that. When we look at the Partition level stats :-

No subpartition stats at all at this stage which is expected behaviour and we'll be gathering them later after we load the data. I'm going to skip the column statistics at this stage because I don't need them to illustrate the problem. So let's imagine that on the live system we've just created the partitions above and are about to load data into the P_20100206_GROT subpartition.

N.B. It's probably worth pointing out at this stage that I put a short pause
in the test script between the original stats gathering on the empty
table and the INSERTs and gather on the newly-loaded subpartition so you might want
to pay attention to the LAST_ANALYZED values here.

So, as far as the Table and Partition Statistics look, this table is still empty! That's not good and I can imagine a near future of execution plans with CARDINALITY=1 and MERGE JOIN CARTESIAN. Looking at the LAST_ANALYSED values on the Partitions, I can see that the timestamp hasn't changed, which is another sign that something is wrong.

Ah, perhaps that's what the problem is. Only one of the P_20100206 subpartitions has valid stats so Oracle can not generate aggregated Global Stats at the higher levels of the table. So I'll try to fix that by gathering statistic on all of the subpartitions in the table. (In fact, I only really need to gather stats on the remaining P_20100206 subpartitions but I'll use this approach for brevity)

So, according to the table and the partition stats, the table is still empty and those partition statistics still haven't been updated!

The problem here is that Oracle won't overwrite true global stats with aggregated global stats. When you think about it, that's a sensible approach because if I have a strategy of collecting Table and Partition stats (i.e. the Oracle-recommended strategy covered in the first post) then the last thing I want is those global stats constantly being overwritten by aggregated stats (with incorrect NDVs) when stats are gathered on subpartitions!

Our mistake here could be viewed as a combination of a) not following Oracle recommendations (because if we did, we'd also be gathering global stats on the Table and Partitions using a seperate task and b) once we depart from that strategy, gathering stats at the incorrect level. Those Partition stats that we gathered can never be over-written except by gathering stats again on the Partitions, which would then be aggregated up to the table level.

Allowing for the fact we want to (have to?), use our current approach, we should only ever gather stats at the SUBPARTITION level which will then be aggregated up to the Table and the Partition level.

As for the fix, we deleted the existing stats, to rid the partitions of their global stats and then regathered at the SUBPARTITION level as a one-off exercise.

The important change is that we now have aggregated stats at both the Table and Partition levels which can then be updated by the aggregation process as we gather stats on new SUBPARTITIONS. Checking the statistics on the Table and Partitions ...

All of the partition stats have been updated and are now aggregated rather than true global stats. A modification to the metadata that our stats process uses to change the
granularity from PARTITION to SUBPARTITION will ensure stats are always gathered at the subpartition level and stop the problem from
re-occuring.

You could argue that we could have avoided all of this by just using the default stats gathering strategy and not try to be too clever, but we would really struggle to support the required additional workload. Oh, and this example makes the problem obvious because the stats were
gathered on empty partitions, we knew we'd done so and it was relatively easy to spot zero-row partitions, but imagine if
someone gathered statistics on your partitions manually for some reason
(it wouldn't be difficult to decide that seemed sensible) and the row counts for partitions are several million or so, frozen and
stuck that way forever until someone decides to repeat the process? Would you really notice the aggregation process
wasn't working for some reason?

Regardless of whether the problem is self-inflicted, as soon as we
spotted this mistake, I could imagine others making the same mistake if
they don't understand the aggregation process fully.

In the next few posts I'll look at some of the new approaches Oracle has introduced which we've investigated, to see if they can help us to gather better global statistics and/or reduce our stats-gathering workload.

I could write "been there, done it, got the t-shirt!" but that is not too helpful

One thing I have done in the past is to use the SET_TABLE_STATISTICS component of the DBMS_STATS package to put in temporary values into partition or table level stats until I have opportunity to collect the real thing - I know the number of rows and blocks populated and can often make the assumption that the NDV and ranges of values are not going to be far enough wrong to cause a bigger problem than not having the information in place.

Using aggregated statistics can be viable - but it forces discipline - the discipline to gather statistics after partition creation, the discipline not to touch global level stats, the discipline to regather stats after loading the partition - now that can cause problems too!
One challenge I see is with the new interval partitions in 11g - when we gather stats we will need to revise our stats gathering code to find the name of the partition - the PARTITION FOR syntax is not available to DBMS_STATS... still I like a challenge

I could write "been there, done it, got the t-shirt!" but that is not too helpful :-)

Or alternatively, write about it on the Rittman Mead blog so everyone can understand it, which *would* be helpful.

One thing I have done in the past is to use the SET_TABLE_STATISTICS component of the DBMS_STATS package to put in temporary values ...

As I commented here, that's something I've done in the past too, but it's not *quite* what the point of this post was Although there will be a copy_table_stats post coming up, I doubt I'll cover setting stats. Can't even attend Wolfgang Breitlings presentation at Hotsos which looks like it might be on this subject, given that I'm presenting at the same time

the discipline to regather stats after loading the partition - now that can cause problems too!

Probably needs to be automated, eh? I doubt something that requires discipline is likely to happen these days

Disclaimer

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.