Some time ago I came across a query that was using dynamic sampling on a partitioned table even though statistics were present at global level. After some time spent investigating this issue I discovered an interesting but unexpected behaviour of the Oracle CBO when dealing with the GLOBAL_STATS statistic (observed on Oracle RDBMS ver. 11.2.0.2.0, 11.2.0.3.0, 12.1.0.2.0).
Let me go through a simple demo so I can explain what I’ve found.

First of all let’s create our test table MYTAB partitioned by RANGE and subpartitioned by LIST and populate it with 100k rows:

In order to show details about statistics and generate the explain plans I need for the demo, I will use the following code (for every test I will only mention the query I’m going to analyze and replace it inside this script):

At the moment the table has no statistics so if we run any query like

the Oracle CBO uses dynamic sampling as expected

Let’s gather statistics at table level executing

and run explain for the same query. This query accesses a specific partition and the explain plan shows that dynamic sampling is not being used anymore because statistics are present at global level

The same happens if we run a query that selects a specific subpartition or scans all partitions like

that generates the following plan

Until now nothing unexpected.
Now let’s delete all statistics and gather them again at partition level

When we do so Oracle sets the table level statistics by aggregating those at partition level. This can be observed by the GLOBAL_STATS flag set to NO on the USER_TAB_STATISTICS view

Now let’s see what happens when we run few queries that access the table at partition, subpartition and global level.
The first query

accesses a specific partition and Oracle correctly uses the statistics we gathered

The second query

accesses a specific subpartition of the partition that has statistics but the CBO surprisingly decides to use dynamic sampling!
It’s clearly visible from the plan

This means that the Oracle CBO simply ignores the partition level statistics (note that they have GLOBAL_STATS=YES). Table level stats are not used either because they have GLOBAL_STATS=NO.
If we execute a third query that scans all partitions

we can observe that dynamic sampling is not used. Moreover, having a look at rows estimates it is clear that table level statistics are being used here

even though GLOBAL_STATS=NO at table level!
Now let’s see what happens if we remove partition level statistics

If we run the query that accesses a specific partition

we can see that dynamic sampling is used

Again the table level statistics are ignored because they have GLOBAL_STATS=NO. We would get the same exact result by accessing specific partition and subpartition.
As a last test let’s manually set the statistics at global level

You’ll notice that the GLOBAL_STATS flag at table level has changed to YES and none of our queries will use dynamic sampling

The bottom line is that when table level stats are obtained by aggregation from underlying partitions, Oracle sets the GLOBAL_STATS flag to NO and queries accessing a specific partition use dynamic sampling unless they have local stats available.
It’s also quite surprising that if partition level statistics are available, a query that accesses a specific subpartition without stats uses table level ones completely ignoring the partition layer.
You can find some other interesting considerations on Tony Hasler’s blog where he ran several tests on the same topic using bind variables.

Leave a Reply

Your email address will not be published. Required fields are marked *