| Oracle® Database Reference 10g Release 2 (10.2) Part Number B14237-01 |
|
|
View PDF |
| Oracle® Database Reference 10g Release 2 (10.2) Part Number B14237-01 |
|
|
View PDF |
| 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 } |
| Default value | 10.1.0 |
| 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 |
