Oracle _fix_control tips

Question:  I need to what the _fix_control parameter (plus v$system_fix_control and v$session_fix_control) do.  What is the purpose of the fix_control parameters?

Answer:  Oracle has several methods of the fix_control setting, set with the "alter session" command".  The _fix_control is also an initialization parameter that was introduced in Oracle 10.2.0.2 to give granular control over specific features and bug fixes.

The _fix_control parameter 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.

Also see v$system_fix_control and v$session_fix_control. and how to display optimizer features by release using v$system_fix_control..

See metalink note 567171.1 for details on using _fix_control settings.  You can set _fix_control parameters at the system level:

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

You can also set _fix_ctontrol at the session level (for testing)

SQL> alter session set "_fix_control"='4728348:OFF';

Session altered.

SQL> select * from v$session_fix_control where session_id=143 and bugno=4728348;

SESSION_ID BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT
---------- ---------- ---------- ---------------------------------------------------------------- --
143 4728348 0 consider mjc if equi-joined pred is dropped in kkoipt 10.2.0.2

A value of 0 indicates the fix is off.

SQL> alter session set "_fix_control"='4728348:ON';

Session altered.

SQL> select * from v$session_fix_control where session_id=143 and bugno=4728348;

SESSION_ID BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT
---------- ---------- ---------- ---------------------------------------------------------------- --
143 4728348 1 consider mjc if equi-joined pred is dropped in kkoipt 10.2.0.2

It appears to me that if you need two fixes off in a session you have to specify them together else you lose the first change.

SQL> alter session set "_fix_control"='4728348:OFF';

Session altered.

SQL> alter session set "_fix_control"='4663698:OFF';

Session altered.

SQL> select * from v$session_fix_control where session_id=143 and bugno in (4728348,4663698);

SESSION_ID BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT
---------- ---------- ---------- ---------------------------------------------------------------- --
143 4663698 0 for cached NL table set tab_cost_io to zero 10.2.0.2 0 0
143 4728348 1 consider mjc if equi-joined pred is dropped in kkoipt 10.2.0.2