7. Internal Oracle parameters that affect the Optimizer
Internal
parameters are supposed to be undocumented and are set by Oracle as per the
requirements. These are also called hidden parameters and start with an
underscore character. Knowledge of these parameters is an added advantage in
debugging application issues and errors. One should not change these unless
recommended by Oracle support. Oracle does not provide any official
documentation on these.
I
have shared details to the extent of my knowledge regarding these. Please note
that some of these are difficult to understand and how they work is not easy to
document. The parameter values that I have mentioned below in examples are
settings that I have used for OLTP setups on Oracle 8.1.7.4 with recommendation
and consultation from support. Most of these are set to the default value.
7.1) _SORT_ELIMINATION_COST_RATIO
When
using an index access plan for a query that has an ORDER BY clause, the final
sorting can be avoided. For example, if the value were set to 5, it would mean
that a plan that avoids a sort may not be 5 times more expensive than a plan
that does not avoid it. Hence, the optimizer will then compare the cost of all
queries accordingly and pick the low cost execution plan. A value of 0 would
mean that an execution plan with ORDER BY sort elimination be chosen even if it
is more expensive than queries that do a final sorting.
e.g.:
_sort_elimination_cost_ratio = 5
7.2) _ALWAYS_SEMI_JOIN
Please refer section 6.18 (ALWAYS_SEMI_JOIN).
7.3) _ALWAYS_ANTI_JOIN
Please refer section 6.17 (ALWAYS_ANTI_JOIN).
7.4) _HASH_MULTIBLOCK_IO_COUNT
Please refer section 6.13 (HASH_MULTIBLOCK_IO_COUNT)
7.5) _COMPLEX_VIEW_MERGING
This
parameter is related to improving the SQL performance on complex views
(including inline views). Oracle tries to merge the query criteria with the
existing view criteria that would result in a faster single query. For example,
if a view is created with a GROUP BY clause in it and a query is executed on
the view having a where clause, Oracle tries to merge the two and create a
single query that would run the where clause prior to grouping it, thus giving
better performance. Default value is FALSE in Oracle 8i and TRUE in Oracle 9i.
e.g.:
_complex_view_merging = true
7.6) _PUSH_JOIN_PREDICATE
This
enables the push join predicate feature that allows the optimizer to push join
predicates inside a non-mergable view(s). This would achieve something similar
to a complex view merging feature, but in this case the join conditions
provided in the query are pushed into the view. The view in this case cannot be
merged with the query. Default value is FALSE in Oracle 8i and TRUE in Oracle
9i.
e.g.:
_push_join_predicate = true
7.7) _PUSH_JOIN_UNION_VIEW
Same
as above, but this parameter allows optimizer to push join predicates inside
non-mergable views that contain UNION ALL set operators. Default value is FALSE
in Oracle 8i and TRUE in Oracle 9i.
e.g.:
_push_join_union_view = true
7.8) _OR_EXPAND_NVL_PREDICATE
This
feature expands the NVL function predicates to evaluate the use of an index
that may be present on the column used in the function. For example, if the expression
is of the type "column1 = nvl(:b1, column1)" and column1 has an index
on it, then optimizer may transform it to a new expression that uses the OR
operator. This new expression will again be further transformed to make use of the
UNION operator. Default value is FALSE in Oracle 8i and TRUE in Oracle 9i.
e.g.:
_or_expand_nvl_predicate = true
7.9) _NO_OR_EXPANSION
_NO_OR_EXPANSION
disable the OR operator expansion by
the optimizer.
e.g.:
_no_or_expansion = false
7.10) _LIKE_WITH_BIND_AS_EQUALITY
This
option allows optimizer to treat the LIKE predicate with bind variable as an
equal-to predicate, for costing purposes. This happens for expressions with the
index column being compared to a bind variable with the LIKE operator. Hence,
expressions such as "column1 like :b1" would be treated as
"column1 = :b1".
e.g.:
_like_with_bind_as_equality = true
7.11) _TABLE_SCAN_COST_PLUS_ONE
This
parameter increases the cost of a full table scan by one, in order to eliminate
ties between a full table scan on a small lookup table and unique or range scan
on the lookup table. Default value is FALSE in Oracle 8i and TRUE in Oracle 9i.
e.g.:
_table_scan_cost_plus_one = true
7.12) _USE_COLUMN_STATS_FOR_FUNCTION
_USE_COLUMN_STATS_FOR_FUNCTION Allows the use of column statistics for columns that are involved in non-operative
expressions in query, such as:
numcol
+ 0
charcol
|| ''
Such
expressions were mainly used in RBO to prevent the use of indexes. The default
value is FALSE in Oracle 8i and TRUE in Oracle 9i.
e.g.:
_use_column_stats_for_function = true
» 
Comment and Contribute
