Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Aug 26, 2003

Oracle Optimizer: Moving to and working with CBO - Part 2 - Page 3

By Amar Kumar Padhi

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM