DBMS_OPTIM_BUNDLE package got introduced with the April 2014 Exadata 18.104.22.168 Bundle Patch. And it got introduced for all Oracle 22.214.171.124 databases with the April 2017 Bundle Patch (BP).
DBMS_OPTIM_BUNDLE is only available in Oracle 126.96.36.199.
Addition March 6, 2018:
See the updated blog post about DBMS_OPTIM_BUNDLE available in Oracle 12.2 and 18c onwards.
For more information on this package on Exadata in Oracle 188.8.131.52 you may look at:
Purpose of DBMS_OPTIM_BUNDLE package in Oracle 184.108.40.206
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 220.127.116.11 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; 18.104.22.168.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'); 22.214.171.124.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' )
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 126.96.36.199?
Beginning from Oracle Database 188.8.131.52 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 – 184.108.40.206 Database Release Update – List of Fixes in each RU/RUR
There’s no such fix included in the July RU for Oracle Database 220.127.116.11.