I was working on a package that we use to gather statistics on the partition level after data is loaded and found some quirks that apply when the partition has subpartitions.
- You can’t use dbms_stats.gather_table_stats to gather stats on just one subpartition. (SEE THE COMMENTS BELOW FOR CLARIFICATION ON THIS)
- If you gather stats on a partition with granularity=>’PARTITION’ it doesn’t gather stats at the subpartition level.
So, if you update some data in subpartitions of a partition and want to update statistics on both the partition and its subpartitions you have to call dbms_stats.gather_table_stats twice. Once with granularity=>’PARTITION’ and once with granularity=>’SUBPARTITION’.
i.e. If you have a subpartitioned table called SUBPARTAB and partition PART_20120101 and you want to gather stats at the partition and subpartition level you run the following commands.
execute dbms_stats.gather_table_stats( NULL,'SUBPARTAB','PART_20120101', granularity=>'PARTITION'); execute dbms_stats.gather_table_stats( NULL,'SUBPARTAB','PART_20120101', granularity=>'SUBPARTITION');
Note that there is no subpartition parameter to dbms_stats.gather_table_stats. You can only specify the partition name.
Here an example script that demonstrates gathering stats at the global, partition, and subpartition levels and shows which update subpartition stats.
You can indeed gather stats on only a subpartition: Just name the subpartition in the partname parameter.
On a given table, partitions and subpartitions share a common namespace–that is, you can’t have a partition and a subpartition with the same name. So there’s no risk of ambiguity: If you specify a name, it has to refer to either a partition or a subpartition; it can’t refer to both.
I tried it just now on 10.2.0.4 and it worked. (I did give granularity => ‘SUBPARTITION’ which might have helped.)
You do make a good point in that Oracle failed to supply us with a granularity level of ‘PARTITION AND SUBPARTITION’, so if you want to analyze a partition and all its subpartitions, you need to make two calls to DBMS_STATS.
I ran a quick test and you are right. This is helpful information for me. If you put the subpartition name into the partition parameter to dbms_stats.gather_table_stats it will gather statistics on just one subpartition. Thanks for your reply!
Here is my testcase proving this:
If we are not gathering subparition level stats, will it have any impact on performance? Is there any specific reason we should or should not gather stats on subpartition?
Thank you for you comment. Unfortunately I don’t know the complete answer to your question. If you have any queries that only touch one subpartition then you definitely need stats on that subpartition. But, I’m not sure whether you need them if you have queries that span multiple subpartitions. I used to think that such queries would not use subpartition stats but they may in certain cases.
It is a good question and maybe others can give you a more complete answer.
thanks for this page, helpful. Tim
How about using execute dbms_stats.gather_table_stats(NULL,’SUBPARTAB’,granularity=>’ALL’);
Thank you for your comment.
I did a quick test and if you gather stats at the table level with granularity=’ALL’ it does gather stats at the table, partition, and subpartition level. I think my original point was that you can’t gather stats on one partition and all of its subpartitions with one call to dbms_stats.gather_table_stats. Your example shows that you can do this at the table level.
In version 12.2 a single call to gather_table_stats with granularity=>’AUTO’ and the name of the partition – will generate statistics for the subpartitions of that partition and as well for the partition itself.
Thank you for your comment. I ran some tests and granularity AUTO with a partition name gathers stats at the partition, subpartition, and table level. This worked as far back as 10.2.0.3.
I was originally trying to gather stats at the partition and subpartition levels only with granularity partition. I tried the newer versions of Oracle up to 18c and they still do not gather subpartition stats with granularity PARTITION.
Interesting stuff. Thanks for your input.
The fact that the table level stats are updated with the effect of the analyzed partition is different than the statistics gathering at table level with AUTO granularity, which actually runs the stats gathering for all partitions. Think of it like the number of rows coming from the partition is made known to the table level stats. So, for running stats at partition level including all its subpartitions, I think the one line that I wrote is good. Similarly, if you run just the subpartition stats, you will see that the partition and table level stats are also updated, but that does not mean that effort has been spent in analyzing other subpartitions or partitions. This is how I understand what is happening.
You could be right. I think it is just a matter of trying the different options and seeing which ones do what you want.
This is a great article! Set granularity => ALL or AUTO has significant difference than PARTITION or SUBPARTITION:
1. granularity => ALL or AUTO will update stats for all partitions & subpartitions and the table; meaning take a lot of time. Even if only 1 partition needs update and all other partitions have complete stats already, Oracle will still spend a lot of time in checking everything. This still happens even if all other partitions have stats locked up but only 1 partition is open for stats collection: Oracle will take long time to check them ‘quietly’ (i believe based on testing) in order to mark table-level stats is updated, although those locked partition stats are not updated at all.
2. granularity => partition will only collect stats on the specified partition, no check other partitions and won’t update table-level stats. This is much quicker than #1 above.
For a table with a size of TBs, collect table-level stats with #1 may take several hours, but take only 15 minutes if collect stats for 1 partition. And if 1 partition data can be used as stand alone, partition-level stats (no table-level stats) may be sufficient. And in this case, #2 is a good option. That’s why Bobby’s blog on updating stats on 1 partition and its subpartitions are important.
Thanks again Bobby!
Thanks for your comment! I am glad that the post was useful for you.