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

Tuesday Aug 26th 2003 by Amar Kumar Padhi
Share:

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

6.1) OPTIMIZER_MODE

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

6.2) OPTIMIZER_FEATURES_ENABLE

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

6.3) OPTIMIZER_MAX_PERMUTATIONS

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.

6.4) OPTIMIZER_INDEX_COST_ADJ

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

6.5) OPTIMIZER_INDEX_CACHING

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

6.6) OPTIMIZER_PERCENT_PARALLEL

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

6.7) COMPATIBLE

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

6.8) DB_FILE_MULTIBLOCK_READ_COUNT

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)

6.9) SORT_AREA_SIZE

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

6.10) SORT_MULTIBLOCK_READ_COUNT

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

6.11) HASH_JOIN_ENABLED

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

6.12) HASH_AREA_SIZE

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.

6.13) HASH_MULTIBLOCK_IO_COUNT

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

6.14) BITMAP_MERGE_AREA_SIZE

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

6.15) QUERY_REWRITE_ENABLED

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

6.16) QUERY_REWRITE_INTEGRITY

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

6.17) ALWAYS_ANTI_JOIN

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

6.18) ALWAYS_SEMI_JOIN

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

6.19) STAR_TRANSFORMATION_ENABLED

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 8.1.7.4 and above.

e.g.: star_transformation_enabled = false

6.20) PARALLEL_BROADCAST_ENABLED

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

6.21) OPTIMIZER_DYNAMIC_SAMPLING

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

6.22) PARTITION_VIEW_ENABLED

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

6.23) CURSOR_SHARING

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

6.24) PGA_AGGREGATE_TARGET

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



7.13) _ORDERED_NESTED_LOOP



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



7.14) _SQLEXEC_PROGRESSION_COST

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

7.15) _OPTIMIZER_UNDO_CHANGES

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

7.16) _NEW_INITIAL_JOIN_ORDERS

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

7.17) _B_TREE_BITMAP_PLANS

_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

7.18) _OPTIMIZER_MODE_FORCE

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

7.19) _UNNEST_SUBQUERY

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

7.20) _OPTIM_ENHANCE_NNULL_DETECTION

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

7.21) _QUERY_COST_REWRITE

_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

7.22) _IMPROVED_ROW_LENGTH_ENABLED

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

e.g.: _improved_row_length_enabled = true.

7.23)_USE_NOSEGMENT_INDEXES

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.

7.24) _SORTMERGE_INEQUALITY_JOIN_OFF

_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

7.25) _NEW_SORT_COST_ESTIMATE

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

7.26) _OPTIMIZER_DYN_SMP_BLKS

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

7.27) _INDEX_JOIN_ENABLED

_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

7.28) _SYSTEM_INDEX_CACHING

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

7.29) _IMPROVED_OUTERJOIN_CARD

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

e.g.: _improved_outerjoin_card = true

7.30) _OPTIMIZER_CHOOSE_PERMUTATION

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

e.g.: _optimizer_choose_permutation = 0

7.31) _ALWAYS_STAR_TRANSFORMATION

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

7.32) _OPTIMIZER_PERCENT_PARALLEL

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.

_ENABLE_TYPE_DEP_SELECTIVITY

_OPTIMIZER_ADJUST_FOR_NULLS

_SUBQUERY_PRUNING_ENABLED

_SUBQUERY_PRUNING_REDUCTION_FACTOR

_SUBQUERY_PRUNING_COST_FACTOR

_DEFAULT_NON_EQUALITY_SEL_CHECK

_ONESIDE_COLSTAT_FOR_EQUIJOINS

_FAST_FULL_SCAN_ENABLED

_CPU_TO_IO

_PRED_MOVE_AROUND

_QUERY_REWRITE_EXPRESSION

_NESTED_LOOP_FUDGE

_OPTIMIZER_COST_MODEL

_GSETS_ALWAYS_USE_TEMPTABLES

_GS_ANTI_SEMI_JOIN_ALLOWED

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

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