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
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,
I have the same issue with 12.2.0.1 – and my first impression was: There are no behavior changing optimizer fixes.
The MOS note for DBMS_OPTIM_BUNDLE patch promises to list the behavior changing fixes in the Update’s (RU) or Revision’s (RUR) Readme:
Oracle Support Document 2245178.1 (Database 12.2.0.1 Release Updates and Release Update Revisions Bugs Fixed Lists) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2245178.1
But when I browse up and down I don’t see any. Nor do I see any in the patches readme (for Jan 2018 for instance):
https://updates.oracle.com/Orion/Services/download?type=readme&aru=21862470
Would you mind to open an SR? I will check with my contacts internally as well.
Thanks
Mike
Thanks Mike, we will open an SR and see what we find!
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
That awesome Mike, thanks for the help and the followup!
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
Martin,
I _slightly_ disagree now after testing a bit.
What I did?
– I installed 12.2.0.1 vanilla and the newest OPatch
– I created a non-CDB and a CDB with one PDB
– DBMS_OPTIM_BUNDLE does not exist – this is expected
– I patched the home with the April 2018 RU
– I ran datapatch -verbose
– DBMS_OPTIM_BUNDLE exists – in the non-CDB and in both, the root container and the pdb
– I applied the Oct 2018 RU
– I ran datapatch -verbose
– I checked again in both databases:
– and I see ONE difference: it does not exist anymore in the PDB afterwards but still in the root container.
I’d say this is expected as you can’t set _fix_control on a per-PDB basis.
But let me double check with the guys who own the package.
Cheers,
Mike
Thanks for your effort.
I can only tell that one critical database of a customer, which was recently migrated from 11.2.0.4 to 12.2.0.1 nonCDB patched with latest October 2018 RU with full datapump does not have the package installed and we had some SQL regressions where I wanted to verify if the latest optimizer bugfixes change that behavior.
I verified that datapatch was run and registry$sqlpatch is populated with OCT18 entries.
Oracle Support does not have a clue whether the package should or should not be there. 😉
Regards,
Martin
Hi Martin,
the package should be there – I saw the SR and we are following up internally.
I’ll get you feedback once the folks have an idea why and how this happens.
Thanks,
Mike