Should you enable _fix_controls with DBMS_OPTIM_BUNDLE?

Today, Paul mailed me asking a question we internally discussed many times in the past weeks: Should you enable _fix_controls with DBMS_OPTIM_BUNDLE? And there is no easy simple answer. But I can give a recommendation based on my experience from many projects.

DBMS_OPTIM_BUNDLE – a quick recap

Just in case you haven’t dealt with DBMS_OPTIM_BUNDLE recently, here is a quick recap.

With below procedure, you can list the available potentially behavior changing optimizer fixes in the current patch bundle – and you can turn them on or off at will:

SQL> set serveroutput on;
SQL> execute dbms_optim_bundle.getBugsforBundle;

19.12.0.0.210720DBRU:
    Bug: 31459242,  fix_controls: 31459242
    Bug: 31082719,  fix_controls: 31082719
    Bug: 28708585,  fix_controls: 28708585
    Bug: 31821701,  fix_controls: 31821701
    Bug: 32107621,  fix_controls: 32107621
    Bug: 26758837,  fix_controls: 26758837
    Bug: 31558194,  fix_controls: 31558194
    Bug: 30781970,  fix_controls: 30781970
    Bug: 30142527,  fix_controls: 30142527
    Bug: 31143146,  fix_controls: 31143146
    Bug: 31961578,  fix_controls: 31961578
    Bug: 31496840,  fix_controls: 31496840
    Bug: 22387320,  fix_controls: 22387320

PL/SQL procedure successfully completed.

These are all the fixes being installed BUT disabled.

SQL> execute dbms_optim_bundle.getBugsforBundle(210720);

19.4.0.0.190719DBRU:
    Bug: 29331066,  fix_controls: 29331066

19.5.0.0.191015DBRU:
    Bug: 28965084,  fix_controls: 28965084
    Bug: 28776811,  fix_controls: 28776811
    Bug: 28498976,  fix_controls: 28498976
    Bug: 28567417,  fix_controls: 28567417
    Bug: 28558645,  fix_controls: 28558645
    Bug: 29132869,  fix_controls: 29132869
    Bug: 29450812,  fix_controls: 29450812

19.7.0.0.200414DBRU:
    Bug: 29687220,  fix_controls: 29687220
    Bug: 29939400,  fix_controls: 29939400
    Bug: 30232638,  fix_controls: 30232638
    Bug: 30001331,  fix_controls: 30001331

19.8.0.0.200714DBRU:
    Bug: 29304314,  fix_controls: 29304314
    Bug: 29930457,  fix_controls: 29930457
    Bug: 30519188,  fix_controls: 30028663
    Bug: 28144569,  fix_controls: 28144569
    Bug: 28776431,  fix_controls: 28776431

19.9.0.0.201020DBRU:
    Bug: 27261477,  fix_controls: 27261477, 31069997, 31077481
    Bug: 28602253,  fix_controls: 28602253
    Bug: 31486557,  fix_controls: 29653132
    Bug: 29937655,  fix_controls: 29937655
    Bug: 30347410,  fix_controls: 30347410
    Bug: 30602828,  fix_controls: 30602828
    Bug: 30896685,  fix_controls: 30896685

19.10.0.0.210119DBRU:
    Bug: 29487407,  fix_controls: 29487407
    Bug: 30998035,  fix_controls: 30998035
    Bug: 30786641,  fix_controls: 30786641
    Bug: 31444353,  fix_controls: 31444353
    Bug: 30486896,  fix_controls: 30486896
    Bug: 28999046,  fix_controls: 28999046
    Bug: 30902655,  fix_controls: 30902655
    Bug: 30681521,  fix_controls: 30681521
    Bug: 29302565,  fix_controls: 29302565
    Bug: 30972817,  fix_controls: 30972817
    Bug: 30222669,  fix_controls: 30222669
    Bug: 31668694,  fix_controls: 31668694
    Bug: 31001490,  fix_controls: 31001490
    Bug: 30198239,  fix_controls: 30198239
    Bug: 30980115,  fix_controls: 30980115
    Bug: 30616738,  fix_controls: 30616738
    Bug: 31895670,  fix_controls: 31895670
    Bug: 19138896,  fix_controls: 19138896
    Bug: 31376708,  fix_controls: 31670824
    Bug: 30564898,  fix_controls: 9876287, 30564898
    Bug: 32234161,  fix_controls: 32075777
    Bug: 30842277,  fix_controls: 30570982

