Oracle Scratchpad

December 22, 2009

Optimizer Features

Filed under: CBO,Troubleshooting — Jonathan Lewis @ 6:53 pm GMT 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, 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, that you could use:

set pagesize 60
set linesize 90
set trimspool on
set tab off

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

--      sql_feature,
order by

spool off

-------- ----------------------------------------------------------------
9.2.0    add new predicate to the VC-EXPR mapping  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 Use index heuristic for join pred being pushed 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 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 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, 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.


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


  1. Jonathan,

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

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


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


    Comment by Jimmy Brock — December 22, 2009 @ 7:55 pm GMT 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 GMT Dec 22,2009 | Reply

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

    Comment by adrian bowden — December 23, 2009 @ 12:41 pm GMT 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 GMT Dec 23,2009 | Reply

  5. Fidelinho,
    I guess a good example of that would be the fixes that adrian sees in – 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 GMT Dec 24,2009 | Reply

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


    Comment by Andy Campbell — December 27, 2009 @ 7:43 am GMT 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 GMT 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 GMT Jan 29,2011 | Reply

  9. […] my preference would (at least in the short term) be the _fix_control one. Specifically, from the v$system_fix_control view, we can see that it addresses the problem very precisely with the description: “index […]

    Pingback by min/max Upgrade | Oracle Scratchpad — March 23, 2017 @ 8:53 am GMT Mar 23,2017 | Reply

  10. […] to Mauro Pagano – identifying this as fix 13345888 (check v$system_fix control) introduced in […]

    Pingback by Uniquely parallel | Oracle Scratchpad — May 21, 2017 @ 6:46 am BST May 21,2017 | Reply

  11. […] iteration and index range scans and cardinality calculations – here’s a quick sample of v$system_fix_control in […]

    Pingback by num_index_keys | Oracle Scratchpad — November 15, 2018 @ 1:13 pm GMT Nov 15,2018 | Reply

  12. […] on, and that it wasn’t available in There are two possibilities – a fix control, and an […]

    Pingback by Index FFS Cost | Oracle Scratchpad — October 1, 2020 @ 11:45 am BST Oct 1,2020 | Reply

  13. […] by default, and it’s not something you can tweak into a query with the opt_param() hint. Fix control 509019 has the description: “set leaf blocks to the number of blocks in the index extent […]

    Pingback by Index FFS Cost 2 | Oracle Scratchpad — October 6, 2020 @ 1:53 pm BST Oct 6,2020 | Reply

  14. […] looks like fix control 24761824 “add is not null for high null column in set function” introduced in 19.1.0. […]

    Pingback by 19c tweak 2 | Oracle Scratchpad — July 9, 2021 @ 5:38 pm BST Jul 9,2021 | Reply

  15. […] looks as if Oracle may be implementing a new strategy to replace the prevalence of fix_control settings to allow for improved visibility of the state of point patches. Possibly the aim is to […]

    Pingback by 19c Trivia | Oracle Scratchpad — January 12, 2022 @ 11:06 am GMT Jan 12,2022 | Reply

  16. […] updates of 19c (the first one appeared in, possibly taking over as a substitute for the fix_control mechanism (though there are still 1,556 fix controls in […]

    Pingback by 12c trivia | Oracle Scratchpad — January 12, 2022 @ 11:10 am GMT Jan 12,2022 | Reply

  17. […] how much CPU time it used.  The default for “too long” can be adjusted by setting a “fix control”.  This makes it a lot easier (see below) to find out where the time went if you see a very long […]

    Pingback by Parse Time | Oracle Scratchpad — January 13, 2022 @ 10:49 am GMT Jan 13,2022 | Reply

  18. […] There is some scope (with the usual “confirm with Oracle support” caveat) for modifying this behaviour with a fix_control: […]

    Pingback by Hash Aggregation – 2 | Oracle Scratchpad — January 19, 2022 @ 12:05 pm GMT Jan 19,2022 | Reply

  19. […] to (and but in those versions it requires you to set a value for the hidden _fix_control parameter (which you can do at the session or system level) to enable the feature. There are three […]

    Pingback by Index Upgrades | Oracle Scratchpad — February 25, 2022 @ 9:22 am GMT Feb 25,2022 | Reply

  20. […] wishes come true and in 19c – with fix_control QKSFM_DBMS_STATS_27268249 – one of mine did. The description of this fix (which is enabled by […]

    Pingback by Index Upgrade | Oracle Scratchpad — April 11, 2022 @ 4:55 pm BST Apr 11,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: