Parameters that enable and control Query Optimizer Features

You know that Query optimizer is responsible to determine the best execution/explain plan. In many cases you may be astonished that the data is same in both database and you have gather statistics but both database give different execution plan. The possible reason for it it the variation of initialization parameter between two database. Now in the later section in this post we will have a look at the initialization parameter that affect the optimizer to determine execution plan.

A)Enable Query Optimizer Feature
--------------------------------------------
OPTIMIZER_FEATURES_ENABLE Parameter
------------------------------------------------
•It is a string type parameter and takes oracle version number as argument.
•Based on this parameter settings it is determined how oracle optimizer behaves.
•Every new release of oracle version comes with new feature for optimizer. Thus new version of optimizer can collect extra features of a query based on which execution plan can changes. If you upgrade your oracle to newer version and your don't want to change your execution plan according to new one (keep like older) then you can set this parameter to older one.
•The valid values of this parameter can be,
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|10.2.0.2|10.2.0.3| etc.


B)Control the Behavior of the Query Optimizer
---------------------------------------------------------------------------
Here is the list of initialization parameters that can be used to control the behavior of the query optimizer.

1)CURSOR_SHARING
2)DB_FILE_MULTIBLOCK_READ_COUNT
3)OPTIMIZER_INDEX_CACHING
4)OPTIMIZER_INDEX_COST_ADJ
5)OPTIMIZER_MODE
6)PGA_AGGREGATE_TARGET
7)STAR_TRANSFORMATION_ENABLED

All of these parameters are discussed in topic Parameters that control the behavior of Query Optimizer.