Init parameterOptimizer & SQL

OPTIMIZER_ADAPTIVE_STATISTICS

What it controls

Adaptive statistics features such as SQL plan directives and dynamic statistics (12.2 and later).

Know this before you change it

FALSE is the default from 12.2 on, because these features caused extra parsing and plan changes on many 12.1 systems.

Default: FALSE

Check and change it

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

Open in the parameter referenceOracle's reference

More in Optimizer & SQL

• OPTIMIZER_FEATURES_ENABLEMakes the optimizer behave like an earlier release.
• 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.