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 – only available in Oracle 12.1.0.2 – or not?
• Fixes for Adaptive Features are included in Oracle 12.1.0.2 October 2017 BP
• Package Differences between Oracle 11.2.0.4 and 12.1.0.2?

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