René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Optimizer related initialization parameters

OPTIMIZER_DYNAMIC_SAMPLING

This parameter can be set to a value between 0 and 10, the default being 2. The higher the value, the more resources (I/O) are spent on sampling. If set to 0, no sampling is done at all.

OPTIMIZER_FEATURES_ENABLE

OPTIMIZER_INDEX_CACHING

Affects the CBO

OPTIMIZER_INDEX_COST_ADJ

Affects the CBO and is one of the most important parameters to tune the CBO.

OPTIMIZER_MAX_PERMUTATIONS

See CBO.

OPTIMIZER_MODE

  • first_rows
  • first_rows_1
  • first_rows_10
  • first_rows_100
  • first_rows_1000
  • all_rows
  • choose
  • rule
Depending on the setting of this parameter, Oracle will choose CBO or RBO. (Note, there will be no RBO anymore with Oracle 10g).

OPTIMIZER_PERCENT_PARALLEL

This parameter became obsolete in 9i.

OPTIMIZER_SEARCH_LIMIT

Affects CBO.

OPTIMIZER_SECURE_VIEW_MERGING