Oracle Database 12.1 -- SQL Plan Baselines not Being Applied

Problem

SQL Plan baselines not being applied during statements executions

Solution

SQL Plan baselines may conflict with the adaptive plans feature introduced in Oracle Database 12.1 version

To make baselines to be applied for SQL statements disable the adaptive plans feature using:

At session level:

SQL> alter session set OPTIMIZER_ADAPTIVE_PLANS=false;

At system level:

SQL> alter system set OPTIMIZER_ADAPTIVE_PLANS=false scope=both;

Optionally to troubleshoot the reasons for SQL Plan baselines not being used, use:

SQL> alter session set events 'trace [SQL_Compiler.*]';

Execute the query and immediately run:

SQL> select * from table(dbms_xplan.display_cursor());

This will generate the trace file in the default trace directory:

SQL> select value from v$diag_info where name='Default Trace File';