Search

Top 60 Oracle Blogs

Recent comments

Adaptive Query Optimization Configuration: Parameters, Preferences and Fix Controls

The aim of this post is to summarize the knowledge about the 12.1 and 12.2 adaptive query optimizer configuration that, as far as I know, is spread over a number of (too many) different sources.

First of all, let’s shortly review which adaptive query optimization features exist:

  • Adaptive plans, which are only available in Enterprise Edition, include adaptive join methods (the ability to switch the join method from a nested
    loops to a hash join and vice versa), adaptive parallel distribution methods (HYBRID HASH distribution method) and adaptive star transformations (the ability to disable the access to a dimension).
  • Adaptive statistics includes automatic reoptimization (statistics and performance feedback), SQL plan directives and dynamic statistics.

Let me start by describing which (undocumented) parameters, preferences and fix controls are available in 12.1 to configure them. Later on, in the second part of this post, I describe the changes introduced by 12.2.

12.1

In 12.1 the central switch for the adaptive query optimization features is the initialization parameter OPTIMIZER_ADAPTIVE_FEATURES. When it’s set, it controls the following undocumented parameters and, therefore, the features related to them. Note that by default the parameter is set to TRUE and, therefore, all the mentioned features are enabled. And, in case it is set to FALSE, all the features are (obviously) disabled.

Adaptive plans:

  • _OPTIMIZER_ADAPTIVE_PLANS controls whether the query optimizer considers adaptive plans. If it’s set to TRUE (the default), the feature is enabled.
  • _OPTIMIZER_NLJ_HJ_ADAPTIVE_JOIN controls the adaptive join methods. If it’s set to TRUE (the default), the feature is enabled.
  • _PX_ADAPTIVE_DIST_METHOD controls the adaptive parallel distribution methods. If it’s set to CHOOSE (the default), the feature is enabled. If it’s set to OFF, the feature is disabled. This parameter also supports the values ALL and FORCE, but I never spent time investigating their behaviour.
  • _OPTIMIZER_STRANS_ADAPTIVE_PRUNING controls the adaptive star transformations. If it’s set to TRUE (the default), the feature is enabled.

Adaptive statistics:

  • _OPTIMIZER_GATHER_FEEDBACK controls whether the query optimizer asks the execution engine to check for misestimates and, as a result, whether it produces feedbacks. In addition, it also controls whether SQL plan directives are created. In fact, a SQL plan directive is created only when a feedback takes place. Hence, if no feedback takes place, SQL plan directives aren’t created. If it’s set to TRUE (the default), the feature is enabled.
  • _OPTIMIZER_USE_FEEDBACK controls whether the query optimizer, for its estimations, uses the feedbacks produced by the execution engine. If it’s set to TRUE (the default), the feature is enabled.
  • _OPTIMIZER_DSDIR_USAGE_CONTROL controls whether the query optimizer uses SQL plan directives. If it’s set to 126 (the default), the feature is enabled. If it’s set to 0, the feature is disabled. This parameter also supports other integer values, but I never spent time investigating their behaviour.

Note that there is one adaptive feature, dynamic statistics, that isn’t controlled by OPTIMIZER_ADAPTIVE_FEATURES. Instead, it’s controlled by OPTIMIZER_DYNAMIC_SAMPLING and, maybe surprisingly, by other features that can automatically enable it. Three are the cases to consider:

  • The “regular” dynamic sampling is enabled if the parameter is set to a value greater than 0. The used level is, obviously, the one specified by the parameter. Note that for the values between 1 and 10 (both included) old-style dynamic sampling is enabled, and for the value 11 adaptive dynamic sampling is enabled.
  • SQL plan directives can instruct the query optimizer to use dynamic statistics. In 12.1.0.1 they use/enable the dynamic sampling level specified by OPTIMIZER_DYNAMIC_SAMPLING. However, as of 12.1.0.2, and provided that OPTIMIZER_DYNAMIC_SAMPLING isn’t set to 0, level 11 is used. Note that to use the 12.1.0.1 behavior in later releases, the fix control 14191778 can be set to OFF (the default in ON).
  • When OPTIMIZER_DYNAMIC_SAMPLING is set to the default value (2) and the query optimizer considers a parallel execution plan, the optimizer can automatically choose the dynamic sampling level. To control this feature there are two fix controls.
    • 7452863 controls whether the feature is enabled. If it’s set to ON (the default), it’s enabled.
    • 12914055 controls, in 12.1.0.2, which dynamic sampling level is used. If it’s set to ON (the default) and OPTIMIZER_FEATURES_ENABLE ≥ 12.1.0.1, the level 11 is used. Otherwise, a level between 2 and 11 (both not included) is used.

In case OPTIMIZER_DYNAMIC_SAMPLING isn’t set to the default value, dynamic statistics at the configured level are used.

In addition to the initialization parameters and fix controls just mentioned, provided that a patch that fixes the bug 21171382 is installed, the preference AUTO_STAT_EXTENSIONS controls whether the DBMS_STATS package automatically creates column groups based on the information stored in SQL plan directives. If it’s set to ON (the default), column groups are created.

12.2

By looking at the Database Exadata Express Cloud Service I have access to and based on information provided at Oracle OpenWorld, here’s what has changed in 12.2…

