Starting 12.2, Optimizer fixes included in the RUs are disabled by default.
To get the complete list of fixes - Oracle has introduced a package called dbms_optim_bundle. Since the RUs are cumulative in nature, this package would list the optimizer fixes that Oracle included in every RU.
Here is the output from my 19.12 database. I can see that there are 13 fixes in 19.12 ( call it a RU 12 or July 2021 bundle), 45 fixes on 19.11 and so on. In total there are 104 fixes and all of them are disabled by default.
SQL> exec 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: 9, 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
To enable these fixes, you run
SQL> execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES')
This will enable all the fixes mentioned above. With dbms_optim_bundle, you do not have an option to enable a particular fix. However, if you want to enable a particular fix, you could do it manually through the _fix_control parameter.
SQL> alter system set "_fix_control" = '22387320 : 1'
You can also find the current values in the view v$system_fix_control.
Why are these fixes disabled by default and should you really enable them on your system?
And the answer is - It depends !
These are optimizer fixes and can cause changes in execution plans. And these changes will most likely be "good" for you because Oracle has fixed a bug in your code. However, there is a rare possibility of a regression bug due to these fixes. Therefore, all changes needs to go through a comprehensive application testing cycle.
And Oracle wants Customers to have a consistent performance experience between different RUs. If a customer moves from 19.4 to 19.5 to 19.6 and so on- Oracle doesn't want you to have mixed experience in terms of overall application performance. This could be the reason why they are turned off in the first place.
Imagine running into one of these bugs. You will have to raise a SR, upload tons of trace files, escalate the SR with Oracle .... and after few days or weeks you find out that the fix is already included - just that it is not enabled. This is gonna be really frustrating.
If yours is a new database installation or if you are upgrading from a previous version say 11g or 12c I would suggest that you enable these fixes. There is absolutely no point in leaving them disabled.
But if you are moving between different RUs - then it is up to you to decide.
