Enabling and Disabling Optimizer Bug Fixes

The following was recommended by Oracle support in connection with a specific bug. However, I think some Oracle users may find it useful when testing.

Oracle releases one-off bug fixes for the CBO quite frequently. You can find out which fixes are enabled in your own instance by inspecting the 10053 trace.

For example:

fix 5385629 = enabled
fix 5705630 = disabled
fix 1234567 = enabled
fix 6122894 = enabled
fix 5842686 = disabled
fix 6006300 = disabled

In the above example bug fix 1234567 is enabled. If you want to disable this fix dynamically you can try using:

ALTER SESSION SET "_fix_control" = '1234567:off';

The output of 10053 should change as follows:

fix 5385629 = enabled
fix 5705630 = disabled
fix 1234567 = disabled *
fix 6122894 = enabled
fix 5842686 = disabled
fix 6006300 = disabled

Note the asterisk indicates a non-default setting for the fix

To enable the fix again use:

ALTER SESSION SET "_fix_control" = '1234567:on';

Obviously this should only be used in a test environment, but it is a useful way to evaluate the impact of a bug fix without needing to repeatedly install and deinstall the patch - something that is under change control with a few of my larger customers.

As this is an unsupported parameter you consult Oracle Support before using it in a production environment.