Sunday, June 7, 2009

Dynamic sampling and partitioned tables

Update January 2010: A thread on OTN mentioned this blog post and another, actually contradicting blog post by Asif Momen.

So why are these two blog posts coming to different conclusions regarding Dynamic Sampling and partitions with missing statistics?

This is the good thing with documented test cases - I reproduced what Asif has done and found out that the significant difference between these two test cases is the existence of global level statistics.

In my test case below, I have explicitly gathered statistics only on partition level, and there are no statistics on global level/table (which can be seen from the output of the query against user_tab_statistics below).

Asif has actually gathered statistics on global/table level which can be seen from his blog post.

So the conclusion seems to be: If you prune to a single partition, but this partition has no statistics, then Dynamic sampling will be used if no global/table level statistics are available. If global/table level statistics are available, the optimizer won't perform dynamic sampling and revert to these global/table level statistics instead.

Oddly this obviously doesn't apply to the subpartition/partition level case: Repeating a similar setup with subpartitions having no statistics, but statistics on partition level are available, Dynamic Sampling still was used (tested on 11.1.0.7 Win32).

Dynamic sampling for tables with missing statistics is enabled by default from Oracle 10g on (OPTIMIZER_DYNAMIC_SAMPLING = 2). You can get the same behaviour in Oracle 9i by increasing the default dynamic sampling level of 1 to at least 2, by the way, at system, session or statement level (OPTIMIZER_DYNAMIC_SAMPLING parameter or the DYNAMIC_SAMPLING hint at statement level). For more information, see the documentation.

It's an interesting question what happens if you have a partitioned table but only for some of the partitions or subpartitions statistics are missing, and some others have statistics gathered.

Does dynamic sampling selectively kick in depending on which partition accessed or is it simply checking if the table itself has statistics or not?

The following testcase which works only on 11.1 and later since it's using list/range composite partitioning for the subpartition specific tests shows the results of 11.1.0.7 on Win32:

So as can be seen in 11.1 dynamic sampling is selectively used, depending on what kind of partition pruning is recognized by the optimizer at parse time and if statistics have been gathered for that partition, and this also applies to the subpartition level. The same can be seen in 10.2.0.4, apart from the severe bug regarding single subpartition pruning in the 10.2.0.4 patch set release as shown here.

As already mentioned a couple of times here on my blog, the optimizer code of pre-10.2.0.4 versions doesn't use subpartition level statistics even when pruned to a single subpartition and always reverts to the partition level.

What I'm not sure about is the following: I read from the results provided that in both cases you've tested dynamic sampling got used.

But in your initial post you said that after analyzing the partition level no dynamic sampling got used in 11.1.0.6.

Also you say now "no stats" and "with subpartition level stats".

But the original issue was about having subpartition stats in place and adding partition level stats afterwards.

I'm not trying to be picky here but in order to understand the issue could we try to clarify this?

I would expect two different 10053 traces, one showing dynamic sampling being used (no partition level stats) and a second one without dynamic sampling (with partition level stats), according to your initial comment.

I think because of the large outputs and blogspot limits something is confusing about my comments. (you can delete my old comments to make things clear)

Let me explain my issue from the beginning For composite partitioning issue on 11.1.0.6, I see dynamic sampling like below but you dont see dynamic sampling on 11.1.0.7. ( which is optimizer_features_enabled=11.1.0.6 has the same bahaviour as optimizer_features_enabled=10.2.0.3)

Therefore I expected two 10053 traces: The case that you've now posted again that uses dynamic sampling (and which I couldn't reproduce in 11.1.0.7) when having subpartition level statistics but no partition level statistics, and the second one when you've gathered partition level statistics and don't get dynamic sampling anymore (which corresponds to the 10.2.0.3 behaviour as you said).

thanks for bearing with me. So finally, this is a bit puzzling, your 11.1.0.6 trace really looks like the 10.2.0.3 behaviour...

It's different from 10.2.0.4 though, since 10.2.0.4 even with partition level statistics in place screws it up (compare e.g. the "Index Stats" lines, which always show up with zeros in the 10.2.0.4 trace).

Very interesting indeed, I was under the impression that 11.1.0.6 was supposed to behave like 11.1.0.7 regarding this subpartition pruning issue.