Skip Headers
Oracle® Database Reference
10g Release 2 (10.2)

Part Number B14237-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

OPTIMIZER_FEATURES_ENABLE

Property Description
Parameter type String
Syntax OPTIMIZER_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.0.0 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.2.0.1}
Default value 10.2.0.1
Modifiable ALTER SESSION, ALTER SYSTEM

OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.

For example, if you upgrade your database from release 8.0.6 to release 9.2, but you want to keep the release 8.0.6 optimizer behavior, you can do so by setting this parameter to 8.0.6. At a later time, you can try the enhancements introduced in releases up to and including release 9.2 by setting the parameter to 9.2.0.

Table 1-3 describes some of the optimizer features that are enabled when you set the OPTIMIZER_FEATURES_ENABLE parameter to an 8.0 release or an 8.1 release. Table 1-4 describes some of the optimizer features that are enabled when you set the OPTIMIZER_FEATURES_ENABLE parameter to a 9.0 release or a 9.2 release.

See Also:

Oracle Database Performance Tuning Guide for more information about the optimizer and for information about the features listed in the following tables

Table 1-3 Release 8.0 and Release 8.1 Optimizer Features

Features Release
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

Index fast full scan

   

X

X

X

X

X

X

X

X

X

X

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

   

X

X

X

X

           

Complex view merging

   

X

X

X

X

           

Push-join predicate

   

X

X

X

X

           

Ordered nested loop costing

   

X

X

X

X

           

Improved outer join cardinality calculation

       

X

X

X

X

X

X

X

X

Improved verification of NULLs inclusion in B-tree indexes

                 

X

X

X

Random distribution method for left of nested loops

                   

X

X

Type-dependent selectivity estimates

                   

X

X

Setting of optimizer mode for user recursive SQL

                   

X

X

Improved average row length calculation

                   

X

X

Partition pruning based on subquery predicates

                   

X

X

Common subexpression elimination

                     

X

Use statistics of a column imbedded in some selected functions such as TO_CHAR to compute selectivity

                     

X

Improved partition statistics aggregation

                     

X


Table 1-4 Release 9.0 and Release 9.2 Optimizer Features

Features Release
9.0.0 9.0.1 9.2.0

Index fast full scan

X

X

X

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

X

X

X

Complex view merging

X

X

X

Push-join predicate

X

X

X

Ordered nested loop costing

X

X

X

Improved outer join cardinality calculation

X

X

X

Improved verification of NULLs inclusion in B-tree indexes

X

X

X

Random distribution method for left of nested loops

X

X

X

Type-dependent selectivity estimates

X

X

X

Setting of optimizer mode for user recursive SQL

X

X

X

Improved average row length calculation

X

X

X

Partition pruning based on subquery predicates

X

X

X

Common subexpression elimination

X

X

X

Use statistics of a column imbedded in some selected functions such as TO_CHAR to compute selectivity

X

X

X

Improved partition statistics aggregation

X

X

X

Peeking at user-defined bind variables

X

X

X

Index joins

X

X

X

Subquery unnesting

X

X

X