19.11.0.0.210420DBRU:
    Bug: 32037237,  fix_controls: 32037237
    Bug: 30927440,  fix_controls: 30927440
    Bug: 31788104,  fix_controls: 30822446
    Bug: 24561942,  fix_controls: 24561942
    Bug: 31625959,  fix_controls: 31625959
    Bug: 31976303,  fix_controls: 31579233
    Bug: 29696242,  fix_controls: 29696242
    Bug: 31626438,  fix_controls: 31626438
    Bug: 30228422,  fix_controls: 30228422
    Bug: 32122574,  fix_controls: 17295505
    Bug: 29725425,  fix_controls: 29725425
    Bug: 30618230,  fix_controls: 30618230
    Bug: 30008456,  fix_controls: 30008456
    Bug: 30537403,  fix_controls: 30537403
    Bug: 30235878,  fix_controls: 30235878
    Bug: 30646077,  fix_controls: 30646077
    Bug: 29657973,  fix_controls: 29657973
    Bug: 30527198,  fix_controls: 29712727
    Bug: 20922160,  fix_controls: 20922160
    Bug: 30006705,  fix_controls: 30006705
    Bug: 29463553,  fix_controls: 29463553
    Bug: 30751171,  fix_controls: 30751171
    Bug: 31009032,  fix_controls: 31009032
    Bug: 30207519,  fix_controls: 30063629, 30207519
    Bug: 31517502,  fix_controls: 31517502
    Bug: 30617002,  fix_controls: 30617002
    Bug: 30483217,  fix_controls: 30483217
    Bug: 30235691,  fix_controls: 30235691
    Bug: 30568514,  fix_controls: 30568514
    Bug: 28414968,  fix_controls: 28414968
    Bug: 32014520,  fix_controls: 32014520
    Bug: 30249927,  fix_controls: 30249927
    Bug: 31580374,  fix_controls: 31580374
    Bug: 29590666,  fix_controls: 29590666
    Bug: 29435966,  fix_controls: 29435966
    Bug: 29867728,  fix_controls: 28173995, 29867728
    Bug: 30776676,  fix_controls: 30776676
    Bug: 26577716,  fix_controls: 26577716
    Bug: 30470947,  fix_controls: 30470947
    Bug: 30979701,  fix_controls: 30979701
    Bug: 31435308,  fix_controls: 30483184, 31001295
    Bug: 31191224,  fix_controls: 31191224
    Bug: 31974424,  fix_controls: 31974424
    Bug: 29385774,  fix_controls: 29385774
    Bug: 28234255,  fix_controls: 28234255

19.12.0.0.210720DBRU:
    Bug: 31459242,  fix_controls: 31459242
    Bug: 31082719,  fix_controls: 31082719
    Bug: 28708585,  fix_controls: 28708585
    Bug: 31821701,  fix_controls: 31821701
    Bug: 32107621,  fix_controls: 32107621
    Bug: 26758837,  fix_controls: 26758837
    Bug: 31558194,  fix_controls: 31558194
    Bug: 30781970,  fix_controls: 30781970
    Bug: 30142527,  fix_controls: 30142527
    Bug: 31143146,  fix_controls: 31143146
    Bug: 31961578,  fix_controls: 31961578
    Bug: 31496840,  fix_controls: 31496840
    Bug: 22387320,  fix_controls: 22387320

PL/SQL procedure successfully completed.

By the way, in case you wonder about the parameter in this call execute dbms_optim_bundle.getBugsforBundle(210720), you need to use the date of the patch, e.g. 210720 for the July 2021 bundle.

What happens to previous settings?

One question I did receive several times already is whether the previous settings, e.g. from 19.11.0, will be kept or wiped out? So let me do a quick check. In my current 19.11.0 environment, I used DBMS_OPTIM_BUNDLE to enable all optimizer fixes turned off by default. My SPFILE has this nice little parameter:

*._fix_control='29331066:1','28965084:1','28776811:1','28498976:1','28567417:1','28558645:1','29132869:1','29450812:1','29687220:1','29939400:1','30232638:1','30001331:0','29304314:1','29930457:1','30028663:1','28144569:1','28776431:1','27261477:1','31069997:1','31077481:1','28602253:1','29653132:0','29937655:1','30347410:1','30602828:1','30896685:0','29487407:1','30998035:1','30786641:1','31444353:0','30486896:1','28999046:1','30902655:1','30681521:1','29302565:1','30972817:1','30222669:1','31668694:1','31001490:1','30198239:7','30980115:1','30616738:0','31895670:0','19138896:1','31670824:0','9876287:1','30564898:1','32075777:0','30570982:1','32037237:1','30927440:1','30822446:1','24561942:1','31625959:1','31579233:1','29696242:1','31626438:1','30228422:1','17295505:1','29725425:1','30618230:1','30008456:1','30537403:1','30235878:1','30646077:1','29657973:1','29712727:1','20922160:1','30006705:1','29463553:1','30751171:1','31009032:1','30063629:1','30207519:1','31517502:1','30617002:1','30483217:1','30235691:1','30568514:1','28414968:3','32014520:1','30249927:1','31580374:1','29590666:0','29435966:1','28173995:1','29867728:1','30776676:1','26577716:1','30470947:1','30979701:1','30483184:1','31001295:1','31191224:1','31974424:1','29385774:1','28234255:3'#added through dbms_optim_bundle package

It would be fab if the enabling would have the date when it got enabled in the parameter’s comment, too. But nobody is perfect.

Now let me turn on the 19.12.0 fixes.

execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES')

I did shorten the output a bit.

SQL> execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES')
DBMS_OPTIM command:  dbms_optim_bundle.enable_optim_fixes('ON', 'BOTH', 'YES')

Bundle's _fix_control setting as per action:ON
29331066:1  28965084:1	28776811:1  28498976:1	28567417:1  28558645:1
29132869:1  29450812:1	29687220:1  29939400:1	30232638:1  30001331:0
29304314:1  29930457:1	30028663:1  28144569:1	28776431:1  27261477:1
31069997:1  31077481:1	28602253:1  29653132:0	29937655:1  30347410:1
30602828:1  30896685:0	29487407:1  30998035:1	30786641:1  31444353:0
30486896:1  28999046:1	30902655:1  30681521:1	29302565:1  30972817:1
30222669:1  31668694:1	31001490:1  30198239:7	30980115:1  30616738:0
31895670:0  19138896:1	31670824:0  9876287:1  30564898:1  32075777:0
30570982:1  32037237:1	30927440:1  30822446:1	24561942:1  31625959:1
31579233:1  29696242:1	31626438:1  30228422:1	17295505:1  29725425:1
30618230:1  30008456:1	30537403:1  30235878:1	30646077:1  29657973:1
29712727:1  20922160:1	30006705:1  29463553:1	30751171:1  31009032:1
30063629:1  30207519:1	31517502:1  30617002:1	30483217:1  30235691:1
30568514:1  28414968:3	32014520:1  30249927:1	31580374:1  29590666:0
29435966:1  28173995:1	29867728:1  30776676:1	26577716:1  30470947:1
30979701:1  30483184:1	31001295:1  31191224:1	31974424:1  29385774:1
28234255:3  31459242:0	31082719:1  28708585:1	31821701:1  32107621:1
26758837:1  31558194:1	30781970:0  30142527:1	31143146:1  31961578:0
31496840:1  22387320:1

Taking current instance CDB2 as base, details on _fix_control setting for
CON_ID 1 :

1) Current _fix_control setting for spfile:
28965084:1  28776811:1	28567417:1  29132869:1	31444353:0  30927440:1
24561942:1  17295505:1	30646077:1  29463553:1	31580374:1  28173995:1
29867728:1  31974424:1	30001331:0  29930457:1	30028663:1  29653132:0
30998035:1  30902655:1	30681521:1  29302565:1	30198239:7  31625959:1
31579233:1  31517502:1	30568514:1  32014520:1	29590666:0  26577716:1
29385774:1  28144569:1	30347410:1  30602828:1	30570982:1  30822446:1
30008456:1  29657973:1	30483217:1  28558645:1	29450812:1  29687220:1
29939400:1  30896685:0	28999046:1  30972817:1	30222669:1  31668694:1
30616738:0  29725425:1	29712727:1  20922160:1	30063629:1  30617002:1
29435966:1  31191224:1	30232638:1  29304314:1	28776431:1  31077481:1
29487407:1  30980115:1	30564898:1  31626438:1	30618230:1  30537403:1
30235878:1  30249927:1	30979701:1  31001295:1	29331066:1  27261477:1
31069997:1  30486896:1	19138896:1  9876287:1  29696242:1  30228422:1
30751171:1  28414968:3	28602253:1  29937655:1	31001490:1  32075777:0
32037237:1  30006705:1	30207519:1  30776676:1	30470947:1  30483184:1
28498976:1  30786641:1	31895670:0  31670824:0	31009032:1  30235691:1
28234255:3

