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
» 
Comment and Contribute
