What is a _FIX_CONTROL & DBMS_OPTIM_BUNDLE in Oracle ?
Posted by FatDBA on July 17, 2022
Lately I was in discussion with one of my friend who was facing an issue with Oracle 19c database where the vendor asked him to apply a patch to fix the problem, but he did not want to apply that single patch because their Oracle homes were shared and he didn’t want to increase the complexity of their patching cycles. Then later on Oracle suggested them to try a workaround which requires a setting using fix controls.
So many times Oracle recommends to set a fix control in case of a bug fix, but what exactly are they ? Their purpose ? & tools and methods to control these bug fixes ? This post is all about explaining all of them in detail.
So, What are they ? – Fix controls are bug fix control parameters introduced in 10.2 and they are typically used to enable/disable certain bug fixes in Oracle database. You cannot pull-back any patch, the patch you trying must have the option to use _FIX_CONTROL, and must be visible under V$SYSTEM_FIX_CONTROL views.
Let’s understand this using one of the case where mview push predicate was not happening due to wrong cardinality estimate in one of the production system running on 12.1.0.2. It was rejecting join predicate pushdown (JPPD) transformations and this was avoiding view to be joined with index-based nested-loop join method and causing issues. This was happening all due to bug 21802552. Let’s check if the bug number is present in fix control views and what’s its status.
SQL> select bugno, value, description from v$system_fix_control where bugno=21802552;
BUGNO VALUE DESCRIPTION
---------- ---------- ----------------------------------------------------------------------
21802552 1 correct cardinality adjusted by DS
-- You can get similar information using DBMS_SQLDIAG.GET_FIX_CONTROL(BUG NUMBER) proc as well.
So, its there in the view’s output and its enabled (value 1), and we can turn it off, lets do it. A proper syntax of using them is given below.
-- To enable:
"_fix_control"='Bugno:ON' (OR) "_fix_control"="Bugno:1"
-- To disable:
"_fix_control"='Bugno:OFF' (OR) "_fix_control"="Bugno:0"
SQL> ALTER SYSTEM SET "_fix_control" = '21802552:OFF';
System altered.
SQL>
SQL> select bugno, value, description from v$system_fix_control where bugno=21802552;
BUGNO VALUE DESCRIPTION
---------- ---------- ----------------------------------------------------------------------
21802552 0 correct cardinality adjusted by DS
-- same was recorded in alert log file as well
2022-07-16T09:04:02.371313-04:00
ALTER SYSTEM SET _fix_control='21802552:OFF' SCOPE=BOTH;
You can do the same using the new dbms_optim_bundle.set_fix_controls package, it was introduced in 12.1.0.2 to implement Oracle’s approach of ‘Automatic Fix Control Persistence’ framework. Let’s try to the same using said package.
-- This will set given _fix_controls in scope=BOTH on all instances
-- Lets enable it again before we disable it back again
SQL> exec dbms_optim_bundle.set_fix_controls('21802552:1','*', 'BOTH', 'NO');
PL/SQL procedure successfully completed.
SQL> select bugno, value, description from v$system_fix_control where bugno=21802552;
BUGNO VALUE DESCRIPTION
---------- ---------- ----------------------------------------------------------------------
21802552 1 correct cardinality adjusted by DS
-- Lets roll it back
SQL> exec dbms_optim_bundle.set_fix_controls('21802552:0','*', 'BOTH', 'NO');
PL/SQL procedure successfully completed.
SQL> select bugno, value, description from v$system_fix_control where bugno=21802552;
BUGNO VALUE DESCRIPTION
---------- ---------- ----------------------------------------------------------------------
21802552 0 correct cardinality adjusted by DS
--
-- Entry in parameter file made by the dbms_optim_bundle package for fix control
*._fix_control='21802552:0'#added through dbms_optim_bundle package
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 
Leave a Reply