8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Granular Control of Optimizer Features (OPTIMIZER_FEATURES_ENABLED, _FIX_CONTROL, V$SYSTEM_FIX_CONTROL and V$SESSION_FIX_CONTROL)

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:

Hope this helps. Regards Tim...

Back to the Top.