You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0

It’s been a while since I blogged about the DBMS_OPTIM_BUNDLE – and to me, the package is still sort of a mystery. Ideally you can least and enable or disable behavior changing optimizer fixes per RU. And in Oracle 19.10.0, you will find a new surprise. So You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0.

You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0

Photo by Jonas Denil on Unsplash

Quick Recap

Just in case you haven’t dealt with DBMS_OPTIM_BUNDLE yet or for a while, a quick recap.

With this procedure, you can list the available patches to turn on or off at will:

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

And if you’d like to check which fixes are installed BUT disabled, use this procedure:

SQL> execute dbms_optim_bundle.getBugsforBundle(201020D);

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

19.8.0.0.200714DBRU:
    Bug: 29304314,  fix_controls: 29304314
    Bug: 29930457,  fix_controls: 29930457

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

PL/SQL procedure successfully completed.

In addition, you can enable the fixes – but the general call does not allow you to enable it fix by fix. So you may need to tweak your preferred setting with an explicit _fix_control instead.

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

January 2021 Release Update 19.10.0

Now when you plan to check the available patches in Oracle 19.10.0, you’ll see this:

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

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

PL/SQL procedure successfully completed.

There are no 19.10.0 fixes listed but instead the 19.9.0 ones.

Ups.

What happens now when you try to enable the 19.10.0 optimizer fixes:

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  29304314:1	29930457:1  27261477:1
31069997:1  31077481:1	28602253:1  29653132:0	29937655:1  30347410:1
30602828:1  30896685:0

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

1) Current _fix_control setting for spfile:
None

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  29304314:1	29930457:1  27261477:1
31069997:1  31077481:1	28602253:1  29653132:0	29937655:1  30347410:1
30602828:1  30896685:0

3) Current _fix_control setting in memory:
29331066:0  28965084:0	28776811:0  28498976:0	28567417:0  28558645:0
29132869:0  29450812:0	29687220:0  29304314:0	29930457:0  27261477:0
31069997:0  31077481:0	28602253:0  29653132:0	29937655:0  30347410:0
30602828:0  30896685:0

4) Final _fix_control setting for memory considering current_setting_precedence
is YES
29331066:0  28965084:0	28776811:0  28498976:0	28567417:0  28558645:0
29132869:0  29450812:0	29687220:0  29304314:0	29930457:0  27261477:0
31069997:0  31077481:0	28602253:0  29653132:0	29937655:0  30347410:0
30602828:0  30896685:0

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.

At first, this output is not easy to digest. And it is not well formatted either.

What happens?

But what did you get here? You enabled all fixes – until 19.9.0. But not the 19.10.0 ones. I did the formatting work. And the 20 fixes you’d enable are exactly the 20 fixes listed from 19.4.0 – 19.9.0. But you didn’t enable the 19.10.0 fixes.

The strange “order” you may recognize is simply coming from the fact that the first _fix_control listed is “29331066” – this is the change which came in in 19.4.0.

How to fix this?

Thanks to Kálmán Lehotai for bringing this to my attention. I was not aware that there is an issue. But when you check MOS Note:  2725758.1 – Oracle Database 19c Release Update & Release Update Revision January 2021 Critical Issues, you will find this section:

You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0

And it points you to Patch 31862593.

You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0

Downside of applying this patch: It is not RAC rolling installable as the readme states.

I downloaded, unzipped and applied the patch in my environment:

And I realized to my surprise that it takes quite a while to get the patch applied – in my tiny lab environment. The step “Patching component oracle.rdbms, 19.0.0.0.0…” took minutes for whatever reason.

I guess, these were the reasons why it took so long:

[Feb 1, 2021 10:33:04 AM] [INFO]    [OPSR-TIME] Cooked inventory loaded successfully
[Feb 1, 2021 10:33:04 AM] [INFO]    CUP_LOG: Found poh CUP 30557433 is a subset of other poh CUP: 30869156
[Feb 1, 2021 10:33:04 AM] [INFO]    CUP_LOG: Found poh CUP 30557433 is a subset of other poh CUP: 31281355
[Feb 1, 2021 10:34:33 AM] [INFO]    Checking if Oracle Home has components required by patches...
[Feb 1, 2021 10:34:33 AM] [INFO]    CheckMissingComps: Cached file does not exist or is invalid, re-build prereq result.

