DBMS_OPTIM_BUNDLE – only available in Oracle 12.1.0.2

The DBMS_OPTIM_BUNDLE package got introduced with the April 2014 Exadata 11.2.0.4 Bundle Patch. And it got introduced for all Oracle 12.1.0.2 databases with the April 2017 Bundle Patch (BP). DBMS_OPTIM_BUNDLE is only available in Oracle 12.1.0.2.

For more information on this package on Exadata in Oracle 11.2.0.4 you may look at:

MOS Note:1942966.1: Exadata: DBMS_OPTIM Package

Purpose of DBMS_OPTIM_BUNDLE package in Oracle 12.1.0.2

The package got introduced to enable module bug fixes which cause an execution plan change in a disabled state. It is called the ‘Automatic Fix Control Persistence’ framework. It furthermore ensures that enabled fixes are preserved during subsequent DB Proactive BP applications.

No new module bug fixes (which cause an execution plan change) will be activated automatically.

The package does not exist since Oracle Database 12.2.0.1 anymore (see below).

How to use DBMS_OPTIM_BUNDLE

To view a listing of all “installed but disabled” execution plan bug fixes from the most recent bundle (RU) applied:

SQL> set serveroutput on;
SQL> execute dbms_optim_bundle.getBugsforBundle;
12.1.0.2.170718DBBP:
     Bug: 21971099,  fix_controls: 21971099
     Bug: 22159570,  fix_controls: 22159570
     Bug: 22119679,  fix_controls: 21802552
     Bug: 21509656,  fix_controls: 21509656

PL/SQL procedure successfully completed.

To view a listing of the “installed but disabled” execution plan bug fixes from BPs up to and including BP 171017:

SQL> execute dbms_optim_bundle.getBugsforBundle('170718');

12.1.0.2.170718DBBP:
     Bug: 21971099,  fix_controls: 21971099
     Bug: 22159570,  fix_controls: 22159570
     Bug: 22119679,  fix_controls: 21802552
     Bug: 21509656,  fix_controls: 21509656

PL/SQL procedure successfully completed.

As there is only one RU present at the moment, both query results look the same.

Now you may still not know what the fixes are meant for. My friend Philippe has posted a nice query I’ll borrow here:

SQL> select bugno, value ,sql_feature, description from v$system_fix_control 
      where bugno in (21971099,22159570,21802552,21509656);

    BUGNO VALUE SQL_FEATURE    DESCRIPTION
--------- ----- -------------------------------- --------------------------------------------------------------
 21509656     0 QKSFM_CARDINALITY_22159570  allocate table/view level stats when generating view
 22159570     0 QKSFM_DYNAMIC_SAMPLING_21802552  correct non-popular region cardinality for hybrid histogram
 21802552     0 QKSFM_CARDINALITY_21971099  correct cardinality adjusted by DS
 21971099     0      do not adjust cardinality when WiF subquery is over partitions

Value=0 means “disabled”, value=1 obviously means then “enabled”.

How to enable fixes

To enable all of these fixes you will issue this command:

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
21971099:1  22159570:1 21802552:1  21509656:1

Taking current instance CDB1 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
21971099:1  22159570:1 21802552:1  21509656:1

3) Current _fix_control setting in memory:
21971099:1  22159570:1 21802552:1  21509656:1

4) Final _fix_control setting for memory considering current_setting_precedence
is YES
21971099:1  22159570:1 21802552:1  21509656:1

PL/SQL procedure successfully completed.

Please find all command options in:
MOS Note:2147007.1 – Automatic Fix Control Persistence (FCP) for Database Proactive Bundle Patch

DBMS_OPTIM_BUNDLE.ENABLE_OPTIM_FIXES(
     action                     => 'ON' | 'OFF' ,
     scope                      => 'MEMORY' | 'SPFILE' | 'BOTH' | 'INITORA' ,
     current_setting_precedence => 'YES' | 'NO' )

The spfile/init.ora

In the init.ora you’ll find the following line afterwards:

*._fix_control='21971099:1','22159570:1','21802552:1','21509656:1'#added through dbms_optim_bundle package

Wait a minute …

The above call enabled ALL fixes. But what if you’d like to enable only one fix?

You can generate the code piece quite easily:

SQL> exec DBMS_OPTIM_BUNDLE.ENABLE_OPTIM_FIXES('ON','INITORA','YES');
DBMS_OPTIM command:  dbms_optim_bundle.enable_optim_fixes('ON', 'INITORA', 'YES')

Please use below command in init.ora file:
_fix_control='21971099:ON','22159570:ON','21802552:ON','21509656:ON'

PL/SQL procedure successfully completed.

Now you have the parameter to set in your init.ora – just exclude the bugs you don’t want to enable.

Why does DBMS_OPTIM_BUNDLE not exist since Oracle 12.2.0.1?

Beginning from Oracle Database 12.2.0.1 and higher, RDBMS fixes that are covered by fix control will be delivered with the fix disabled by default. The patch readme will have the following notation:

“This patch introduces fix control for one or more fixes contained herein. These fixes are disabled by default and will have to be explicitly enabled via alter session/system commands to persist in pfile/spfile as appropriate”

Quick check with the July 2017 RU (Patch 26123830):
MOS Note: 2245178.1 – 12.2.0.1 Database Release Update – List of Fixes in each RU/RUR
There’s no such fix included in the July RU for Oracle Database 12.2.0.1.

oracleops-support

Wikipedia