Oracle DBMS_OPTIM_BUNDLE
Version 23c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 again most recently from 19.3 which is why it was again dropped from the Library. Oracle reintroduced it with 19.4 which the Library did not research so, from our perspective, it is "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 PLITBLM V_$SYSTEM_PARAMETER
DBMS_STANDARD UTL_FILE XMLTYPE
DBMS_SYSTEM    
Documented Yes: Packages and Types Reference
Exceptions
Error Code Reason
   
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
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 23c)
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

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_QOPATCH
OPATCH
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx