Oracle Optimizer: Moving to and working with CBO - Part 2 - Page 4
August 26, 2003
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
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
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
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
_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
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
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
_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.
_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
Optimizer is enhanced for computing the average row length, this option is enabled by the parameter.
e.g.: _improved_row_length_enabled = true.
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.
_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
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
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
_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
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
_IMPROVED_OUTERJOIN_CARD enables the use of improved outer-join cardinality calculation.
e.g.: _improved_outerjoin_card = true
_OPTIMIZER_CHOOSE_PERMUTATION forces the optimizer to use the specified permutation. Default value is 0.
e.g.: _optimizer_choose_permutation = 0
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
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.
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.