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.
Photo by paolo candelo on Unsplash
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.
- UPGRADE
When you upgrade, e.g. from 12.2.0.1 to 19.18.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. - PATCHING
When you patch, e.g. from 19.14.0 to 19.17.0, then I would not enable them unless (and this is very important) I’d done proper testing with tools such as SQL Performance Analyzer. Of course, there is reason why we included these fixes. All together, for my above 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. Certainly, I know that you may see this the other way round. I would bring it down to a summary saying: You did proper and distinct testing: enable them when you patch. And if you didn’t do such testing, then keep them disabled. - 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 recommend to turn them on by default as soon as you deploy a new database. But again, the devil is in the detail. One thing I would try to avoid is having now environments with different settings. We keep telling you that you should standardize as much as possible.
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 all 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 you clearly should enable the fixes. And when you patch, it depends a lot on your testing routine and tools. If you are not able to do proper testing, be cautious. And even when you deploy a new database, for the individual database I would always enable the fixes. But your database is part of a larger environment, and hence, you may not want to have various databases on the same patch level with varying settings.
Further Links and Information
- You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0
- How and why I joined Oracle
- In case you miss DBMS_OPTIM_BUNDLE … again
- In case you miss DBMS_OPTIM_BUNDLE in 12.2
- DBMS_OPTIM_BUNDLE Package in Oracle 12.2 and 18c
- DBMS_OPTIM_BUNDLE – only available in 12.1.0.2, isn’t it?
- MOS Note: 2147007.1 – Managing “installed but disabled” bug fixes in Database Release Updates using DBMS_OPTIM_BUNDLE
–Mike
Hi Mike,
I see your arguments but usually I tend to leave the fix controls disabled.
One of the main reason is also to stay in the “main” stream: as the fix controls are disabled by default, I consider (and I may be wrong) that how most of customers are running their databases that way. Then staying like this should make things easier for support as we keep close to the standard. And honestly, when I have an issue I prefer to have a fix quickly (one-off or fix control) because the issue is known.
The other reason is that patching comes to a regular basis and the idea is to limit the testing because the main goal is the security fixes.
About the upgrades, I like the idea to enable the fixes because during upgrade testing can be done deeper.
But we you consider an environment more globally, it starts to be more difficult as you end very quickly with many different configurations. Then keeping a standard and enable only the fix control for known issue looks easier.
It brings me one question that I was not able to check yet:
– are the fix controls enabled in the Oracle Cloud managed databases?
Best Regards,
Nicolas
Hi Nicolas,
I see your points – and I doubt that many will apply the _fix_controls with patching due to the testing topic.
We try it – but I can speak only for my own systems.
With my Support background, I know just that it is not a pleasant situation when we included a fix, you have it installed but it is disabled. Now you open an SR and I tell you: “If you had just enabled the _fix_controls …”. That is my point why I tend to do it proactively since we include fixes not just for fun.
But I can see your arguments.
And no idea about cloud DBs.
Cheers,
Mike
First I want to say thank you, once again. Your posts about updates are a constant reminder to patch. And of course has helped me in the past with issues we had that I’ve read about here before it happened to us.
This DBMS_OPTIM_BUNDLE series prompted me to try this with one of our databases. Nice thing. It’s patched to 19.11, datapatch is executed, but dbms_optim_bundle.listBundlesWithFCFixes only knows about fix controls up to 19.8.
I’m looking forward to my newest SR to keep me busy. 😉
Hi Jan,
please run:
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/creating-additional-data-dictionary-structures.html#GUID-571DA1D1-5274-4763-99B4-B1FF60E79F9F
==> utlfixdirs.sql
This should fix it.
Blog post is in the making.
Cheers,
Mike
Wow, thank you, how did you guess that the update was done by moving to a new ORACLE_HOME? 😀
I’ve never heard of this script, but I see that I really need to use this in future. Unfortunately, listBundlesWithFCFixes did not change it’s output. Is there some kind of job/task that triggers a re-read of this information (other than a database restart)?
Ok, a colleague just stumbled across your blog post https://mikedietrichde.com/2019/06/06/in-case-you-miss-dbms_optim_bundle-again/ and the comments below that.
the output of utlfixdirs shows that the directories ORACLE_HOME, ORACLE_OCM_CONFIG_DIR, ORACLE_OCM_CONFIG_DIR2, PREUPGRADE_DIR, SDO_DIR_ADMIN, XMLDIR and XSDDIR are being updated. There are several others, that are unchanged. Namely
DBMS_OPTIM_ADMINDIR
DBMS_OPTIM_LOGDIR
And as posted in the comments of said other blog post: those are the two directories that are needed for this package to work.
After changing them -> Everything works
In any way: Thanks for your pointer to this script
Thanks – and glad it works now!
Cheers,
Mike
Hi Mike,
I tried to find the answers for two questions I still have even reading your valuable blog posts as well as MOS Note 2147007.1 but they are still unanswered. The first question applies to Multitenant: the MOS Note tells me that if enabling optim fixes in the root container “the concept of inheritance would mean that those same execution plan bug fixes may become visible in all of the associated PDBs as well”. For me that means if I have a single tenant DB I would run the package only in the CDB$root and have the fixes enabled in both containers. What about the seed pdb? And what if I have more than one pdb? The second question is related to Dataguard environment: MOS Note says running the package “when the standby database is either activated or a graceful switchover is done”. Activated means ‘failover’? Im quite sure that bringing the standby temporarily in readonly mode is not sufficient for running the package.
Thanks & regards
Axel D.
Hi Axel,
correct – the PDBs inherit the settings from the CDB$ROOT unless you overwrite it again.
But in this specific case I wonder if you can really overwrite them – I would need to try this out as I would expect then a different setting in v$system_parameter for my PDB.
The SEED does not matter since you don’t work with it. When you provision a new PDB, it gets the settings from the ROOT inherited – unless you overwrite it again.
Regarding DG, “activated” means when you decouple log apply and activate it. This could be after converting it to snapshot standby, or for a failover.
Cheers,
Mike
Cheers,
Mike
Hi Mike,
On our recent 12.1.0.2 > 19.14 upgrade we followed the recommendation here and enabled all of the _fix_control optimizer changes.
Post upgrade we have a number of SR’s running relating to performance – with one SR (3-30242103511) recommending we remove all of these setting (except for 30786641).
This is based on MOS note – Things to Consider to Avoid SQL Performance Problems on 19c (Doc ID 2773715.1)
which states “Some of the Optimizer bug fixes (not all) are controlled by _FIX_CONTROL setting and are DISABLED by default in 19c when installed either through one-off backport or through RUs. For one-off patches, please refer to the corresponding Patch Readme carefully and enable the fix control in order to activate the fix.”
The SR is progressing and we are trying many things.
Can you update / advise on the latest thinking regarding _fix_control post 12c>19c upgrade?
Thanks
Des
Hi Des,
I disagree with Support’s view here.
We in Development have released guidance to enable them when you:
a) upgrade and
b) create a new database
Patching is different – but if there is an issue, this needs to be treated via an SR/bug.
The fix you refer to has the tagline “Example”. So I guess this should only demonstrate how to enable a single fix.
Cheers
Mike
on standby I can add the _fix_control parameter to the spfile without executing enable_optim_fixes. right?
Hi Alex,
I don’t have a standby environment to check with right now. But my assumption no.1 would be that the call on PROD will propagate it to the standby. If it doesn’t then you could manually add it certainly. But I really wonder whether it gets propagated (IMHO, it should).
Thanks
Mike
Hi Alex,
Daniel and I finally tested this.
1. When you execute DBMS_OPTIM_BUNDLE on the primary, it will NOT be propagated to the standby
2. In order to set DBMS_OPTIM_BUNDLE on the standby, you need to use a ALTER SYSTEM or put the standby in OPEN mode (stop redo apply before), and then set it there and bring it back into managed recovery mode.
Cheers,
Mike
Russ pointed me to this article today. Thanks Mike!
Hello Mike,
One of my customer have upgraded to “Database Release Update : 19.23.0.0.240416 (36233263)” they have enabled almost 338 optimizer fixes. they have tested turning on all hidden optimizer fixes to see if they would improve performance. After doing so the application started reporting numerous problems, the alert log showed multiple core dumps, and the trace files in question had master key does not exist in the wallet errors. They turned the optimizer fixes back off and the problem went away. Now there is no way to find out which fixes were causing performance problem out of 338.their question is is there any way to determine which optimizer fix is causing the issue?
We have suggested From Bug it is NOT possible to say which FIX Control (out of 338) might have caused this issue.
So instead of 1 at a time, it suggested to test by doing elimination process of 50%. to see which has problem.
For example,
apply first 119 , then next 60, then 30 … 15, , 8 .. 4 … 2 etc.
Any suggestion Mike.
Thanks,
Meeta Verma
@Oracle CSS
Please drop me an email, then we can involve Nigel.
Cheers,
Mike
Hi Mike,
after enabling all and disabling all, there are 3 records in v$system_fix_control with is_default=0 and value=0.
those records were enabled before:
28234255 3 QKSFM_OR_EXPAND_28234255 allow more cases for interleaving ORE with SU
26491973 1 QKSFM_PQ_26491973 performance issue in min/max with pq on subpartitioned table
34225667 1 QKSFM_PQ_34225667 dump PX plan signature diagnostic info
bug or feature?
regards
roland
Hi Roland,
I can sense only that something has been turned out to be sensitive in some environments, and the powers in charge decided to turn those after in the next round.
Or, in the case of 34225667 (the last one in your list), the bug has been put into 85 meaning: Fix superseeded.
So, in consequence, there is a new fix for 34225667, and therefore 34225667 had to be turned OFF (==> set to “0” now).
The new one is:
Bug 35001214 – ADDING TRACING RELATED TO SIGNATURE MISMATCH IN PX BLACKBOX FOR ORA-12850
which did come in with 19.22. and made the setting for 34225667 obsolete.
I guess it is a similar story for the other two ones.
Cheers,
MIke
Hi Mike, what happens to the fixed base plans if we activate the Optim Fixes? Could there be any side effects since the cost calculation and everything else was done with a different configuration? We want to activate the fixes but we don’t know what might happen in these cases and what the next steps would be. Thank you very much for your help.
Hi Carlos,
it depends.
When you enable them with patching, you need to do proper testing. Customers reported very positive results but I know also a few cases where one or two out of the hundreds of fixes created unexpected trouble.
For upgrades, we highly recommend to start with these fixes being on.
Cheers,
Mike