7. Internal Oracle parameters that affect the Optimizer
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.
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 18.104.22.168 with recommendation
and consultation from support. Most of these are set to the default value.
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.
_sort_elimination_cost_ratio = 5
Please refer section 6.18 (ALWAYS_SEMI_JOIN).
Please refer section 6.17 (ALWAYS_ANTI_JOIN).
Please refer section 6.13 (HASH_MULTIBLOCK_IO_COUNT)
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.
_complex_view_merging = true
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
_push_join_predicate = true
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.
_push_join_union_view = true
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.
_or_expand_nvl_predicate = true
disable the OR operator expansion by
_no_or_expansion = false
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".
_like_with_bind_as_equality = true
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.
_table_scan_cost_plus_one = true
_USE_COLUMN_STATS_FOR_FUNCTION Allows the use of column statistics for columns that are involved in non-operative
expressions in query, such as:
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.
_use_column_stats_for_function = true