Oracle _fix_control and DBMS_OPTIM_BUNDLE

_FIX_CONTROL is a special hidden dynamic parameter introduced in 10.2.0.2 which can be used to enable or disable certain bug fixes. This is typically used in the optimizer area to allow specific fixes to be turned on or off as some fixes can have a significant  effect on execution plans.

The value parameter set to 1 means bug fix is activated 

When you upgrade oracle database version, you can be face  a problem of CBO that changes its behavior.

 

To enable:

"_fix_control"='Bugno:ON'    (OR)   "_fix_control"="Bugno:1"

To disable:
"_fix_control"='Bugno:OFF'  (OR)   "_fix_control"="Bugno:0"


Eg
alter system set "_fix_control"='27268249:0';   --> Disable 

For _FIX_CONTROL to work, several conditions must be met:

1) The patch that is referenced must have the option to use _FIX_CONTROL.  Using _FIX_CONTROL can't be used to backout any patch.  The patch (usually an Optimizer patch)  has to be enabled to use the _FIX_CONTROL parameter.

2)  The patch must be installed and visible in the V$SYSTEM_FIX_CONTROL view.  To check this:
SQL>  SELECT * FROM V$SYSTEM_FIX_CONTROL;

Note: To determine which bug fixes have been altered one can select from the fixed views GV$SYSTEM_FIX_CONTROL, GV$SESSION_FIX_CONTROL or their V$ counterparts.

Handling _fix_control    at session level  . 

SQL> alter session set "_fix_control"='4728348:OFF';
Session altered.
SQL> select * from v$session_fix_control where session_id=143 and bugno=4728348;



Handling _fix_control   using Hints 

/*+ OPT_PARAM('_fix_control’ ’9195582:0') */



Handling _FIX_CONTROL using DBMS_OPTIM_BUNDLE

This package is created to manage (enable/disable) optimizer fixes provided as part of PSU/bundles. Optimizer fixes are those provided as part of bundle which has a fix-control and can possibly cause a plan change.

This package has existed in some previous versions of the database, was dropped again most recently from 19.3 which is why it was again dropped from the Library. Oracle reintroduced it with 19.4 which the Library did not research so, from our perspective, it is "new" again in 20c.

dbms_optim_bundle.enable_optim_fixes(
action                     IN VARCHAR2 DEFAULT 'OFF',
scope                      IN VARCHAR2 DEFAULT 'MEMORY',
current_setting_precedence IN VARCHAR2 DEFAULT 'YES);

DBMS_OPTIM_BUNDLE.ENABLE_OPTIM_FIXES(
     action                     => 'ON' | 'OFF' ,
     scope                      => 'MEMORY' | 'SPFILE' | 'BOTH' | 'INITORA' ,
     current_setting_precedence => 'YES' | 'NO' )


exec dbms_optim_bundle.enable_optim_fixes('ON','MEMORY', 'NO');

exec dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES');
exec dbms_optim_bundle.enable_optim_fixes;
exec dbms_optim_bundle.enable_optim_fixes('ON', 'INITORA');
execute dbms_optim_bundle.enable_optim_fixes('OFF','BOTH','NO');

set serveroutput on;
execute dbms_optim_bundle.getBugsforBundle;

exec dbms_optim_bundle.listbundleswithfcfixes;
exec dbms_optim_bundle.getBugsForBundle(170718);

From Oracle 19.12.0 the API got extended  .  We can  have one of fix set to disable and rest set to enable using same command .

SQL> exec dbms_optim_bundle.set_fix_controls('27268249:0','*', 'BOTH','YES');




References : 
How to use the _FIX_CONTROL hidden parameter (Doc ID 827984.1)
MOS Note:2147007.1 – Automatic Fix Control Persistence (FCP) for Database Proactive Bundle Patch