8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Granular Control of Optimizer Features (OPTIMIZER_FEATURES_ENABLED, _FIX_CONTROL, V$SYSTEM_FIX_CONTROL and V$SESSION_FIX_CONTROL)
- OPTIMIZER_FEATURES_ENABLE Initialization Parameter
- _FIX_CONTROL Initialization Parameter
- OPT_PARAM Hint
OPTIMIZER_FEATURES_ENABLE Initialization Parameter
When a database is upgraded or patched, it is possible one or more bug fixes will cause the optimizer to select different execution plans for some SQL statements. If this causes performance problems in a particular application, it is not uncommon for the DBA to set the OPTIMIZER_FEATURES_ENABLE
to a previous version or patch set, to effectively downgrade the optimizer functionality, while the problem is investigated. The parameter can be set at the system or session level.
SQL> ALTER SYSTEM SET optimizer_features_enable='9.2.0.8'; SQL> ALTER SESSION SET optimizer_features_enable='9.2.0.8';
The parameter can also be set for a specific SQL statement using the OPTIMIZER_FEATURES_ENABLE
hint.
SELECT /*+ optimizer_features_enable('9.2.0.8') */ * FROM ...;
Although very useful, the OPTIMIZER_FEATURES_ENABLE
parameter is a brute force approach as you lose all newer features and bug fixes, rather than just those causing the issue.
_FIX_CONTROL Initialization Parameter
The _FIX_CONTROL
initialization parameter was introduced in Oracle 10.2.0.2 to give granular control over specific features and bug fixes. It is most commonly associated with the optimizer, but it can be used for other areas also. It's a hidden parameter, so it should only be used under the direction of Oracle Support.
Rather than setting the OPTIMIZER_FEATURES_ENABLE
parameter, you identify specific bug numbers and toggle their state. The available bug fixes are listed in the V$SYSTEM_FIX_CONTROL
and V$SESSION_FIX_CONTROL
views. I query these using the following two scripts:
For example.
SQL> @system_fix join 11.1.0.7 BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER EVENT IS_DEFAULT ---------- ---------- ----------------------------------- ---------------------------------------------------------------- --------- ---------- ---------- 6377505 1 QKSFM_TRANSFORMATION_6377505 Convert outer-join to inner-join if single set aggregate functio 11.1.0.7 0 1 6006300 1 QKSFM_JPPD_6006300 allow JPPD with Cartesian join 11.1.0.7 0 1 6502845 1 QKSFM_CBO_6502845 enable PWJ on ref-part table and composite parent join 11.1.0.7 0 1 6503543 1 QKSFM_CBO_6503543 Improve range join selectivity 11.1.0.7 0 1 6982954 1 QKSFM_PQ_6982954 bloom filter for hash join with broadcast left 11.1.0.7 0 1 5 rows selected. SQL>
The VALUE
column shows the current state of the bug fix, where 1=ON and 0=OFF.
To test the impact of a specific bug, you can toggle its state using the ALTER SESSION
command, specifying the bug number and the ON/OFF or 1/0 flag.
-- Off ALTER SESSION SET "_fix_control"='6377505:OFF'; ALTER SESSION SET "_fix_control"='6377505:0'; -- On ALTER SESSION SET "_fix_control"='6377505:ON'; ALTER SESSION SET "_fix_control"='6377505:1';
Multiple flags are set using a comma-separated list.
ALTER SESSION SET "_fix_control"='6377505:OFF','6006300:OFF';
If you need to, they can be turned off for the whole instance.
ALTER SYSTEM SET "_fix_control"='6377505:OFF','6006300:OFF'; ALTER SYSTEM RESET "_fix_control";
OPT_PARAM Hint
The OPT_PARAM
hint is used to set a specific initialization parameter during the execution of a single SQL statement. It can be used with the _FIX_CONTROL
parameter if a session or system level setting is too extreme.
SELECT /*+ OPT_PARAM('_fix_control' '6377505:OFF') */ * FROM ...;
Multiple entries are set using a space-separated list.
SELECT /*+ OPT_PARAM('_fix_control' '6377505:OFF 6006300:OFF') */ * FROM ...;
For more information see:
- OPTIMIZER_FEATURES_ENABLE
- OPTIMIZER_FEATURES_ENABLE Hint
- v$SYSTEM_FIX_CONTROL
- v$SESSION_FIX_CONTROL
- OPT_PARAM Hint
- How to use the _FIX_CONTROL hidden parameter [ID 827984.1]
- Init.ora Parameter "_FIX_CONTROL" [Hidden] Reference Note [ID 567171.1]
Hope this helps. Regards Tim...