OPTIMIZER_FEATURES_ENABLE - Control Oracle Optimizer Version Behavior

OPTIMIZER_FEATURES_ENABLE

Use at Your Own Risk

Setting OPTIMIZER_FEATURES_ENABLE to a prior version disables potentially beneficial optimizer improvements. Use it as a short-term regression mitigation strategy, not a permanent configuration. Always test thoroughly in a non-production environment. See our disclaimer for full terms.

Overview

OPTIMIZER_FEATURES_ENABLE controls which optimizer features, heuristics, and bug fixes Oracle activates when generating execution plans. Setting it to a specific Oracle version string causes the optimizer to behave as it did in that release — disabling any plan-changing improvements introduced in later versions. This parameter is primarily used as a post-upgrade regression safety valve: if an upgrade to 19c or 23ai causes query plan regressions, setting this parameter to the prior version allows you to stabilise performance while methodically testing and accepting new optimizer behaviours one at a time.

Parameter Type: Dynamic (ALTER SESSION and ALTER SYSTEM) Default Value: Matches the current database version (e.g., 19.1.0 on Oracle 19c) Valid Values: Oracle version strings from 8.0.0 through the current release (e.g., 11.2.0.4, 12.2.0.1, 18.1.0, 19.1.0, 21.1.0) Available Since: Oracle 8.0 Modifiable: Yes — ALTER SESSION and ALTER SYSTEM PDB Modifiable: Yes

Configuration

Viewing Current Value

-- Current running value
SELECT name, value, description
FROM   v$parameter
WHERE  name = 'optimizer_features_enable';

-- SPFILE value
SELECT name, value
FROM   v$spparameter
WHERE  name = 'optimizer_features_enable';

-- All optimizer-related parameters for a complete picture
SELECT name, value
FROM   v$parameter
WHERE  name LIKE 'optimizer%'
ORDER  BY name;

-- Check the database version to understand what the default should be
SELECT banner FROM v$version WHERE banner LIKE 'Oracle%';

Setting the Parameter

-- Roll back optimizer to 12.2 behaviour after upgrading to 19c
ALTER SYSTEM SET optimizer_features_enable = '12.2.0.1' SCOPE = BOTH;

-- Roll back to 11g behaviour (aggressive regression protection)
ALTER SYSTEM SET optimizer_features_enable = '11.2.0.4' SCOPE = BOTH;

-- Test a specific query with a prior optimizer version (session-level, non-disruptive)
ALTER SESSION SET optimizer_features_enable = '18.1.0';

-- Reset to the current database version (re-enable all features)
-- Replace with your actual database version
ALTER SYSTEM SET optimizer_features_enable = '19.1.0' SCOPE = BOTH;

-- Verify the current value
SELECT name, value FROM v$parameter WHERE name = 'optimizer_features_enable';

Tuning Guidance

Recommended Values

Scenario	Recommended Setting	Rationale
Stable production (no recent upgrade)	Default (current DB version)	All current optimizer improvements active
Immediately post-upgrade (stabilisation)	Prior major version (e.g., 12.2.0.1 when upgrading to 19c)	Prevents optimizer regressions while validating workload
Specific SQL regression identified	Prior version at session level only	Surgical fix without database-wide impact
Testing new optimizer features	Default	Allow the optimizer to use current-version improvements
23ai upgrade from 19c	19.1.0 initially, then test incrementally	Controlled adoption of 23ai optimizer improvements

Upgrade Planning Workflow

The standard workflow for managing optimizer behaviour across a major Oracle upgrade:

Phase 1 — Pre-upgrade baseline capture

-- Capture execution plans for critical SQL before the upgrade
-- Store in SQL Plan Baselines so they survive the upgrade
BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
      table_name  => 'SPM_STAGE_PRE_UPGRADE',
      table_owner => 'SYS'
  );
END;
/

-- Load plans from the cursor cache into the staging table
DECLARE
  l_plans PLS_INTEGER;
BEGIN
  -- Load all accepted baselines
  l_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
      table_name  => 'SPM_STAGE_PRE_UPGRADE',
      table_owner => 'SYS'
  );
  DBMS_OUTPUT.PUT_LINE('Packed ' || l_plans || ' plans');
END;
/

Phase 2 — Post-upgrade immediate stabilisation

-- Set to prior version to prevent plan regressions immediately post-upgrade
ALTER SYSTEM SET optimizer_features_enable = '19.1.0' SCOPE = BOTH;  -- when upgrading to 23ai, for example

-- Confirm setting
SELECT name, value FROM v$parameter WHERE name = 'optimizer_features_enable';

Phase 3 — Identify changed plans

