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 Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

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

Featured Database Articles


Posted Aug 26, 2003

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

By Amar Kumar Padhi


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


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


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


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


_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


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


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


_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.


_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


Optimizer is enhanced for computing the average row length, this option is enabled by the parameter.

e.g.: _improved_row_length_enabled = true.


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.


_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


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


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


_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


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


_IMPROVED_OUTERJOIN_CARD enables the use of improved outer-join cardinality calculation.

e.g.: _improved_outerjoin_card = true


_OPTIMIZER_CHOOSE_PERMUTATION forces the optimizer to use the specified permutation. Default value is 0.

e.g.: _optimizer_choose_permutation = 0


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


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.
















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

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