Init parameterOptimizer & SQL

OPTIMIZER_FEATURES_ENABLE

What it controls

Makes the optimizer behave like an earlier release.

Know this before you change it

Lowering it after an upgrade is a blunt fallback that turns off every newer optimizer feature. Prefer fixing individual plans with baselines.

Check and change it

1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'optimizer_features_enable';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'optimizer_features_enable' AND isspecified = 'TRUE';8 9-- Dynamic: takes effect now and is kept after a restart10ALTER SYSTEM SET optimizer_features_enable = '19.1.0' SCOPE = BOTH SID = '*';11 12-- Or for your own session only13ALTER SESSION SET optimizer_features_enable = '19.1.0';14 15-- Remove it from the spfile to go back to the default at the next restart16ALTER SYSTEM RESET optimizer_features_enable SCOPE = SPFILE SID = '*';

Related scripts

• SQL plan baselines and SQL profilesWhat's pinning plans in this database. Baselines come with Enterprise Edition; SQL profiles are created by the SQL Tuning Advisor, which needs the…
• Plan history for a SQL_ID from AWREach AWR snapshot where the statement ran, with its plan hash value and average time. Shows exactly when a plan flipped and what it cost.

Open in the parameter referenceOracle's reference

More in Optimizer & SQL

• OPTIMIZER_ADAPTIVE_STATISTICSAdaptive statistics features such as SQL plan directives and dynamic statistics (12.2 and later).
• OPTIMIZER_ADAPTIVE_PLANSLets the optimizer switch join methods during the first execution (12.2 and later).
• CURSOR_SHARINGWhether Oracle replaces literals with bind variables before parsing.
• STATISTICS_LEVELHow much performance data Oracle collects.
• OPTIMIZER_MODEWhether the optimizer aims for total throughput or fast first rows.
• DB_FILE_MULTIBLOCK_READ_COUNTBlocks read in one I/O during full scans.
• PARALLEL_MAX_SERVERSThe most parallel execution processes per instance.
• PARALLEL_DEGREE_POLICYWhether Oracle chooses the degree of parallelism itself.
• RESULT_CACHE_MAX_SIZEMemory for the server result cache.