In 12.2 OPTIMIZER_ADAPTIVE_FEATURES no longer exists. Or, better, it’s no longer relevant. It isn’t listed in dynamic performance views like V$PARAMETER but you can set it to whatever value you want without raising an error. For example:

SQL> alter session set optimizer_adaptive_features = blablabla;

Session altered.

Instead of OPTIMIZER_ADAPTIVE_FEATURES, 12.2 has two new initialization parameters: OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS. As their name implies, the former controls the adaptive plans and the latter the adaptive statistics. The first thing to point out is that by default the former is set to TRUE and the latter to FALSE. Therefore, by default only adaptive plans are enabled! But, to further understand their impact, let me describe which undocumented parameters they control and what their default value is.

OPTIMIZER_ADAPTIVE_PLANS controls the three undocumented parameters related to adaptive plans that I already described in the 12.1 section (i.e. _OPTIMIZER_NLJ_HJ_ADAPTIVE_JOIN, _PX_ADAPTIVE_DIST_METHOD and _OPTIMIZER_STRANS_ADAPTIVE_PRUNING). Note that also their default values are the same as in 12.1. In summary, for the configuration of adaptive plans, by default there is no relevant difference between 12.1 and 12.2.

OPTIMIZER_ADAPTIVE_STATISTICS controls, in addition to the three undocumented parameters related to adaptive statistics that I already described in the 12.1 section (i.e. _OPTIMIZER_GATHER_FEEDBACK, _OPTIMIZER_USE_FEEDBACK and _OPTIMIZER_DSDIR_USAGE_CONTROL), two new parameters: _OPTIMIZER_USE_FEEDBACK_FOR_JOIN and _OPTIMIZER_ADS_FOR_PQ. Since there are new undocumented parameters and, more importantly, the behaviour of the old ones isn’t the same as in 12.1, let’s describe what their purpose is.

  • _OPTIMIZER_GATHER_FEEDBACK has the same purpose as in 12.1. The only difference is that independently on whether OPTIMIZER_ADAPTIVE_STATISTICS is set to FALSE (the default) or TRUE, this parameter is always set to TRUE! In other words, the execution engine will always provide feedbacks to the query optimizer and generate SQL plan directives.
  • _OPTIMIZER_USE_FEEDBACK controls, as in 12.1, whether the query optimizer uses, for its estimations, the feedbacks produced by the execution engine. The difference is that with 12.2 it controls only part of the feedbacks. Specifically, only the single-table cardinality feedbacks. As for _OPTIMIZER_GATHER_FEEDBACK also this one is independent from OPTIMIZER_ADAPTIVE_STATISTICS and is always set to TRUE.
  • _OPTIMIZER_USE_FEEDBACK_FOR_JOIN controls whether the query optimizer uses, for its estimations, the feedbacks that aren’t controlled by _OPTIMIZER_USE_FEEDBACK. If it’s set to FALSE (the default), the query optimizer doesn’t use the join cardinality feedbacks.
  • _OPTIMIZER_DSDIR_USAGE_CONTROL has exactly the same purpose as in 12.1. If OPTIMIZER_ADAPTIVE_STATISTICS is set to FALSE (the default), this parameter is set to 0. However, if OPTIMIZER_ADAPTIVE_STATISTICS is set to TRUE, this parameter is set to 126. In other words, the query optimizer (obviously) uses SQL plan directives only if adaptive statistics are enabled.
  • _OPTIMIZER_ADS_FOR_PQ is supposed to control (I say so because I wasn’t able to test it — the Database Exadata Express Cloud Service doesn’t provide parallel processing) whether the query optimizer uses adaptive dynamic sampling (level 11) when it considers parallel execution plans. In other words, it provides the same functionality as the fix control 12914055 in 12.1. If OPTIMIZER_ADAPTIVE_STATISTICS is set to FALSE (the default), this parameter is also set to FALSE and the query optimizer uses old-style dynamic sampling. However, if OPTIMIZER_ADAPTIVE_STATISTICS is set to TRUE, this parameter is also set to TRUE and the query optimizer uses adaptive dynamic sampling.

In addition to the initialization parameters another relevant change has to be mentioned: the AUTO_STAT_EXTENSIONS preference is by default set to OFF. Hence, by default SQL plan directives don’t lead to the creation of column groups. But, since they are always created, you can enable them at any time.

In summary, by default 12.2 behaves more or less like a 11.2 database with two additional functionalities: adaptive plans and the creation of SQL plan directives that by default they neither lead to dynamic sampling nor are used by the DBMS_STATS package to create column groups.

Finally, it’s interesting to point out that Oracle is working on a patch to backport the 12.2 changes discussed in this post to 12.1. For additional information have a look to the MOS note Recommendations for Adaptive Features in Oracle Database 12c Release 1 (2187449.1). I requested the mentioned patch two weeks ago but for the moment I didn’t receive it. As soon as I’m able to test it, I’ll update this post or publish a new one. UPDATE 2016-12-21: I was able to test the patch. See this post for additional information.

PS: According to my understanding I shouldn’t be able to see undocumented parameters and their values when accessing the Database Exadata Express Cloud Service. But, obviously, I was able to see them… so, even though I’m not sure whether I should publish how to did it, that could be the topic of a future post.