Data … as usual

When you upgrade oracle database version, you can be faced to a problem of CBO that changes its behaviour.

To do this, Oracle have introduced an undocumented parameter : “_fix_control” (Since 10.2.0.2). With this parameter, you can unable or disable a bug fixe to see, for example, if the CBO behaviour is controlled by a bug fix or not.

Off course, this kind of tests have to be implemented for testing purposes only !

To see what bug fix is implemented on your database, oracle have implemented two views V$SYSTEM_FIX_CONTROL and V$SESSION_FIX_CONTROL.

In these views, you will find information about the bug number, a short description, and the value of the parameter optimizer_features_enabled in which the bug fix is activated.

SQL> select bugno,value,description,optimizer_feature_enable,is_default from v$system_fix_control;

BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE IS_DEFAULT
---------- ---------- ---------------------------------------------------------------- ------------------------- ----------
3834770 1 Lift restriction on unnest subquery with a view 8.0.0 1
3746511 1 do not combine predicates from LNNVL 8.0.0 1
4519016 1 Pick view card from view qb instead of parent qb 9.2.0 1
3118776 1 Check for obj# for named view estimated card 9.2.0.8 1
4488689 1 ignore IS NOT NULL predicate as an index filter 10.2.0.2 1
2194204 0 disable push predicate driven by func. index into partition view 1
2660592 1 do not trigger bitmap plans if no protential domain index driver 8.1.7 1
2320291 1 push into table with RLS 9.2.0 1
2324795 1 add(remove) cluster index for push view 8.1.7 1
4308414 1 outer query must have more than one table unless lateral view 10.1.0.5 1
3499674 0 enable tiny index improvements: consider small indexes as cachhe 1
4569940 1 Use index heuristic for join pred being pushed 10.1.0.5 1
4631959 1 Refine criteria for additional phase in JPPD 10.2.0.2 1
4519340 1 Generate distinct view in SU if candidate for JPPD 10.2.0.2 1
4550003 1 do not consider no sel predicates in join selectivity sanity 10.1.0 1

.../...

In this example, we can see that the bug fix #4488689 is activated in our database (because of the value parameter set to 1).

We can see too that some og the buf fixes are not activated by default (ex: 2194204)

You can have a session view by querying the V$SESSION_FIX_CONTROL.

Now, if you want to test to desactivate a bug fix, you can do it by using the _fix_control undocumented parameter.

To disable a bug fix, you have to specify the bug number completed by ON or OFF.

For example:

-- To deactivate a single bug fix:
SQL> alter session set "_fix_control"='4488689:OFF';
-- To deactivate many bug fixes:
SQL> alter session set "_fix_control"='4488689:OFF','4631959:OFF','4519340:OFF';
-- To activate it, you have to use ON instead of OFF
SQL> alter session set "_fix_control"='4488689:ON';

If we control the V$SESSION_FIX_CONTROL view on these specific bug fixes, we can view that they are desactivated (value column equals 0).

SQL> select SESSION_ID,BUGNO,VALUE,OPTIMIZER_FEATURE_ENABLE,IS_DEFAULT
 2 from V$SESSION_FIX_CONTROL
 3 where session_id=sys_context('USERENV','SID') and bugno in (4488689,4631959,4519340);
 SESSION_ID BUGNO VALUE OPTIMIZER_FEATURE_ENABLE IS_DEFAULT
 ---------- ---------- ---------- ------------------------- ----------
 22 4488689 0 10.2.0.2 0
 22 4631959 0 10.2.0.2 0
 22 4519340 0 10.2.0.2 0