Adaptive Query Optimization

Performance feedback is one of the adaptive query optimizer features introduced in Oracle Database 12c. The aim of this short post isn’t to explain how it works, but to simply warn you about a buggy behavior in its configuration.

The parameters that control performance feedback are the following:

OPTIMIZER_ADAPTIVE_FEATURES (12.1 only): if set to TRUE (default), performance feedback is enabled. Otherwise, it is disabled.

OPTIMIZER_ADAPTIVE_STATISTICS (12.1 with patch 22652097, and 12.2): if set to TRUE, performance feedback is enabled. Otherwise, which is the default, it is disabled.

PARALLEL_DEGREE_POLICY (12.1 and 12.2): it has to be set to ADAPTIVE (default is MANUAL) to enable performance feedback.

I finally managed to install and test patch 22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES). Actually, I installed and tested two patches. The first was installed on top of “Oracle Database Patch 23054246 – Database Patch Set Update 12.1.0.2.160719” (from now on, 12.1.0.2.160719 PSU). The second was installed on top of “Patch 24448103 – Database Proactive Patch 12.1.0.2.161018” (12.1.0.2.161018 PBP).

From a functional point of view, both patches provides the 12.2 configuration in 12.1 as I describe it in this post. In fact, the behavior of setting the (un)documented initialization parameters as well as all the default values are the same. I was able to find only two differences between 12.2 and a patched 12.1.

In the past I gave a number of 1-day seminars about the new performance features available in Oracle Database 12c Release 1. On the 22nd of February, for the first time, I’ll give an updated version of that seminar with content about both Release 1 and Release 2. Note that because there is more content, I extended it from one day to two days.

I’ve said a number of times, the process of writing articles is part of an ongoing learning experience for me. A few days ago my personal tech editor (Jonathan Lewis) asked about a statement I made in the SQL Plan Directive article. On further investigation it turned out the sentence was a complete work of fiction on my part, based on my misunderstanding of something I read in the manual, as well as the assumption that everything that happens must be as a result of a new feature.

The aim of the STATISTICS COLLECTOR row source operation, which is used in adaptive plans, is to buffer all data produced by its child operation until it is known whether the inflection point is crossed. It goes without saying that buffering requires memory and, therefore, Oracle Database has to limit the amount of memory that can be allocated for that purpose. As a result, in some situations no adaptive plans can be used because according to the query optimizer estimations too much memory is required.

A special case of the previous limitation is when the row source operation under the STATISTICS COLLECTOR operation produces a LOB. In fact, when a LOB is detected, independently of the amount of data that is expected to be produced, the adaptive plan is bypassed by the query optimizer.