Optimizer Features

Each time you upgrade the Oracle server (even with a patch release), you may find that some strange things happen to a few execution paths. Every release carries some changes to the optimizer code – sometimes enhancements, sometimes bug fixes – and every change might be one that just happens to do something nasty with your existing code.

A little feature that may help when you upgrade is the view v$system_fix_control. This is a view which lists a number of bug fixes that you can disable with the _fix_control parameter. (The parameter and view appeared 10.2.0.2, I believe).

One of the convenient details of this view is that it describes the bug fixes, and gives the Oracle release where the fix was enabled by default. So when you see an odd change in the optimizer’s behaviour on an upgrade, it’s worth a quick look in this view to see if you recognise your symptoms in one of the descriptions and the version numbers match.

Here’s a simple query, and the output from 10.2.0.3, that you could use:

set pagesize 60
set linesize 90
set trimspool on

set null N/A

column optimizer_feature_enable format a8
columun sql_feature   format a30

break on optimizer_feature_enable skip 1

spool opt_features

select
 optimizer_feature_enable,
-- sql_feature,
 description
from
 v$system_fix_control
order by
 to_number(substr(optimizer_feature_enable,1,2)),
 optimizer_feature_enable
;

spool off

OPTIMIZE DESCRIPTION
-------- ----------------------------------------------------------------
9.2.0    add new predicate to the VC-EXPR mapping

9.2.0.8  Enable code optimization for bitmap access path
         Pick view card from view qb instead of parent qb
         make NL comparable to HJ
         allow index skip scan with no index keys
         remove null first element from multicolumn inlist if possible
         Check for obj# for named view estimated card

10.1.0   do not consider no sel predicates in join selectivity sanity

10.1.0.5 Use index heuristic for join pred being pushed

10.2.0.1 kkoidc: add SI caching for index branch blocks
         do not copy kcc of columns in non-view side of a join predicate
         avoid semantically redundant predicates to be used
         generate transitive predicates across anti join predicates

10.2.0.2 tbl$or$idx$part$num() predicate causing peformance problems
         CPU cost estimation for unique access
         consider mjc if equi-joined pred is dropped in kkoipt
         min # of join permutations for starting table, new initial order
         ignore IS NOT NULL predicate as an index filter
         Generate distinct view in SU if candidate for JPPD
         Refine criteria for additional phase in JPPD
         optimize top-level predicate chain
         do not disable cartesian products if ORDERED hint is used
         no selectivity for transitive inequality predicates
         for cached NL table set tab_cost_io to zero
         do not clobber predicate during first-k row estimate phase
         use smallest table as first table in join card. initial ordering

10.2.0.3 Enhance view merging security checks for PL/SQL functions
         Enable star plan for 2 column part pruning index
         restore start position before typechecking(star transformation)
         ANSI syntax prevents table elimination (backport)
         Cost inlists as index filters as well as keys
         peek at any foldable exprssion during pruning
         try for subqueries before forcing in star trans with FACT hint
         skip internal ref columns for index only check for update/delete
         multiple signatures for selectivity func

N/A      enable tiny index improvements: consider small indexes as cachhe
         upgrade to row-read access instead of row-write
         Discount FFS cost using optimizer_index_cost_adj.

38 rows selected.

The column sql_feature appears in 11g, which is why it’s commented out for the example above. You could also select the column bugno  to get a bug number that can be checked on My Oracle Support (metalink) for further details. Obviously this isn’t a complete list of changes that might affect the optimizer – but it could be a very convenient starting point for trouble-shooting an optimizer problem.

The output from 11g is much longer with 406 items in 11.2.0.1, some of which go back to Oracle 8.0. So even if your production systems are running a lower version, it could be convenient to have a PC or other small box somewhere running the latest version.

Addendum:

The 11.2 manual describes this view with the following comment on the optimizer_feature_enable column: “Version on (and after) which the fix is enabled by default”. It occurs to me, however, that this isn’t entirely accurate – given the name of the column, it may be the setting of the parameter optimizer_features_enable rather than the version of the database that dictates which of the bug fixes are enabled.

If this is the case, then you might be able to set your optimizer_features_enable to a version than the database version, but still enable a bug fix that was registered for a higher version of the database. (See also the comment below from fidelinho.)