Setting OPTIMIZER_ADAPTIVE_FEATURES in Oracle Database 12c Release 2

2017-09-20 Off By Markus Flechtner

In Oracle Database 12c Release 2 the parameter OPTIMIZER_ADAPTIVE_FEATURES was declared “Obsolete”. It was replaced by the parameters optimizer_adaptive_statistics, optimizer_adaptive_plans and AUTO_STAT_EXTENSIONS to allow better control of the adaptive features of the optimizer but what happens, if you try the set OPTIMIZER_ADAPTIVE_FEATURES on a 12.2 database?

There are two answers:

The system is an unpatched 12.2 system:
In this case you can set the parameter (even to a meaningless value), but it has no effect and you will not receive an error message.

The system was already patched:
(I’ve discovered the behaviour with the patch “DATABASE RELEASE UPDATE: 12.2.0.1.170718 (26123830)”.)

In this case you will receive an error message:

SQL> alter system set OPTIMIZER_ADAPTIVE_FEATURES =TRUE;
alter system set OPTIMIZER_ADAPTIVE_FEATURES
*
ERROR at line 1:
ORA-25138: OPTIMIZER_ADAPTIVE_FEATURES initialization parameter has been made obsolete

I like this behaviour more 🙂

Links: