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

Tuesday Aug 26th 2003 by Amar Kumar Padhi

In this months installment of 'Oracle Optimizer and how to move to CBO,' Amar Padhi covers the Initialization parameters and Hidden or Internal Oracle parameters that influence the Optimizer in choosing execution plans.

Part 2 of this series covers the Initialization parameters and Hidden or Internal Oracle parameters that influence the Optimizer in choosing execution plans. It is very important to setup these parameters properly.

6. Initialization parameters that affect the Optimizer

Apart from generating statistics, the setup of the below mentioned parameters play a very crucial role in dictating how your system will work. Setting these will depend a lot on what kind of environment you want to create, Online, Batch processing, Data warehousing or a combination of more than one. Please note that the optimizer considers these parameters to evaluate every execution plan it generates in CBO.

The parameter values that I have mentioned below in examples are settings that I have used in some OLTP setups on Oracle with good results. Oracle gives you the liberty of deciding what kind of a setup you want to maintain, so do not just keep the default values and make sure that these parameters are set as per your requirements.


This will decide the mode in which the optimizer engine should run in. Valid values are RULE, CHOOSE, ALL_ROWS, FIRST_ROWS (_n). CBO options have been explained in Part-1 (3.2.2 - Available CBO Modes)

Optionally, the OPTIMIZER_MODE can be set to CHOOSE. This is kind of an intermediate option between RBO and CBO. In fact, it tries to run the query in either CBO or RBO depending on the availability or unavailability of statistics. Therefore, if the tables present in the query have statistics generated on them, CBO (ALL_ROWS only) is preferred or RBO is taken up.

e.g.: optimizer_mode = first_rows


This is set to a version number such as- 8.1.5, 8.1.7, 9.0.0. Since new features and functionality are being added to CBO in every release, its behavior may change and result in different execution plans. You can set this to a version number for which your application is tuned. Please note setting it to a lower version will prevent the use of new features that have come in later versions.

e.g.: optimizer_features_enable = 8.1.7


This parameter specifies the maximum number of permutations that should be considered for queries with joins, to choose an execution plan. This will influence the parse time of queries. This parameter should be set to a lower value. Make sure the other parameters mentioned in this section are set properly so that the optimizer finds an optimal execution plan within the specified limits. It defaults to 80000 in Oracle 8, which means no limits! In Oracle 9i it is defaulted to 2000.

e.g.: optimizer_max_permutations = 2000

Another parameter, OPTIMIZER_SEARCH_LIMIT overrides the effect of this parameter. OPTIMIZER_SEARCH_LIMIT specifies the maximum tables in a query that would be considered for join orders with Cartesian; it is obsolete in 8.1.6.


Optimizer_Index_Cost_Ad takes a value between 1 and 10000 (default 100). Setting this value makes the index more or less friendly to the optimizer. For example setting it to 50 tells the optimizer that an execution plan that uses index(es) is half as expensive as other execution plans that are not using them.

The lower the value (less than 100), the less full table scan executions will take place in the system.

Use of indexes in OLTP is a crucial factor for deciding the response time, but sometimes, full table scans provide better throughput. Nested loop joins are influenced by this parameter value. Set this parameter to a value that is optimal for your application.

e.g.: optimizer_index_cost_adj = 10


This tells optimizer to favor nested loops and IN-list iterators over sort-merge and hash joins. The default value is 100 and makes nested loops and IN-list operator look less expensive and more appealing to Optimizer. The value basically indicates the percentage of index blocks that the optimizer should assume are present in cache.

e.g.: optimizer_index_caching = 100


OPTIMIZER_PERCENT_PARALLEL takes a value between 0 and 100. A low value favors indexes and a higher value will favor full table scans. The optimizer uses this parameter in working out the cost of a full table scan. A value of 100 makes use of degree of parallelism set at object level. I prefer setting it to zero to favor use of indexes and prevent use of parallel query in computing the costing.

It is renamed to _OPTIMIZER_PERCENT_PARALLEL in Oracle 9i and its value
          should not be altered unless recommended by Oracle support.

e.g.: optimizer_percent_parallel = 0


This parameter is used to provide backward compatibility with earlier releases. This may also restrict the use of some new features. CBO has undergone lot of changes in release 8. It is advisable to set this parameter to 8.1.0 or higher. Only three digits are required to be specified, however, you can specify more for record purposes.

e.g.: compatible = 8.1.7


This parameter determines the number of database blocks read in one input/output operation during a full table scan. The value set is used in computing the cost of full table scans. OLTP systems will not benefit by setting a high value for this parameter as the computed cost for full table scan execution plans would reduced. The maximum size is Operating system dependent.

e.g.: db_file_multiblock_read_count = 1 (may be set to a higher value)


This parameter defines the maximum memory space allocated per user process that requires sorting of data and for insert and updates to bitmap indexes. Optimizer uses the set value for determining the cost of sorting in a query. This defaults to 64K. Normally a size of 64K to 1M is appropriate for OLTP systems.

Sort-merge joins are influenced by this parameter value. The bigger the size the more appealing will be sort-merge joins over nested-loop and hash joins. The lower the size the more sorting will take place in temporary segments. So assign a value that has been evaluated for your setup.

Oracle recommends the use of PGA_AGGREGATE_TARGET instead of this parameter from Oracle 9i.

e.g: sort_area_size = 1048576


This specifies the number of database blocks to be read each time a sort performs a read from a temporary segment. The default value is 2. General recommendation is to keep the default value. However, if you intend to hike up the SORT_AREA_SIZE considerably, you may evaluate the improvement by changing this parameter.

e.g.: sort_multiblock_read_count = 2


Hash joins are available only in CBO. Valid values are

In hash joins, a hash table is created on the join key of the smallest sized table. It then joins the other tables to find the match. Hash joins may prove to be faster than other type of joins in some conditions, especially when the index is missing or search criteria is not very selective. Hash joins require a large amount of memory as the hash tables are retained; this may sometimes result in memory swapping.

Nested-loop joins return the first row faster than sort-merge and hash joins and are preferred for OLTP, but other types of joins cannot be ignored for running other aspects of the applications.

e.g.: hash_join_enabled = true


This specifies the maximum amount of memory in bytes to be used for a hash join per process. It is defaulted to 2 times SORT_AREA_SIZE.

Oracle recommends the use of PGA_AGGREGATE_TARGET instead of this parameter from Oracle 9i.

e.g.: hash_area_size = 2097152

Setting this to a very low number may sometimes result in the following error.

ORA-6580: Hash Join ran out of memory while keeping large rows in memory.


This specifies how many sequential blocks a hash join reads and writes in one Input/Output activity. Oracle recommends not changing or assigning a value to this parameter; this will let oracle decide on the appropriate value for each individual query. In such casees, the value of the parameter will appear as 0 in the V$PARAMETER view.

This parameter is renamed to _HASH_MULTIBLOCK_IO_COUNT as of Oracle 9i.

e.g.: hash_multi_block_io_count = 0

Some Oracle 8i setups have reported the below error for Locally Managed temporary tablespaces. This occurs when Oracle tries to allocate a larger number of database blocks than are available in the largest extent (that are all of uniform size). If you have this issue come up, set the value of this parameter to greater than 0 (preferably, 1 or 2) and test it out in your environment.

ORA-3232: unable to allocate an extent of %s blocks from tablespaces %s


This parameter is relevant for systems using bitmap indexes. It specifies the amount of memory Oracle uses to merge bitmaps retrieved from a range scan of a bitmap index. The default value is 1 MB, which is considered sufficient for most setups.

Oracle recommends use of PGA_AGGREGATE_TARGET instead of this parameter from Oracle 9i.

e.g.: bitmap_merge_area_size = 1048576


