Oracle Optimizer: Moving to and working with CBO - Part 2 - Page 3
August 26, 2003
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 18.104.22.168 with recommendation and consultation from support. Most of these are set to the default value.
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
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)
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
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
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
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
_NO_OR_EXPANSION disable the OR operator expansion by the optimizer.
e.g.: _no_or_expansion = false
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
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
_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