2) Final _fix_control setting for spfile considering current_setting_precedence
is YES
29331066:1  28965084:1	28776811:1  28498976:1	28567417:1  28558645:1
29132869:1  29450812:1	29687220:1  29939400:1	30232638:1  30001331:0
29304314:1  29930457:1	30028663:1  28144569:1	28776431:1  27261477:1
31069997:1  31077481:1	28602253:1  29653132:0	29937655:1  30347410:1
30602828:1  30896685:0	29487407:1  30998035:1	30786641:1  31444353:0
30486896:1  28999046:1	30902655:1  30681521:1	29302565:1  30972817:1
30222669:1  31668694:1	31001490:1  30198239:7	30980115:1  30616738:0
31895670:0  19138896:1	31670824:0  9876287:1  30564898:1  32075777:0
30570982:1  32037237:1	30927440:1  30822446:1	24561942:1  31625959:1
31579233:1  29696242:1	31626438:1  30228422:1	17295505:1  29725425:1
30618230:1  30008456:1	30537403:1  30235878:1	30646077:1  29657973:1
29712727:1  20922160:1	30006705:1  29463553:1	30751171:1  31009032:1
30063629:1  30207519:1	31517502:1  30617002:1	30483217:1  30235691:1
30568514:1  28414968:3	32014520:1  30249927:1	31580374:1  29590666:0
29435966:1  28173995:1	29867728:1  30776676:1	26577716:1  30470947:1
30979701:1  30483184:1	31001295:1  31191224:1	31974424:1  29385774:1
28234255:3  31459242:0	31082719:1  28708585:1	31821701:1  32107621:1
26758837:1  31558194:1	30781970:0  30142527:1	31143146:1  31961578:0
31496840:1  22387320:1

[...]


WARNING: final _fix_control setting for memory is not same as final
_fix_control setting for spfile. Please look at point 2 and 4 above to see the
differences.

PL/SQL procedure successfully completed.

And at least to me, this is not self-explaining.

But I trust it – and check my SPFILE afterwards.

*._fix_control='29331066:1','28965084:1','28776811:1','28498976:1','28567417:1','28558645:1','29132869:1','29450812:1','29687220:1','29939400:1','30232638:1','30001331:0','29304314:1','29930457:1','30028663:1','28144569:1','28776431:1','27261477:1','31069997:1','31077481:1','28602253:1','29653132:0','29937655:1','30347410:1','30602828:1','30896685:0','29487407:1','30998035:1','30786641:1','31444353:0','30486896:1','28999046:1','30902655:1','30681521:1','29302565:1','30972817:1','30222669:1','31668694:1','31001490:1','30198239:7','30980115:1','30616738:0','31895670:0','19138896:1','31670824:0','9876287:1','30564898:1','32075777:0','30570982:1','32037237:1','30927440:1','30822446:1','24561942:1','31625959:1','31579233:1','29696242:1','31626438:1','30228422:1','17295505:1','29725425:1','30618230:1','30008456:1','30537403:1','30235878:1','30646077:1','29657973:1','29712727:1','20922160:1','30006705:1','29463553:1','30751171:1','31009032:1','30063629:1','30207519:1','31517502:1','30617002:1','30483217:1','30235691:1','30568514:1','28414968:3','32014520:1','30249927:1','31580374:1','29590666:0','29435966:1','28173995:1','29867728:1','30776676:1','26577716:1','30470947:1','30979701:1','30483184:1','31001295:1','31191224:1','31974424:1','29385774:1','28234255:3','31459242:0','31082719:1','28708585:1','31821701:1','32107621:1','26758837:1','31558194:1','30781970:0','30142527:1','31143146:1','31961578:0','31496840:1','22387320:1'#added through dbms_optim_bundle package

Since the package does not add a date to the comment, it is really hard to say at first sight whether anything had changed.

I copy/pasted both entries into my favorite editor. And it is true, the entries are different. It looks to me as if the new settings get simply added at the end. But I didn’t do a parameter-by-parameter verification. 19.12.0 adds 13 fixes disabled by default – and the _fix_control parameter in my SPFILE has 13 additional fixes added at the end of the string.

Looks solid to me.

Cumulative?

Are the fixes cumulative as well? This was a question I received just a week ago. And yes, this is the case. I didn’t count the fixes in my SPFILE’s _fix_control parameter but when I count all the disabled fixes, it must be around 104 disabled fixes which you will enable. The DBMS_OPTIM_BUNDLE works in a way of carrying forward the settings.