This parameter is relevant for systems using Materialized views, Function based indexes and stored outlines. Setting this parameter enables query rewrite for the database. The materialized views should also be query rewrite enabled to allow the optimizer to redirect a query to it rather than the actual tables listed in the FROM clause. Query rewriting is internally done by the optimizer based on what results are expected and whether these are already present in materialized view form.

e.g.: query_rewrite_enabled = true


This parameter determines the extent to which the optimizer must enforce query rewriting to Materialized views. This determines the accuracy of the query rewrite. It can be set to ENFORCED, TRUSTED or STALE_TOLERATED. ENFORCED option uses Materialized views if they contain fresh data, thus guarantying the integrity and consistency of data. TRUSTED uses relationships that have been defined in the system for rewriting the query. STALE_TOLERATED allows the use of relationships that are not enforced and may use stale data. For OLTP systems, ENFORCED would be the ideal setup, as reports need to be up-to-date.

e.g.: query_rewrite_integrity = enforced


This parameter specifies the join method for anti-joins, for example when a NOT IN operator is present in your query. It can be set to NESTED_LOOPS, MERGE or HASH. It is defaulted to NESTED_LOOPS in Oracle 8i and CHOOSE in Oracle 9i.

This parameter is renamed to _ALWAYS_ANTI_JOIN as of Oracle 9i.

e.g.: always_anti_join = nested_loops


This parameter specifies the join method for semi-joins. These types of joins are carried out by Optimizer after transforming a query. In such joins, duplicate values from the inner table are removed and then the type of join specified in the parameter is used to perform a semi-join. It can be set to NESTED_LOOPS, MERGE or HASH. In Oracle 8i, it is defaulted to STANDARD and in Oracle 9i it is defaulted to CHOOSE, to pick up an appropriate join.

This parameter is renamed to _ALWAYS_SEMI_JOIN as of Oracle 9i.

e.g.: always_semi_join = nested_loops


This specifies whether query transformation will be applied to star queries. It can be set to TRUE, FALSE or TEMP_DISABLE (transformation will take place but will not use temporary tables). I presently set it to FALSE due to some known issues of sub-optimal queries being generated. If you intend to use this, please upgrade your version to and above.

e.g.: star_transformation_enabled = false


This parameter refers to parallel executions in cluster databases. It is meant for improving hash and sort-merge join operations where a very large result set is joined with a very small result set. When this option is enabled, the optimizer broadcasts a copy of all rows in the smaller result set to all cluster databases that are processing some rows of the larger result set. I know this parameter in theory only, never got a chance to work on it.

It is obsolete in release 9.2.0.

e.g.: parallel_broadcast_enabled = false


This parameter is introduced in release 9i. It is meant for situations where tables are not analyzed. As CBO depends heavily on statistics, the parameter tells the optimizer to sample the unanalyzed tables that are being used in a query. A level of 0 to 10 can be specified, the higher the value the more time optimizer spends in sampling.

e.g.: optimizer_dynamic_sampling = 1


This parameter is meant for backward compatibility to support partition views. Oracle recommends use of partition tables rather than partition views. If you are migrating to CBO, chances are that you may not be using partition views.

e.g.: partition_view_enabled = false


This parameter determines what kind of SQL statements can share the same cursor. It can be set to FORCE, SIMILAR or EXACT. FORCE will try to squeeze statements that may differ in some literals to share the same cursor. SIMILAR is somewhat the same but will try to maintain the plan optimization for identical statements. EXACT allows statements with exact identical text to share a cursor.

Using FORCE may sometimes result in unexpected results.

e.g.: cursor_sharing = exact


Introduced in Oracle 9i, this parameter specifies the aggregate PGA memory available to all server processes attached to an instance. This parameter can be set for automatic sizing of SQL working areas. It replaces other existing parameters like SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and HASH_AREA_SIZE.

It can be set to a value between 10 MB to 4000 GB, depending on the setup requirement.

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


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

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved