Disabled Fixes ... Hidden gems of the RDBMS

When making changes to the RDBMS,the developers of Oracle are always wondering if the new change will introduce a regression.
Every change is checked against a suite of regressions tests to try to trap if the change introduced a regression and fix it before it is released to our clients.
Just in case that change does introduce a regression the developers add parameters and events to disable them. These are the workarounds used when a defect is encounter.
But sometimes the developers are overly cautious and decide that a change may be better to be introduced in a more controlled manner and decide to leave it disabled and only very few people would know how to enable them.
Another reason is that these changes are introduced to help one specific customer and there is no eviedence to support it might help or hurt someone else.

These disabled changes are still tested for regressions anyway.

With the introduction of FIX CONTROL in 10.2.0.2 there is now discover-able information about these disabled fixes.

SQL> desc v$session_fix_control
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SESSION_ID                                         NUMBER
 BUGNO                                              NUMBER
 VALUE                                              NUMBER
 SQL_FEATURE                                        VARCHAR2(64)
 DESCRIPTION                                        VARCHAR2(64)
 OPTIMIZER_FEATURE_ENABLE                           VARCHAR2(25)
 EVENT                                              NUMBER
 IS_DEFAULT                                         NUMBER

SQL> select value,count(*) from v$system_fix_control group by value order by value;
     VALUE   COUNT(*)  -- 11.2.0.3
---------- ----------
         0         47  <-- Disabled
         1        600  <-- Enabled
         3          4  ] 
         7          1  ]__ Configurable
        15          1  ]
        20          1  ]

So in 11.2.0.3 there are 47 fixes that dormant, waiting for an application to take advantage of them.

Lets see an example :

Bug 6708183 - CBO cannot get good cardinality estimate for TABLE() functions [ID 6708183.8]
Introduced in 11.1.0.7 and 11.2.0.1.

This fix allows for a limited version of dynamic sampling to look inside a Collection Table in an effort to get a better cardinality estimation.
By default the CBO makes a guess that the collection has as many rows of 100 bytes that can fit in 100 block and that usually translates to 8168 rows.

There are exception when this fix is enabled where still cardinality is not better estimated and are corrected in posterior fixes or are still to be corrected but all of them them depend on fix 6708183 to be enabled first.

The fix is enabled by "_fix_control" = '6708183:ON' or by adding a DYNAMIC_SAMPLING hint in the query.

If you wish to know more about one particular disabled (or enabled) fix, try its bugtag first.
if it does not have a bugtag let me know and I'll see if one can be made.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

I am Abel Macias email, an Oracle support engineer that specialized in Performance that belongs to Exadata Support.
Disclaimer This blog looks to broadcast my experiences while diagnosing performance problems on Oracle's RDBMS, mostly on Exadata.
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today