The Oracle SQL optimizer has always been
problematic (especially with histogram evaluation), and Oracle
has introduced
v$system_fix_control and v$session_fix_control
starting in 10gr2 to
control whether CBO bug patches are enabled or disabled.
Also see my notes on how to
display optimizer
features by release using v$system_fix_control.
Oracle 11g's data dictionary
defines the v$system_fix_control view using the
following source query:
select BUGNO, VALUE, SQL_FEATURE, DESCRIPTION,
OPTIMIZER_FEATURE_ENABLE, EVENT, IS_DEFAULT from
GV$SYSTEM_FIX_CONTROL where inst_id=USERENV('Instance')
For internal testing ONLY, we have the v$session_fix_control
view that allows you to enable and disable specific optimizer
features.
This SQL optimization fix control feature is enabled via a
hidden parameter called _fix_control.
The _fix_control parameter should ONLY be
adjusted with the knowledge and consent of Oracle technical
support.
alter session set “_fix_control”=’5483301:off’;
This article notes the SQL optimizer bugs and the impact of
using _fix_control:
"If a column has a frequency histogram and a
query uses an equality predicate over the column looking for a
value that does not exists in the histogram then the CBO was
estimating a cardinality of 1. This could favor Nested
Loops too much.
The fix introduces a notable change in
behavior: instead of estimating a cardinality of 1 then with
this fix CBO estimates a cardinality of (0.5 * the lowest
cardinality found in the histogram). This can result in plan
changes."
Notes on v$system _fix_control are on MOSC.
- MOSC Doc ID 5483301.8
- Bug 6082745 and bug 5483301: Cardinality of 1 when
predicate value non-existent in frequency histogram
