Setting OPTIMIZER_ADAPTIVE_FEATURES in Oracle Database 12c Release 2
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: 22.214.171.124.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 🙂
- Adaptiver Optimizer in Oracle 12.1 und 12.2:
- OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2: https://mikedietrichde.com/2016/11/22/optimizer_adaptive_features-obsolete-in-oracle-12-2/
- Optimizer Adaptive Features in Oracle Database 12c Release 2: https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2
Ad (Amazon Link):