-- Find SQL where the plan changed after the upgrade
-- Compare AWR plan history before and after upgrade date
SELECT s.sql_id,
       s.plan_hash_value,
       s.parsing_schema_name,
       s.executions,
       s.elapsed_time / 1000000     AS elapsed_sec,
       s.rows_processed,
       SUBSTR(s.sql_text, 1, 80)    AS sql_text
FROM   v$sql s
WHERE  s.last_active_time > SYSDATE - 7    -- last week post-upgrade
  AND  s.executions > 10
ORDER  BY s.elapsed_time DESC
FETCH FIRST 30 ROWS ONLY;

-- Check AWR for historical plan hash values to compare
SELECT sql_id,
       plan_hash_value,
       optimizer_cost,
       optimizer_mode,
       parsing_schema_name,
       module
FROM   dba_hist_sqlstat
WHERE  snap_id IN (
    SELECT snap_id
    FROM   dba_hist_snapshot
    WHERE  begin_interval_time BETWEEN SYSDATE - 14 AND SYSDATE - 7  -- pre-upgrade window
)
ORDER  BY elapsed_time_total DESC
FETCH FIRST 20 ROWS ONLY;

Phase 4 — Incrementally re-enable optimizer features

-- Test with next version to identify which version introduced regressions
ALTER SESSION SET optimizer_features_enable = '19.3.0';

-- Run your critical SQL workload and validate plans
EXPLAIN PLAN FOR
    SELECT /* your critical query */ * FROM your_table WHERE ...;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL +PEEKED_BINDS'));

Optimizer Fix Controls (V$SYSTEM_FIX_CONTROL)

Beyond the version-level switch, Oracle provides granular control over individual optimizer fixes via V$SYSTEM_FIX_CONTROL and the _fix_control hidden parameter.

-- List all optimizer fixes available in the current database
SELECT bugno,
       value,
       sql_feature,
       description,
       optimizer_feature_enable
FROM   v$system_fix_control
ORDER  BY bugno;

-- Find fixes that were introduced between two versions
-- (useful for understanding what changed between 19c and 23ai)
SELECT bugno,
       sql_feature,
       description,
       optimizer_feature_enable
FROM   v$system_fix_control
WHERE  optimizer_feature_enable BETWEEN '19.1.0' AND '23.1.0'
ORDER  BY optimizer_feature_enable, bugno;

-- Find fixes related to a specific optimizer feature area
SELECT bugno,
       value,
       sql_feature,
       description,
       optimizer_feature_enable
FROM   v$system_fix_control
WHERE  sql_feature LIKE '%JOIN%'   -- e.g., join-related optimizer fixes
ORDER  BY bugno;

-- Check if a specific fix is enabled
SELECT bugno, value, description
FROM   v$system_fix_control
WHERE  bugno = 8937971;   -- example: a specific optimizer bug fix number

-- Disable a specific optimizer fix at the session level
-- (alternative to setting the entire OPTIMIZER_FEATURES_ENABLE back a version)
ALTER SESSION SET "_fix_control" = '8937971:OFF';

-- Disable at the system level (persists to SPFILE)
ALTER SYSTEM SET "_fix_control" = '8937971:OFF' SCOPE = BOTH;

-- Verify
SELECT bugno, value, description
FROM   v$system_fix_control
WHERE  bugno = 8937971;

Using _fix_control to disable individual fixes is more surgical than rolling back OPTIMIZER_FEATURES_ENABLE to a prior version, as it avoids disabling unrelated beneficial improvements.

Monitoring

-- Monitor SQL plan stability after changing OPTIMIZER_FEATURES_ENABLE
-- Track which SQL IDs have multiple plan hash values (plan instability signal)
SELECT sql_id,
       COUNT(DISTINCT plan_hash_value) AS distinct_plans,
       MAX(last_active_time)           AS last_active,
       SUM(executions)                 AS total_executions,
       ROUND(SUM(elapsed_time) / 1e6, 2) AS total_elapsed_sec,
       SUBSTR(MAX(sql_text), 1, 80)    AS sql_text
FROM   v$sql
WHERE  executions > 5
GROUP  BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER  BY total_elapsed_sec DESC
FETCH FIRST 20 ROWS ONLY;

-- Check the current optimizer features version against the database version
SELECT (SELECT value FROM v$parameter WHERE name = 'optimizer_features_enable') AS ofe_setting,
       (SELECT REGEXP_SUBSTR(banner, '[0-9]+\.[0-9]+\.[0-9]+', 1, 1)
        FROM   v$version
        WHERE  banner LIKE 'Oracle%')  AS db_version,
       CASE
         WHEN (SELECT value FROM v$parameter WHERE name = 'optimizer_features_enable') =
              (SELECT REGEXP_SUBSTR(banner, '[0-9]+\.[0-9]+\.[0-9]+', 1, 1)
               FROM v$version WHERE banner LIKE 'Oracle%')
         THEN 'CURRENT - All optimizer features active'
         ELSE 'DOWNGRADED - Optimizer running at prior version level'
       END AS status
