something I noticed today while patching the first database node in the super cluster with BP12 since this will be probably the last patch we will implement before going into UAT in december this year I decided to patch an isolated environment, from a Solaris side , no need to patch anything, since we were already on QFSDP july 2013.

the readme says following
“

This bundle includes fixes which can cause a plan change. These are installed in a disabled state by default.

To display such fixes included in the bundle , execute the following to get the list of bugs included in the current bundleSQL> connect / as sysdba
SQL> set serveroutput on;
SQL> exec dbms_optim_bundle.getBugsforBundle

To display such fixes included in all bundles till now say BP12, execute the following SQL> connect / as sysdba
SQL> set serveroutput on;
SQL> exec dbms_optim_bundle.getBugsforBundle(12)

To switch ON all fixes with plan changes included in bundles till nowSQL> exec dbms_optim_bundle.enable_optim_fixes(‘ON’,’Scope’);
Where Scope can be either of MEMORY or SPFILE or BOTH, the default is MEMORY.

To get the information on what needs to be added to init.ora to enables these fixesSQL> set serveroutput on;
SQL> exec dbms_optim_bundle.enable_optim_fixes(‘ON’,’INITORA’);

These can be disabled by following the steps mentioned below
% sqlplus /nolog
SQL> connect / as sysdba
SQL> exec dbms_optim_bundle.enable_optim_fixes(‘OFF’,’Scope’);
Where Scope can be either of MEMORY or SPFILE or BOTH, the default is MEMORY.

To disable the fixes enabled using init.ora , please remove the earlier added entries.

If you are rolling back then the above steps that were done to enable/disable fixes needs be undone manually


“

I am not sure if I am happy with the fact that BP can change the execution plans but I am happy that by default those changes are not put and Oracle gives you the choice

anyway on our environment this gave following 
exec dbms_optim_bundle.getBugsforBundle
BP12:
   16825679
   19174639
   16015637
   18798414
   18365267
   18304693
But what are those fixes/bugs about 
V$system_fix_control to the rescue …
select bugno,value ,sql_feature,description from v$system_fix_control where bugno in (16825679,19174639,16015637,18798414,18365267,18304693)
SQL> /
     BUGNO      VALUE SQL_FEATURE                    DESCRIPTION
———- ———- —————————— —————————————————————-
  16825679          0 QKSFM_ACCESS_PATH_16825679     Remember opn value is out of range or with in range
  19174639          0 QKSFM_OLD_PUSH_PRED_19174639   allow OJPPD with valid cases of multiple columns in predicate
  16015637          0 QKSFM_CBO_16015637             consider col bind for pred intersection
  18798414          0 QKSFM_JPPD_18798414            Unnest distinct view in IN/ANY subquery if candidate for JPPD
  18365267          0 QKSFM_JPPD_18365267            Allow JPPD into SPJ view with NO_MERGE hint
  18304693          0 QKSFM_STATS_18304693           only reinitialize table stats for column if table is analyzed

On By phil

Leave a Reply

Related Post