if you upgrade your database from release 10.1 to release 11.1, but you want to keep the release 10.2.0.4 optimizer behavior, you can do so by setting this parameter to 10.2.0.4. At a later time, you can try the enhancements introduced in releases up to and including release 11.1 by setting the parameter to 11.1.0.6.
ALTERSYSTEM SET optimizer_features_enable = \'10.2.0.4\';
PropertyDescriptionParameter typeString
SyntaxOPTIMIZER_FEATURES_ENABLE = { 8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.1.0.5 | 10.1.0.6 | 10.2.0.1 | 10.2.0.2 | 10.2.0.3 | 10.2.0.4 | 10.2.0.5 | 11.1.0.6 | 11.1.0.7 }
Default value11.1.0.7
ModifiableALTER SESSION, ALTER SYSTEM
Table 1-3 Optimizer Features for Oracle9I ReleasesFeatures9.0.09.0.19.2.0Index fast full scanXXX
Consideration of bitmap access to paths for tables with only B-tree indexesXXX
Complex view mergingXXX
Peeking into user-defined bind variablesXXX
Index joinsXXX
Table 1-4 Optimizer Features for Oracle Database 10g ReleasesFeatures10.1.010.1.0.310.1.0.410.1.0.510.2.0.110.2.0.2Index fast full scanXXXXXX
Consideration of bitmap access to paths for tables with only B-tree indexesXXXXXX
Complex view mergingXXXXXX
Peeking into user-defined bind variablesXXXXXX
Index joinsXXXXXX
Dynamic samplingXXXXXX
Query rewrite enablesXXXXXX
Skip unusable indexesXXXXXX
Automatically compute index statistics as part of creationXXXXXX
Cost-based query transformationsXXXXXX
Allow rewrites with multiple MVs and/or base tablesXX
Table 1-5 Optimizer Features for Oracle Database 11g ReleasesFeatures11.1.0.6Enhanced Bind PeekingX
Use extended statistics to estimate selectivityX
Use native implementation for full outer joinsX
Partition pruning using join filteringX
Group by placement optimizationX
Null aware antijoinsX