When you enable the fixes in 19.12.0 as you may have never used the package before, it will enable 104 fixes in one task.

Disable?

When you want to disable all of the fixes, you will use this command:

execute dbms_optim_bundle.enable_optim_fixes('OFF','BOTH','NO');

And you will see that all fixes without exception get set to 0.

Bundle's _fix_control setting as per action:OFF
29331066:0  28965084:0	28776811:0  28498976:0	28567417:0  28558645:0
29132869:0  29450812:0	29687220:0  29939400:0	30232638:0  30001331:0
29304314:0  29930457:0	30028663:0  28144569:0	28776431:0  27261477:0
31069997:0  31077481:0	28602253:0  29653132:0	29937655:0  30347410:0
30602828:0  30896685:0	29487407:0  30998035:0	30786641:0  31444353:0
30486896:0  28999046:0	30902655:0  30681521:0	29302565:0  30972817:0
30222669:0  31668694:0	31001490:0  30198239:0	30980115:0  30616738:0
31895670:0  19138896:0	31670824:0  9876287:0  30564898:0  32075777:0
30570982:0  32037237:0	30927440:0  30822446:0	24561942:0  31625959:0
31579233:0  29696242:0	31626438:0  30228422:0	17295505:0  29725425:0
30618230:0  30008456:0	30537403:0  30235878:0	30646077:0  29657973:0
29712727:0  20922160:0	30006705:0  29463553:0	30751171:0  31009032:0
30063629:0  30207519:0	31517502:0  30617002:0	30483217:0  30235691:0
30568514:0  28414968:0	32014520:0  30249927:0	31580374:0  29590666:0
29435966:0  28173995:0	29867728:0  30776676:0	26577716:0  30470947:0
30979701:0  30483184:0	31001295:0  31191224:0	31974424:0  29385774:0
28234255:0  31459242:0	31082719:0  28708585:0	31821701:0  32107621:0
26758837:0  31558194:0	30781970:0  30142527:0	31143146:0  31961578:0
31496840:0  22387320:0

You may recognize that even in case you enable “all” fixes, some stay “off” even then such as 31459242, 31895670 and others, too.

Enable, or not enable?

That’s the question. We are updating docs and MOS notes right now to give a more clear guideline. But at the end of the day, it will remain your decision whether you enable the disabled fixes, or you just leave them disabled.

But this would be my recommendation.

  1. UPGRADE
    When you upgrade, e.g. from 12.2.0.1 to 19.12.0, then you should definitely enable the fixes before you start your testing. There is not much of a point to have them disabled. You will do a full stack application testing for a database upgrade anyway most likely. So please, make sure you enable the fixes as well.
  2. PATCHING
    When you patch, e.g. from 19.10.0 to 19.12.0, then there is no correct answer. We can argue that there is reason why we included these fixes. All together, for my example 19.10.0 to 19.12.0, there are 45 (19.11.0) plus 13 (19.12.0) new fixes disabled by default. And of course, we didn’t include those 58 fixes just for fun or because we were bored. On the other hand, you could see this as potentially 58 behavior changes which you may need to check and test. I use “behavior change” in this case in positive context as it is tied to a fix. But I know that some of your will see this totally opposite. Hence, in this case, it is fully up to you. I would enable them, but I see your points of keeping them disabled.
  3. NEW DATABASE
    Unfortunately, not even DBCA has any way to just turn on the fixes by default. So unless you don’t enable them, your new database deployment will not have the new fixes turned on. But especially in this case, I would strongly recommend to turn them on by default as soon as you deploy a new database.

Another thought based on my history at Oracle. As you may know, I started in Oracle Support a long while ago. From solving many many SRs over the year I can tell you that one of the most frustrating experiences is when a customer hits an issue, and you can tell your customer that this issue is solved already.

So mapping it to this case here, this means: We included 58 potentially behavior changing optimizer fixes in 19.11.0 and 19.12.0. But those are fixes, and not new bugs. So by keeping them disabled you may see yourself in a situation where a Support engineer tells you: “The fix is there, but as you didn’t enable it yet, you ran into this issue.”

To me, this will be a very negative and avoidable experience.

But again, I can see your arguments as well.

Coming back to my question: Should you enable _fix_controls with DBMS_OPTIM_BUNDLE?

So to me, there is a clear answer: When you upgrade and you deploy a new database, you clearly should enable the fixes. And when you patch, it depends a lot on your testing routine and tools. Even though I tend to recommend it, I can see also enough arguments to keep them disabled. It is up to you.

Further Links and Information

–Mike

Share this: