DBMS_OPTIM_BUNDLE Package in Oracle 12.2 and 18c

Yes, I did blog about the DBMS_OPTIM_BUNDLE package in earlier Oracle releases before. And I thought (and it was documented this way) that it will disappear in Oracle 12.2 and newer. But in fact you’ll find the DBMS_OPTIM_BUNDLE package in Oracle 12.2 and 18c. Most likely …

This is a very interesting case and you may see also my previous blog posts:

DBMS_OPTIM_BUNDLE Package in Oracle 12.2 and 18c

First of all, as there’s no documentation in the documentation about the DBMS_OPTIM_BUNDLE package what it is meant for. MOS Note:2147007.1 – Automatic Fix Control Persistence (FCP) for Database Proactive Bundle Patch tells you more about it. In brief, it deals with the – off by default – behavior changing optimizer fixes shipped in Bundle Patches and Updates. I describe how to use of the package in this blog post from July 25, 2017.

MOS Note:2147007.1 – Automatic Fix Control Persistence (FCP) for Database Proactive Bundle Patch has gotten a change in January 2018 documenting that DBMS_OPTIM_BUNDLE exist now in Oracle Database 12.2 and beyond as well.

Initially it was meant to disappear as you can see from this bug 26671620 (DBMS_OPTIM_BUNDLE package did not get removed during a database upgrade from 12.1 to 12.2) and this MOS Note:Oracle 2310318.1 – Package SYS.DBMS_OPTIM_BUNDLE is Showing as Invalid After Upgrade to 12.2.0.1.

However, DBMS_OPTIM_BUNDLE package is indeed needed in Oracle 12.2.0.1 and Oracle 18c. Therefore it will reappear. Once you applied the Jan 2018 Update or later (or the Apr 2018 Revision 1 or the July 2018 Revision 2) you’ll find the package again in your database. And it will be present in Oracle 19c by default as well.

SQL> desc DBMS_OPTIM_BUNDLE

PROCEDURE ENABLE_OPTIM_FIXES
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ACTION 			VARCHAR2		IN     DEFAULT
 SCOPE				VARCHAR2		IN     DEFAULT
 CURRENT_SETTING_PRECEDENCE	VARCHAR2		IN     DEFAULT

PROCEDURE GETBUGSFORBUNDLE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUNDLEID			NUMBER			IN     DEFAULT

PROCEDURE LISTBUNDLESWITHFCFIXES

Actually I like it – I’d just have wished that things would have been documented a bit more transparently. This will happen soon I’d guess.

Very Important

Finally, there’s something really important at the end of MOS Note:2147007.1 – Automatic Fix Control Persistence (FCP) for Database Proactive Bundle Patch: The list of optimizer behavior changing fixes turned off by default for each Update (RU):

You can get a full list by accessing MOS Note:2147007.1 – then scroll down to the button of the note.

–Mike

10 thoughts on “DBMS_OPTIM_BUNDLE Package in Oracle 12.2 and 18c

  1. Interesting, thank you! Bug 26476244 – port/implement dbms_optim package on 12.2.0.1 (Doc ID 26476244.8) documents the port from 12.1.0.2 to RU 171017 for 12.2.0.1. Yet I have databases on that patch and when I run any of the procedures in this package in this version, they show zero results, just the . I can’t find anywhere on MOS that lists the 12.2.0.1 disabled optimizer fixes, the way the end of that article you linked to does for 12.1.0.2. Not sure if that means there aren’t any or if it’s not working. Expected, or open SR, I wonder?

    12201_171017> exec dbms_optim_bundle.listBundlesWithFCFixes();

    Bundles with fixes which can cause plan changes not applied yet

    • Tyler,

      actually the colleague in the US I’m in contact with recommended the following:
      exec dbms_optim_bundle.getBugsforBundle
      should be used to figure out if there are any.

      See here:
      SQL> exec dbms_optim_bundle.getBugsforBundle;

      12.2.0.1.180116DBRU:
       Bug: 25476149, fix_controls: 25476149
       Bug: 23249829, fix_controls: 23249829
       Bug: 26019148, fix_controls: 26019148

      PL/SQL procedure successfully completed.

      Does this help?
      Thanks

    • Tyler,

      actually the colleague in the US I’m in contact with recommended the following:
      exec dbms_optim_bundle.getBugsforBundle
      should be used to figure out if there are any.

      See here:
      SQL> exec dbms_optim_bundle.getBugsforBundle;

      12.2.0.1.180116DBRU:
       Bug: 25476149, fix_controls: 25476149
       Bug: 23249829, fix_controls: 23249829
       Bug: 26019148, fix_controls: 26019148

      PL/SQL procedure successfully completed.

      Digging down further:

      SQL> select bugno, value ,sql_feature, description from v$system_fix_control
      where bugno in (25476149,23249829,26019148);
      2
      BUGNO VALUE SQL_FEATURE DESCRIPTION
      ———- ———- —————————————————————- —————————————————————-
      26019148 0 QKSFM_OR_EXPAND_26019148 Allow ORE in select list subq
      25476149 0 QKSFM_ACCESS_PATH_25476149 impose a limit on memory used by bitmap access paths
      23249829 0 QKSFM_CARDINALITY_23249829 amend UNION-ALL cardinality estimate in presence of not null pre

      Does this help?
      Thanks

      • Ah thanks … for that command still nothing, on 171017. Perhaps for 171017 there are none … I’ll still open an SR to confirm.

        1 select action_time, action, version
        2 from registry$history
        3 where version like ‘12.2%’
        4 and action_time is not null
        5* order by action_time
        SQL> /

        ACTION_TIME ACTION VERSION
        —————————— ——————– ——————————
        18-JAN-18 11.18.42.889312 AM UPGRADE 12.2.0.1.0
        18-JAN-18 11.19.57.176922 AM APPLY 12.2.0.1.171017OJVMPSU

        2 rows selected.

        dvrace01:CRSEDV1:SYS> set serveroutput on
        dvrace01:CRSEDV1:SYS> exec dbms_optim_bundle.getBugsforBundle;

        Bundles with fixes which can cause plan changes not applied yet

        PL/SQL procedure successfully completed.

        • Tyler,

          yes, I think in the October one there weren’t any. The 3 I mention below came in with the Jan 2018 one. Still I believe it should be mentioned in the README as promised – and it wouldn’t harm to have them in the MOS note as well. I’m discussing this right now …

          Cheers,
          Mike

  2. Hello Mike,

    it seems that with recent 12.2 RU (oct 2018), the package is does no longer exist. I have opened an SR for it and was told that that is expected. The package is no longer needed. Still unsure, how FCP is implemented without the package.

    MOS: 2310318.1
    MOS: 2398897.1
    SR 3-18908518201 : Package DBMS_OPTIM_BUNDLE missing in 12.2.0.1.181016 Database

    • Martin,

      BIG thanks for bringing this to my attention – I wasn’t aware that it disappeared again.
      There’s a Bug 26476244 – PORT/IMPLEMENT DBMS_OPTIM PACKAGE ON 12.2.0.1
      which has several requests to include it into various patch bundles. Now for Oct18 and Jan19 it says “bootstrapped” whatever this means. I’ll check if there was a change and post an updated as soon as I know.

      Thanks!
      Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.