DBA Hub

📋Steps in this guide1/3

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

Enable and disable specific optimizer features and bug fixes in Oracle 10.2.0.2 and above.

oracle miscconfigurationintermediate
by OracleDba
11 views
1

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 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. The parameter can also be set for a specific SQL statement using the hint. Although very useful, the parameter is a brute force approach as you lose all newer features and bug fixes, rather than just those causing the issue.

Code/Command (click line numbers to comment):

1
2
3
4
5
SQL> ALTER SYSTEM SET optimizer_features_enable='9.2.0.8';
SQL> ALTER SESSION SET optimizer_features_enable='9.2.0.8';

SELECT /*+ optimizer_features_enable('9.2.0.8') */ *
FROM   ...;
2

_FIX_CONTROL Initialization Parameter

The 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 parameter, you identify specific bug numbers and toggle their state. The available bug fixes are listed in the and views. I query these using the following two scripts: - system_fix.sql - session_fix.sql For example. The 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 command, specifying the bug number and the ON/OFF or 1/0 flag. Multiple flags are set using a comma-separated list. If you need to, they can be turned off for the whole instance.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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>

-- 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';

ALTER SESSION SET "_fix_control"='6377505:OFF','6006300:OFF';

ALTER SYSTEM SET "_fix_control"='6377505:OFF','6006300:OFF';

ALTER SYSTEM RESET "_fix_control";
3

OPT_PARAM Hint

The hint is used to set a specific initialization parameter during the execution of a single SQL statement. It can be used with the parameter if a session or system level setting is too extreme. Multiple entries are set using a space-separated list. 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...

Code/Command (click line numbers to comment):

1
2
3
4
5
SELECT /*+ OPT_PARAM('_fix_control' '6377505:OFF') */ *
FROM   ...;

SELECT /*+ OPT_PARAM('_fix_control' '6377505:OFF 6006300:OFF') */ *
FROM   ...;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!