Did You Know #21 – Adaptive Features

When Oracle are adding a new feature to the database, they usually add a parameter to control it. Sometimes, after adding feature and the parameter, they realize that the parameter they chose for controlling the feature is not suitable. It might be confusing, or too general or something else. In these cases they change the parameter in the following version.

One example for that was in Oracle 10gR2, when Oracle introduced asynchronous commit. In 10gR2 they decided to add the parameter COMMIT_WRITE to control this feature. This parameter was confusing as it got 2 different values. They decided to split it into two parameters in 11.1: COMMIT_WAIT and COMMIT_LOGGING, which makes much more sense and these parameters are the current ones even today in 12.2.
The same happened with the adaptive features. The adaptive features were introduced in Oracle 12.1 and included adaptive plans and adaptive statistics. In 12.1, Oracle added the parameter OPTIMIZER_ADAPTIVE_FEATURES to enable or disable these features.
In Oracle 12.2, it seems like Oracle decided that it’s problematic to enable and disable all of these features using a single parameter. In some cases I would like to enable one feature while disable another. So in 12.2 the OPTIMIZER_ADAPTIVE_FEATURES parameter exists but it’s obsolete and shouldn’t be used. Two new parameters for controlling these features were introduced: OPTIMIZER_ADAPTIVE_PLANS to control the adaptive execution plans, and OPTIMIZER_ADAPTIVE_STATISTICS to control the adaptive statistics.
I guess this allows adding more adaptive features in the future while controlling each one individually.