and:

[Feb 1, 2021 10:41:57 AM] [INFO]    CUP_LOG: Found poh PLO 31862593 implicitly overlays poh CUP: 31771877
[Feb 1, 2021 10:41:57 AM] [INFO]    CUP_LOG: Found poh PLO 31862593 implicitly overlays poh CUP: 32218454
[Feb 1, 2021 10:43:22 AM] [INFO]    [OPSR-TIME] Patch 31862593 saved to inventory
[Feb 1, 2021 10:43:22 AM] [INFO]    ApplySession: Skip patch verification.
[Feb 1, 2021 10:43:22 AM] [INFO]    [OPSR-TIME] Finished applying patch "31862593" to local system

I couldn’t find instructions to execute datapatch. So I will try it without.

Same calls, now with 31862593 in place

Let me repeat my checks from above:

SQL> execute dbms_optim_bundle.getBugsforBundle;

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

Ah, now I can see the included fixes.

SQL> execute dbms_optim_bundle.getBugsforBundle(210119);

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

19.8.0.0.200714DBRU:
    Bug: 29304314,  fix_controls: 29304314
    Bug: 29930457,  fix_controls: 29930457

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

Quite a long list in 19.10.0 containing 22 fixes. But we expected this. We managed to get a lot of important fixes into 19.10.0 to avoid the need for so many one-offs and merges.

Let me enable them to see what I will get in the SPFILE:

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  29304314:1	29930457: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

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

1) Current _fix_control setting for spfile:
None

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  29304314:1	29930457: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

3) Current _fix_control setting in memory:
29331066:1  28965084:1	28776811:1  28498976:1	28567417:1  28558645:1
29132869:1  29450812:1	29687220:1  29304314:1	29930457:1  27261477:1
31069997:1  31077481:1	28602253:1  29653132:0	29937655:1  30347410:1
30602828:1  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

4) Final _fix_control setting for memory considering current_setting_precedence
is YES
29331066:1  28965084:1	28776811:1  28498976:1	28567417:1  28558645:1
29132869:1  29450812:1	29687220:1  29304314:1	29930457:1  27261477:1
31069997:1  31077481:1	28602253:1  29653132:0	29937655:1  30347410:1
30602828:1  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

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.

This is the setting I have now in my SPFILE:

*._fix_control='29331066:1','28965084:1','28776811:1','28498976:1','28567417:1','28558645:1','29132869:1','29450812:1','29687220:1','29304314:1','29930457: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'#added through dbms_optim_bundle package

All together, 42/43 fixes get enabled/disabled with this call.

Do you really need the fix?

Now Kálmán’s question was: Do I need to apply the fix?

I was wondering as well – and this is the reason why I write this blog post. Technically, all should work fine even without the patch if you know the correct _fix_control setting to put into your SPFILE. So let me check this – now without applying patch 31862593:

SQL> alter system set "_fix_control"='29331066:1','28965084:1','28776811:1','28498976:1','28567417:1','28558645:1','29132869:1','29450812:1','29687220:1','29304314:1','29930457: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' sid='*' scope=both;

Works for me.

BUT … the problem is: DBMS_OPTIM_BUNDLE does not know about the fixes. When you check with:

SQL> exec dbms_optim_bundle.listBundlesWithFCFixes

bundleId: 190719,  bundleName: 19.4.0.0.190719DBRU
bundleId: 191015,  bundleName: 19.5.0.0.191015DBRU
bundleId: 200414,  bundleName: 19.7.0.0.200414DBRU
bundleId: 200714,  bundleName: 19.8.0.0.200714DBRU
bundleId: 201020,  bundleName: 19.9.0.0.201020DBRU

you’ll recognize that it is not aware of 19.10.0.

So if you’d ask me, I’d rather tend to apply this fix when you apply 19.10.0. As you may remember, you can do this in one action with -applyRU and -applyOneOffs. Just make sure to keep the unzipped fixes in strictly separated subdirectories.

Further Links and Information

–Mike

Share this: