A m Z o n e

Oracle Optimizer: Moving to and working with CBO
Part 2

This part 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). In CBO options have been explained in detail above.

Optionally, the OPTIMIZER_MODE could be set to CHOOSE. This is kind of an intermediate option between RBO and CBO. Infact it tries to run the query in either CBO or RBO depending on the availability or unavailability of statistics. So 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 is 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 that your application is tuned for. Please note setting it to a lower version will prevent 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
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 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 lesser 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 thoughput. 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 favour 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
Takes a value between 0 and 100. A low value favours indexes and a higher value will favour 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 0 to favour use of indexes and prevent use of parallel query in computing the costing.

It is _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 an earlier release. This may also restrict 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, you can specify more for record purpose.

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 reduce. 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 sizing 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 you setup.

Oracle recommends 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 would prove to be faster than other type of joins in some conditions, especially when index is missing or search criteria is not very selective. Hash join requires large amount of memory as the hash tables are retained there, 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 type 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 use of PGA_AGGREGATE_TARGET instead of this parameter from Oracle 9i.

e.g.: hash_area_size = 2097152

Setting this to very low 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 to change or assign a value to this parameter, this will let oracle decide on the appropriate value for each individual query. In such case, 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 more number of database blocks than is available in the largest extent (that are all of uniform size). In case you have the same issue coming up, please 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 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 could 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 could 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 could 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 and 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 could 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 could 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 and 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 not easy to document on how they work. 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 could be avoided. For example, if the value is 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 above section.

7.3) _ALWAYS_ANTI_JOIN
Please refer above section.

7.4) _HASH_MULTIBLOCK_IO_COUNT
Please refer above section.

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 some thing similar to 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 could not 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 features expands the NVL function predicates to evaluate use of index that may be present on the column used in the function. For example, if 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 OR operator. This new expression will again be further transformed to make use of 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
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 LIKE predicate with bind variable as an equal-to predicate for costing purpose. This happens for expressions with index column being compared to a bind variable with LIKE operator. Hence, expressions like "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
Allows 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 use of indexes. 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 progress of queries that are running for long duration. Queries which cost more than the value set are identified for monitoring. Progression monitoring involves overhead and may affect the performance. The default value is 1000 and this 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
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 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 inherit the session's optimizer mode. Hence if 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
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
Perform 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
Enables/disables 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, 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, refers to number of blocks used for dynamic sampling by the optimizer.

e.g.: _optimizer_dyn_smp_blks = 32

7.27) _INDEX_JOIN_ENABLED
Enable use of index joins where ever 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, refers to the percentage of index caching that the optimizer considers. I find this similar to OPTIMIZER_INDEX_CACHING but may be behaving differently and having different meaning internally. Default value is 0.

e.g.: _system_index_caching = 0

7.29) _IMPROVED_OUTERJOIN_CARD
Enables use of improved outer-join cardinality calculation.

e.g.: _improved_outerjoin_card = true

7.30) _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
Enabling this favours use of star transformation in the database.

e.g.: _always_star_transformation = false

7.32) _OPTIMIZER_PERCENT_PARALLEL
Please refer above section.

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

This article was hosted on 30/08/03.
Press the Back button of you Browser to go to previous page.
Home