Skip Headers

Oracle9i Database Reference
Release 2 (9.2)

Part Number A96536-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
Feedback

Go to previous page Go to next page
View PDF

OPTIMIZER_FEATURES_ENABLE

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}

Default value

9.2.0

Parameter class

Static

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-2 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-3 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:

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

Table 1-2  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-3  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