Oracle Scratchpad

December 22, 2009

Optimizer Features

Filed under: CBO,Troubleshooting — Jonathan Lewis @ 6:53 pm BST Dec 22,2009

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 lower 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.)

9 Comments »

  1. Jonathan,

    It appears the view v$system_fix_control is only available on 10.2.0.3 or higher. Is that correct? I tried it on 10.2.0.1 and got ‘table or view does not exist’.

    jbrock@orcl-local> select synonym_name from dba_synonyms where synonym_name like ‘V$SYSTEM_%';

    SYNONYM_NAME
    ——————————
    V$SYSTEM_CURSOR_CACHE
    V$SYSTEM_EVENT
    V$SYSTEM_PARAMETER
    V$SYSTEM_PARAMETER2
    V$SYSTEM_WAIT_CLASS

    jbrock@orcl-local> select view_name from dba_views where view_name like ‘V_$SYSTEM_%';

    VIEW_NAME
    ——————————
    V_$SYSTEM_PARAMETER
    V_$SYSTEM_PARAMETER2
    V_$SYSTEM_WAIT_CLASS
    V_$SYSTEM_EVENT
    V_$SYSTEM_CURSOR_CACHE

    Comment by Jimmy Brock — December 22, 2009 @ 7:55 pm BST Dec 22,2009 | Reply

  2. A Good Day ……

    … in the Oracle blogosphere. Well, whether all these posts were ‘today’ depends on timezones, I suppose, but it’s today that I read them. My morning started with a read of this fine post by Cary Millsap. I immediately shared it around at work beca…

    Trackback by Doug's Oracle Blog — December 22, 2009 @ 11:06 pm BST Dec 22,2009 | Reply

  3. Very interesting Jonathan. I ran your query on our 11.1.0.7 db and got back some 11.1.0.8 features !

    Comment by adrian bowden — December 23, 2009 @ 12:41 pm BST Dec 23,2009 | Reply

  4. The parameter “_fix_control” allows you also to enable “bug fixes” that oracle delivers deactivated by default. Some times, after install a patch (patch set) you need to enable the one that “fixes” your issue

    Comment by fidelinho — December 23, 2009 @ 6:40 pm BST Dec 23,2009 | Reply

  5. Fidelinho,
    I guess a good example of that would be the 11.1.0.8 fixes that adrian sees in 11.1.0.7 – could we guess that the bug fix has been registered in the earlier version even though it is not enabled by default until the later version.

    There’s an interesting note on the Pythian blog about v$fix_control – check comment number 3 in particular.

    Comment by Jonathan Lewis — December 24, 2009 @ 7:49 am BST Dec 24,2009 | Reply

  6. There was a Post by Fairlie Rego a while ago that gave examples of enabling/disabling specific bug fixes …

    http://el-caro.blogspot.com/2007/06/fix-control.html

    Andy

    Comment by Andy Campbell — December 27, 2009 @ 7:43 am BST Dec 27,2009 | Reply

  7. [...] 7-How to see optimizer features in your current release (v$system_fix_control)? Jonathan Lewis-Optimizer Features [...]

    Pingback by Blogroll Report – 18/12/2009-25/12/2009 « Coskan’s Approach to Oracle — January 6, 2010 @ 7:45 pm BST Jan 6,2010 | Reply

  8. [...] I read Coskan’s notes I had forgotten that I had written a short item about this myself about a year ago where I listed the  relatively small number of  items [...]

    Pingback by Fix Control « Oracle Scratchpad — January 29, 2011 @ 10:15 am BST Jan 29,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,015 other followers