Activating and Deactivating Performance Feedback

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 in 12c 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.

So far, so good.

The problem is that all those parameters control the undocumented parameter _OPTIMIZER_PERFORMANCE_FEEDBACK without checking how the others are set. As a result, knowing the value of the documented parameters isn’t enough to know whether statistics feedback is enabled. What you have to know is the order in which they were set! Alternatively you can check the value of the undocumented parameter.

To demonstrate that behavior I wrote this script. It’s output, when executed against 12.1 and 12.2, is the following. As you can see, when PARALLEL_DEGREE_POLICY is set after OPTIMIZER_ADAPTIVE_FEATURES/ OPTIMIZER_ADAPTIVE_STATISTICS, performance feedback is incorrectly enabled.

• 12.1

VERSION
------------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_features *before* parallel_degree_policy

optimizer_adaptive_features parallel_degree_policy _optimizer_performance_feedback
--------------------------- ---------------------- -------------------------------
FALSE                       MANUAL                 OFF
FALSE                       LIMITED                OFF
FALSE                       AUTO                   OFF
FALSE                       ADAPTIVE               ALL
TRUE                        MANUAL                 OFF
TRUE                        LIMITED                OFF
TRUE                        AUTO                   OFF
TRUE                        ADAPTIVE               ALL

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_features *after* parallel_degree_policy

parallel_degree_policy optimizer_adaptive_features _optimizer_performance_feedback
---------------------- --------------------------- -------------------------------
MANUAL                 FALSE                       OFF
LIMITED                FALSE                       OFF
AUTO                   FALSE                       OFF
ADAPTIVE               FALSE                       OFF
MANUAL                 TRUE                        OFF
LIMITED                TRUE                        OFF
AUTO                   TRUE                        OFF
ADAPTIVE               TRUE                        ALL

• 12.2

VERSION
------------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_statistics *before* parallel_degree_policy

optimizer_adaptive_statistics parallel_degree_policy _optimizer_performance_feedback
----------------------------- ---------------------- -------------------------------
FALSE                         MANUAL                 OFF
FALSE                         LIMITED                OFF
FALSE                         AUTO                   OFF
FALSE                         ADAPTIVE               ALL
TRUE                          MANUAL                 OFF
TRUE                          LIMITED                OFF
TRUE                          AUTO                   OFF
TRUE                          ADAPTIVE               ALL

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_statistics *after* parallel_degree_policy

parallel_degree_policy optimizer_adaptive_statistics _optimizer_performance_feedback
---------------------- ----------------------------- -------------------------------
MANUAL                 FALSE                         OFF
LIMITED                FALSE                         OFF
AUTO                   FALSE                         OFF
ADAPTIVE               FALSE                         OFF
MANUAL                 TRUE                          OFF
LIMITED                TRUE                          OFF
AUTO                   TRUE                          OFF
ADAPTIVE               TRUE                          ALL

Update 2018-02-26: to fix this issue, as of version 18.1.0, the initialization parameter OPTIMIZER_ADAPTIVE_STATISTICS no longer controls performance feedback.

VERSION
------------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_statistics *before* parallel_degree_policy

optimizer_adaptive_statistics parallel_degree_policy _optimizer_performance_feedback
----------------------------- ---------------------- -------------------------------
FALSE                         MANUAL                 OFF
FALSE                         LIMITED                OFF
FALSE                         AUTO                   OFF
FALSE                         ADAPTIVE               ALL
TRUE                          MANUAL                 OFF
TRUE                          LIMITED                OFF
TRUE                          AUTO                   OFF
TRUE                          ADAPTIVE               ALL

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_statistics *after* parallel_degree_policy

parallel_degree_policy optimizer_adaptive_statistics _optimizer_performance_feedback
---------------------- ----------------------------- -------------------------------
MANUAL                 FALSE                         OFF
LIMITED                FALSE                         OFF
AUTO                   FALSE                         OFF
ADAPTIVE               FALSE                         ALL
MANUAL                 TRUE                          OFF
LIMITED                TRUE                          OFF
AUTO                   TRUE                          OFF
ADAPTIVE               TRUE                          ALL