Oracle provides powerful tools for managing bug fixes that may impact execution plans. Two key features—_FIX_CONTROL and DBMS_OPTIM_BUNDLE—enable administrators to selectively enable or disable certain bug fixes in a controlled manner.
This post will help you understand these features, their usage, and how they can be applied to resolve issues like those that affect query optimization.
What is _FIX_CONTROL?
Introduced in Oracle 10.2, the _FIX_CONTROL parameter is used to manage specific bug fixes in Oracle Database.
These bug fixes are tied to issues that could affect query execution plans, optimizer behavior, or other performance-related aspects of the database. By using _FIX_CONTROL, administrators can enable or disable specific fixes without requiring a full patch rollback.
The key point here is that some bug fixes may need to be selectively enabled or disabled depending on their impact on system performance or query execution.
You can do this by using the V$SYSTEM_FIX_CONTROL view, which shows the status of each bug fix and its associated behavior.
Example: Bug Fix for cyclic join selectivity of 31487332 for serial plans
Let’s walk through an example of how _FIX_CONTROL works to manage a specific bug fix related to cyclic join selectivity of 31487332 for serial plans,
Query the V$SYSTEM_FIX_CONTROL View:
To check the current status of the bug fix for Bug 34044661, use the following SQL query:
SELECT bugno, value, description FROM v$system_fix_control WHERE bugno = 34044661;
BUGNO VALUE DESCRIPTION
---------- ---------- ----------------------------------------------------------------------
34044661 1 cyclic join selectivity of 31487332 for serial plans
Disabling the Fix:
To disable the fix and revert to the default behavior, use the following command:
ALTER SYSTEM SET "_fix_control" = '34044661:OFF';
After running this, you can check the status again:
SELECT bugno, value, description FROM v$system_fix_control WHERE bugno = 34044661;
BUGNO VALUE DESCRIPTION
---------- ---------- ----------------------------------------------------------------------
34044661 0 cyclic join selectivity of 31487332 for serial plans
Log Entry:
This action will also be recorded in the alert log:
2025-01-16T09:04:02.371313-04:00
ALTER SYSTEM SET _fix_control='34044661:OFF' SCOPE=BOTH;
What is DBMS_OPTIM_BUNDLE?
Introduced in Oracle 12.1.0.2, the DBMS_OPTIM_BUNDLE package offers a more robust approach to managing "installed but disabled" execution plan bug fixes that are installed during a patching event. These bug fixes are generally installed but remain disabled by default to prevent unintended changes in execution plans.
The DBMS_OPTIM_BUNDLE package provides more flexibility in managing these fixes by ensuring that bug fixes affecting execution plans are either enabled or preserved based on the patching status.
Key Features:
Automatic Fix Control Persistence: This package ensures that fixes are managed even after patching, and they can be enabled or disabled automatically based on the configuration.
Scope Flexibility: Administrators can apply fixes at the system level (BOTH), or for a specific instance or session (MEMORY or SPFILE).
Managing Execution Plan Bug Fixes: It allows administrators to explicitly enable or disable execution plan bug fixes that could change query performance.
Managing Bug Fixes Using DBMS_OPTIM_BUNDLE:
The DBMS_OPTIM_BUNDLE package simplifies the management of bug fixes that might impact query execution plans. After a patching event, Oracle does not activate these fixes automatically; they must be manually enabled if necessary. Here’s how you can manage them using the package.
we can list the available potentially behavior changing optimizer fixes in the current patch bundle:
SQL> set serveroutput on;
SQL> execute dbms_optim_bundle.getBugsforBundle;
19.21.0.0.231017DBRU:
Bug: 34044661, fix_controls: 34044661
Bug: 34544657, fix_controls: 33549743
Bug: 34816383, fix_controls: 34816383
Bug: 35330506, fix_controls: 35330506
PL/SQL procedure successfully completed.
These are all the fixes being installed BUT disabled.
SQL> execute dbms_optim_bundle.getBugsforBundle(231017);
19.19.0.0.230418DBRU:
Bug: 34027770, fix_controls: 34244753
Bug: 34467295, fix_controls: 34467295
Bug: 23220873, fix_controls: 23220873
Bug: 32550281, fix_controls: 32061341
Bug: 33548186, fix_controls: 33548186
Bug: 33421972, fix_controls: 33421972
Bug: 34605306, fix_controls: 32616683
19.20.0.0.230718DBRU:
Bug: 33627879, fix_controls: 33627879
Bug: 32005394, fix_controls: 32005394
Bug: 33069936, fix_controls: 33069936
Bug: 35012562, fix_controls: 35012562
Bug: 34685578, fix_controls: 34685578
Bug: 34862366, fix_controls: 31184370
Bug: 35313797, fix_controls: 35313797
Bug: 35412607, fix_controls: 35412607
19.21.0.0.231017DBRU:
Bug: 34044661, fix_controls: 34044661
Bug: 34544657, fix_controls: 33549743
Bug: 34816383, fix_controls: 34816383
Bug: 35330506, fix_controls: 35330506
PL/SQL procedure successfully completed.
To enable all "installed but disabled" execution plan bug fixes after applying a patch, use the following command:
EXEC dbms_optim_bundle.enable_optim_fixes('ON', 'BOTH', 'NO');
This will enable the fixes across all instances. After executing, Oracle will ensure that the bug fixes affecting the execution plans are applied as needed.
Enabling/Disabling Specific Fixes Using SET_FIX_CONTROLS
The SET_FIX_CONTROLS procedure is part of DBMS_OPTIM_BUNDLE and allows you to control the status of specific bug fixes. Here's how to use it to manage individual bug fixes like the one for Bug 34044661:
Enable the Bug Fix:
EXEC dbms_optim_bundle.set_fix_controls('34044661:1', '*', 'BOTH', 'NO');
This command enables the fix for Bug 34044661 across all instances.
Disable the Bug Fix:
EXEC dbms_optim_bundle.set_fix_controls('34044661:0', '*', 'BOTH', 'NO');
This command disables the fix for Bug 34044661 across all instances.
Example Output from SET_FIX_CONTROLS:
Here is the process for enabling and disabling the fix:
SQL> EXEC dbms_optim_bundle.set_fix_controls('34044661:1', '*', 'BOTH', 'NO');
PL/SQL procedure successfully completed.
SQL> SELECT bugno, value, description FROM v$system_fix_control WHERE bugno = 34044661;
BUGNO VALUE DESCRIPTION
---------- ---------- ----------------------------------------------------------------------
34044661 1 cyclic join selectivity of 31487332 for serial plans
SQL> EXEC dbms_optim_bundle.set_fix_controls('34044661:0', '*', 'BOTH', 'NO');
PL/SQL procedure successfully completed.
SQL> SELECT bugno, value, description FROM v$system_fix_control WHERE bugno = 34044661;
BUGNO VALUE DESCRIPTION
---------- ---------- ----------------------------------------------------------------------
34044661 0 cyclic join selectivity of 31487332 for serial plans
