Introduction

OPTIMIZER_ADAPTIVE_FEATURES enables or disables all of the adaptive optimizer features, including adaptive plan (adaptive join methods and bitmap plans), automatic re-optimization, SQL plan directives, and adaptive distribution methods.

Issue

How to set the value of optimizer_adaptive_features parameter as 'false' from SQL prompt in Oracle Database 12c?

Resolution

OPTION-1

1. Connect as sysdba, and

2. Issue the following commands if you want to set optimizer_adaptive_features for the present session:

connect / as sysdba
SQL> alter session set optimizer_adaptive_features=false;

SQL> show parameter optimizer_adaptive_features

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features boolean FALSE

Issue the following command to revert its value to 'true':

SQL> alter session set optimizer_adaptive_features=true;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features boolean TRUE

OPTION-2

If you want to permenantly set the value the issue the following command at SQL prompt:

SQL> alter system set optimizer_adaptive_features=false;

System altered.

Environment

+ Oracle Database 12c

+ Oracle Linux 5.x (x86-64)