FROM   dual;

Common Issues

Issue 1: Plan Regressions Immediately After Upgrade

Symptom: Critical queries perform significantly worse after upgrading from 12c to 19c or 19c to 23ai. The new optimizer is choosing different (and worse) plans.

Cause: Each Oracle version introduces optimizer improvements that can change plan selection. While most changes are improvements, some queries are sensitive to heuristic changes and may regress.

Immediate Resolution:

-- Step 1: Roll back optimizer to prior version to restore performance
ALTER SYSTEM SET optimizer_features_enable = '12.2.0.1' SCOPE = BOTH;
-- (replace with your pre-upgrade version)

-- Step 2: Verify critical query plans are restored
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

-- Step 3: Begin methodical re-enablement and testing (see upgrade workflow above)

Issue 2: OPTIMIZER_FEATURES_ENABLE Is Set to an Old Version in Production

Symptom: During a database audit, OPTIMIZER_FEATURES_ENABLE is found set to 11.2.0.4 on a 19c database — set years ago to fix a regression and never re-evaluated.

Cause: Temporary regression fixes are set and forgotten, causing the database to miss years of optimizer improvements.

Resolution: This is a common and significant finding. Plan a systematic re-enablement:

-- Check how long the parameter has been non-default
-- (cross-reference with instance startup time and change management records)
SELECT name, value, default_value
FROM   v$parameter
WHERE  name = 'optimizer_features_enable';

-- Set to a version between the old setting and current to test incrementally
-- Test at 12.1, then 12.2, then 18, then 19 — validating at each step
ALTER SESSION SET optimizer_features_enable = '12.1.0.2';
-- Run workload tests, then:
ALTER SESSION SET optimizer_features_enable = '12.2.0.1';
-- Run workload tests, then continue to current

Issue 3: Parameter Set in SPFILE but Session Value Differs

Symptom: The SPFILE shows optimizer_features_enable = '19.1.0' but a specific application session is using different plans consistent with an older version.

Cause: An application framework or connection pool is issuing ALTER SESSION SET optimizer_features_enable at connection time, overriding the system setting.

Resolution:

-- Check what value specific active sessions are using
SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       sp.value AS session_ofe_value
FROM   v$session      s
JOIN   v$ses_optimizer_env sp ON sp.sid = s.sid
WHERE  sp.name = 'optimizer_features_enable'
  AND  s.username IS NOT NULL
ORDER  BY s.sid;

Identify the application or middleware layer issuing the session-level override and remove or update it.

Related Parameters

Parameter	Relationship
OPTIMIZER_MODE	Controls the optimizer’s primary goal (throughput vs. first-rows latency). OPTIMIZER_FEATURES_ENABLE controls which features are available within the chosen mode.
CURSOR_SHARING	Literal-to-bind-variable rewriting can interact with optimizer feature levels.
OPTIMIZER_ADAPTIVE_PLANS	Adaptive plans are a newer feature (12c+). Setting OPTIMIZER_FEATURES_ENABLE to 11.2.0.4 disables adaptive plans entirely.
OPTIMIZER_ADAPTIVE_STATISTICS	Similarly disabled when OPTIMIZER_FEATURES_ENABLE is set to a pre-12c version.
OPTIMIZER_USE_SQL_PLAN_BASELINES	SQL Plan Baselines are an alternative to OPTIMIZER_FEATURES_ENABLE for plan stability — they pin specific plans for specific SQL IDs rather than rolling back all optimizer behaviour.

Related Errors

Error	Description
ORA-00600	Internal Error — can be triggered by optimizer bugs in specific versions. OPTIMIZER_FEATURES_ENABLE is sometimes used to avoid a version-specific optimizer bug while waiting for a patch.
ORA-07445	Exception Encountered — similar to ORA-00600; version-specific optimizer code paths occasionally surface as ORA-07445.

Version Notes

Oracle Version	Notes
Oracle 8.0	Parameter introduced. Allowed rollback to 7.3 behaviour.
Oracle 10g	Rule-based optimiser removed. Minimum valid value became 8.0.0.
Oracle 11g	Many new optimizer features (result cache, adaptive cursor sharing) gated by this parameter.
Oracle 12c	Adaptive plans and adaptive statistics introduced; both disabled when rolling back below 12.1.0.1.
Oracle 18c–19c	Approximate query processing, SQL quarantine, and real-time statistics gated by version.
Oracle 21c	In-memory improvements and SQL macro features tied to 21.1.0 baseline.
Oracle 23ai	AI vector search optimizer support gated at 23.1.0. Setting to prior version disables 23ai-specific plan improvements.