Oracle Optimizer: Moving to and working with CBO - Part 2 - Page 4

August 26, 2003

7.13) _ORDERED_NESTED_LOOP

This reduces the cost of a nested loop join when the left side of the join is using an index or sort row source. Default value is FALSE in Oracle 8i and TRUE in Oracle 9i.

e.g.: _ordered_nested_loop = true

7.14) _SQLEXEC_PROGRESSION_COST

This controls the population of V$SESSION_LONGOPS view by long running queries. This view is used to monitor the progress of queries that are running for long duration. Queries that cost more than the value that has been set are identified for monitoring. Progression monitoring involves overhead and may affect the performance. The default value is 1000, which may prevent SQL statements from being shared! Setting it to 0 will turn off the monitoring.

e.g.: _sqlexec_progression_cost = 0

7.15) _OPTIMIZER_UNDO_CHANGES

This overrides the default optimizer setting and is not meant for CBO. This parameter used to be enabled to undo query related changes made to the optimizer in one of the earlier releases, even before CBO came up. It may have been used by Oracle to test cases with and without the optimizer changes. It should always be set to false.

e.g.: _optimizer_undo_changes = false

7.16) _NEW_INITIAL_JOIN_ORDERS

This parameter enables join permutation optimization. New ordering directives have been added to CBO for better processing of joins, setting this parameter will allow use of these directives. Default value is FALSE in Oracle 8i and TRUE in Oracle 9i.

e.g.: _new_initial_join_orders = true

7.17) _B_TREE_BITMAP_PLANS

_B_TREE_BITMAP_PLANS enables creation of interim bitmap representation for tables in a query with only binary index(es). Default value is FALSE in Oracle 8i and TRUE in Oracle 9i.

e.g.: _b_tree_bitmap_plans = false

7.18) _OPTIMIZER_MODE_FORCE

This parameter decides the optimizer mode for users recursive SQL, for example, queries running from the PL/SQL block. In CBO, recursive SQL is executed in CHOOSE mode if this parameter is set to FALSE. If this parameter is set to TRUE, then recursive SQL inherits the session's optimizer mode. Hence, if the session is running in FIRST_ROWS, then all SQL processing carried out will be done in the same optimizer mode.

e.g.: _optimizer_mode_force = true

7.19) _UNNEST_SUBQUERY

This enables un-nesting of correlated sub-queries. Such queries may undergo MERGE join operations.

Default value is FALSE in Oracle 8i and TRUE in Oracle 9i.

e.g.: _unnest_subquery = false

7.20) _OPTIM_ENHANCE_NNULL_DETECTION

_OPTIM_ENHANCE_NNULL_DETECTION makes use of index full scans more often. There are some published issues related to this in Oracle 8i.

e.g.: _optim_enhance_nnul_detection = false.

7.21) _QUERY_COST_REWRITE

_QUERY_COST_REWRITE performs cost based rewrite with materialized views. There are some published issues related to this in Oracle 8i.

e.g.: _query_cost_rewrite = false

7.22) _IMPROVED_ROW_LENGTH_ENABLED

Optimizer is enhanced for computing the average row length, this option is enabled by the parameter.

e.g.: _improved_row_length_enabled = true.

7.23)_USE_NOSEGMENT_INDEXES

This option is related to virtual indexes and is used for testing a potential new index prior to actually building it. Setting this parameter enables the optimizer to consider virtual indexes in execution plans.

e.g.: _use_nosegment_indexes = false.

7.24) _SORTMERGE_INEQUALITY_JOIN_OFF

_SORTMERGE_INEQUALITY_JOIN_OFF enables/disables the use of sort merge joins on inequality conditions. Setting the parameter to TRUE will disable inequality Sort merge joins.

e.g.: _sortmerge_inequality_join_off = false

7.25) _NEW_SORT_COST_ESTIMATE

Introduced in Oracle 9i, _NEW_SORT_COST_ESTIMATE enables the use of new cost estimate process for sort.

e.g.: _new_sort_cost_estimate = true

7.26) _OPTIMIZER_DYN_SMP_BLKS

Related to OPTIMIZER_DYNAMIC_SAMPLING, this refers to the number of blocks used for dynamic sampling by the optimizer.

e.g.: _optimizer_dyn_smp_blks = 32

7.27) _INDEX_JOIN_ENABLED

_INDEX_JOIN_ENABLED enables the use of index joins wherever feasible, rather than at table level. Default value is FALSE in Oracle 8i and TRUE in Oracle 9i.

e.g.: _index_join_enabled = false

7.28) _SYSTEM_INDEX_CACHING

Introduced in Oracle 9i, _SYSTEM_INDEX_CACHING refers to the percentage of index caching that the optimizer considers. I find this similar to OPTIMIZER_INDEX_CACHING but it may be behaving differently and have a different meaning internally. Default value is 0.

e.g.: _system_index_caching = 0

7.29) _IMPROVED_OUTERJOIN_CARD

_IMPROVED_OUTERJOIN_CARD enables the use of improved outer-join cardinality calculation.

e.g.: _improved_outerjoin_card = true

7.30) _OPTIMIZER_CHOOSE_PERMUTATION

_OPTIMIZER_CHOOSE_PERMUTATION forces the optimizer to use the specified permutation. Default value is 0.

e.g.: _optimizer_choose_permutation = 0

7.31) _ALWAYS_STAR_TRANSFORMATION

When _ALWAYS_STAR_TRANSFORMATION is enabled, Optimizer will prefer the use of start transformed query. However, what extent this will be done is not documented.

e.g.: _always_star_transformation = false

7.32) _OPTIMIZER_PERCENT_PARALLEL

Please refer section 6.6 (OPTIMIZER_PERCENT_PARALLEL)

7.33) More Internal parameters

The following hidden parameters also influence the optimizer but not enough information is published to understand what functionality they support. I personally have never had the opportunity to try any of these. These are mentioned here for completeness.

_ENABLE_TYPE_DEP_SELECTIVITY

_OPTIMIZER_ADJUST_FOR_NULLS

_SUBQUERY_PRUNING_ENABLED

_SUBQUERY_PRUNING_REDUCTION_FACTOR

_SUBQUERY_PRUNING_COST_FACTOR

_DEFAULT_NON_EQUALITY_SEL_CHECK

_ONESIDE_COLSTAT_FOR_EQUIJOINS

_FAST_FULL_SCAN_ENABLED

_CPU_TO_IO

_PRED_MOVE_AROUND

_QUERY_REWRITE_EXPRESSION

_NESTED_LOOP_FUDGE

_OPTIMIZER_COST_MODEL

_GSETS_ALWAYS_USE_TEMPTABLES

_GS_ANTI_SEMI_JOIN_ALLOWED

Though hidden parameters should be set in consultation with Oracle support, set up the Initialization parameters appropriately as per your setup requirements. Please note that setting this improperly will significantly affect the performance.

» See All Articles by Columnist Amar Kumar Padhi