Oracle Optimizer

Oracle optimizer version:

select * from v$version;
show parameter optimizer

OPTIMIZER_FEATURES_ENABLE
Parameter type:  String
 
Parameter class:  Static
 
Default value:  8.1.6
 
Range of values:  8.0.0, 8.0.3, 8.0.4, 8.0.5, 8.0.6, 8.1.0, 8.1.3, 8.1.4, 8.1.5, 8.1.6, 8.1.7
 
OPTIMIZER_FEATURES_ENABLE lets you change the behavior. of the Oracle optimizer based on a release number. For example, if you migrate your database from 8.0.5 to 8.1.5, but you want to keep the 8.0.5 optimizer behavior, you can do so by setting this parameter to 8.0.5. At another time, you can try the new enhancements introduced up to release 8.1.5 by setting the parameter to 8.1.5.
 

 The oracle optimizer
  The optimizer is influenced by its decision by the following factors:
OPTIMIZER_MODE in the initialization file
statistics in the data dictionary
OPTIMIZER_GOAL parameter of the ALTER SESSION statement
CHOOSE
ALL_ROWS
FIRST_ROWS
RULE
hints 

select value from v$parameter where name='optimizer_mode'

alter session set optimizer_mode='rule';