Oracle | DBMS_OPTIM

Oracle DBMS_OPTIM_BUNDLE Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose This package is created to manage (enable/disable) optimizer fixes provided as part of PSU/bundles. Optimizer fixes are those provided as part of bundle which has a fix-control and can possibly cause a plan change.

This package has existed in some previous versions of the database, was dropped most recently from 19.3 which is why it was dropped from the Library. Oracle reintroduced it with 19.4 which the Library did not research so, from our perspective, it was "new" again in 20c.

Be sure to read: 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 Update which Mike Dietrich discussed in his July 25, 2017 blog post: https://mikedietrichde.com/2017/07/25/dbms_optim_bundle-only-available-in-oracle-12-1-0-2/

AUTHID CURRENT_USER

Data Types TYPE dbms_optim_bugvalobtype ... TYPE dbms_optim_fctabtype ...

Dependencies

DBMS_ASSERT	DBMS_UTILITY	V_$INSTANCE
DBMS_LOB	GV_$INSTANCE	V_$PARAMETER
DBMS_OPTIM_BUGVALOBTYPE	GV_$PDBS	V_$PDBS
DBMS_OPTIM_FCTABTYPE	GV_$SPPARAMETER	V_$SYSTEM_FIX_CONTROL
DBMS_OUTPUT	GV_$SYSTEM_FIX_CONTROL	V_$SYSTEM_PARAMETER
DBMS_STANDARD	PLITBLM	XMLTYPE
DBMS_SYSTEM	UTL_FILE

Documented Yes: Packages and Types Reference

First Available 20c

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/dbmsoptim.sql
{ORACLE_HOME}/rdbms/admin/prvtoptim.plb

Subprograms

ENABLE_OPTIM_FIXES	LISTBUNDLESWITHFCFIXES	SET_FIX_CONTROLS
GETBUGSFORBUNDLE

ENABLE_OPTIM_FIXES

Enables and disables fixes dbms_optim_bundle.enable_optim_fixes( action IN VARCHAR2 DEFAULT 'OFF', scope IN VARCHAR2 DEFAULT 'MEMORY', current_setting_precedence IN VARCHAR2 DEFAULT 'YES);

exec dbms_optim_bundle.enable_optim_fixes('ON', 'MEMORY', 'NO');

      

      exec dbms_optim_bundle.enable_optim_fixes('ON', 'BOTH', 'YES');

      

      exec dbms_optim_bundle.enable_optim_fixes;

      

      exec dbms_optim_bundle.enable_optim_fixes('ON', 'INITORA');

GETBUGSFORBUNDLE

Displays opimizer bug#s applied as part of a given PSU/bundle dbms_optim_bundle.getBugsForBundle(bundleid IN NUMBER DEFAULT NULL);

exec dbms_optim_bundle.getBugsForBundle(170718);

      

      Bundles with fixes which can cause plan changes not applied yet

      

      PL/SQL procedure successfully completed.

LISTBUNDLESWITHFCFIXES

Displays bundle-ids and bundle-names that have fixes with _fix_controls i.e. fixes which may cause plan change dbms_optim_bundle.

exec dbms_optim_bundle.listbundleswithfcfixes;

      

      Bundles with fixes which can cause plan changes not applied yet

      

      PL/SQL procedure successfully completed.

SET_FIX_CONTROLS (new 23ai)

Invoked to enable/disable fixes dbms_optim_bundle.set_fix_controls( fix_control_string IN VARCHAR2, scope IN VARCHAR2 DEFAULT 'MEMORY', current_setting_precedence IN VARCHAR2 DEFAULT 'YES');

TBD