optimizer_features_enable

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 typeStringSyntaxOPTIMIZER_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.7ModifiableALTER SESSION, ALTER SYSTEM

Table 1-3 Optimizer Features for Oracle9I ReleasesFeatures9.0.09.0.19.2.0

Index fast full scan

X

X

X

Consideration of bitmap access to paths for tables with only B-tree indexes

X

X

X

Complex view merging

X

X

X

Peeking into user-defined bind variables

X

X

X

Index joins

X

X

X

Table 1-4 Optimizer Features for Oracle Database 10g Releases

Features10.1.010.1.0.310.1.0.410.1.0.510.2.0.110.2.0.2

Index fast full scan

X

X

X

X

X

X

Consideration of bitmap access to paths for tables with only B-tree indexes

X

X

X

X

X

X

Complex view merging

X

X

X

X

X

X

Peeking into user-defined bind variables

X

X

X

X

X

X

Index joins

X

X

X

X

X

X

Dynamic sampling

X

X

X

X

X

X

Query rewrite enables

X

X

X

X

X

X

Skip unusable indexes

X

X

X

X

X

X

Automatically compute index statistics as part of creation

X

X

X

X

X

X

Cost-based query transformations

X

X

X

X

X

X

Allow rewrites with multiple MVs and/or base tables

    

X

X

Table 1-5 Optimizer Features for Oracle Database 11g Releases

Features11.1.0.6

Enhanced Bind Peeking

X

Use extended statistics to estimate selectivity

X

Use native implementation for full outer joins

X

Partition pruning using join filtering

X

Group by placement optimization

X

Null aware antijoins

X