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 18.104.22.168 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 22.214.171.124, 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.)