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.
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
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.
parameter values that I have mentioned below in examples are settings that I
have used in some OLTP setups on Oracle 126.96.36.199 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.
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
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.
optimizer_mode = first_rows
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.
optimizer_features_enable = 8.1.7
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.
optimizer_max_permutations = 2000
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.
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.
lower the value (less than 100), the less full table scan executions will take
place in the system.
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.
optimizer_index_cost_adj = 10
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.
optimizer_index_caching = 100
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.
optimizer_percent_parallel = 0
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.
compatible = 8.1.7
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.
db_file_multiblock_read_count = 1 (may be set to a higher value)
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
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.
recommends the use of PGA_AGGREGATE_TARGET instead of this parameter from
sort_area_size = 1048576
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
sort_multiblock_read_count = 2
joins are available only in CBO. Valid values are
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
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.
hash_join_enabled = true
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.
recommends the use of PGA_AGGREGATE_TARGET instead of this parameter from
hash_area_size = 2097152
this to a very low number may sometimes result in the following error.
Hash Join ran out of memory while keeping large rows in memory.
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.
parameter is renamed to _HASH_MULTIBLOCK_IO_COUNT as of Oracle 9i.
hash_multi_block_io_count = 0
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.
unable to allocate an extent of %s blocks from tablespaces %s
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
recommends use of PGA_AGGREGATE_TARGET instead of this parameter from Oracle 9i.
bitmap_merge_area_size = 1048576
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.
query_rewrite_enabled = true
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.
query_rewrite_integrity = enforced
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
parameter is renamed to _ALWAYS_ANTI_JOIN as of Oracle 9i.
always_anti_join = nested_loops
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.
parameter is renamed to _ALWAYS_SEMI_JOIN as of Oracle 9i.
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 188.8.131.52 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.
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
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
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.
can be set to a value between 10 MB to 4000 GB, depending on the setup
7. Internal Oracle parameters that affect the Optimizer
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.
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 184.108.40.206 with recommendation
and consultation from support. Most of these are set to the default value.
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.
_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)
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.
_complex_view_merging = true
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
_push_join_predicate = true
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.
_push_join_union_view = true
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.
_or_expand_nvl_predicate = true
disable the OR operator expansion by
_no_or_expansion = false
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".
_like_with_bind_as_equality = true
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.
_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:
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.
_use_column_stats_for_function = true
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
_ordered_nested_loop = true
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.
_sqlexec_progression_cost = 0
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.
_optimizer_undo_changes = false
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
_new_initial_join_orders = true
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.
_b_tree_bitmap_plans = false
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.
_optimizer_mode_force = true
enables un-nesting of correlated sub-queries. Such queries may undergo MERGE
value is FALSE in Oracle 8i and TRUE in Oracle 9i.
_unnest_subquery = false
makes use of index full scans more
often. There are some published issues related to this in Oracle 8i.
_optim_enhance_nnul_detection = false.
performs cost based rewrite with
materialized views. There are some published issues related to this in Oracle
_query_cost_rewrite = false
is enhanced for computing the average row length, this option is enabled by the
_improved_row_length_enabled = true.
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.
_use_nosegment_indexes = false.
enables/disables the use of sort
merge joins on inequality conditions. Setting the parameter to TRUE will
disable inequality Sort merge joins.
_sortmerge_inequality_join_off = false
in Oracle 9i, _NEW_SORT_COST_ESTIMATE enables the use of new cost estimate
process for sort.
_new_sort_cost_estimate = true
to OPTIMIZER_DYNAMIC_SAMPLING, this refers to the number of blocks used for
dynamic sampling by the optimizer.
_optimizer_dyn_smp_blks = 32
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.
_index_join_enabled = false
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.
_system_index_caching = 0
enables the use of improved
outer-join cardinality calculation.
_improved_outerjoin_card = true
forces the optimizer to use the
specified permutation. Default value is 0.
_optimizer_choose_permutation = 0
_ALWAYS_STAR_TRANSFORMATION is enabled, Optimizer will prefer the use of
start transformed query. However, what extent this will be done is not
_always_star_transformation = false
Please refer section 6.6 (OPTIMIZER_PERCENT_PARALLEL)
7.33) More Internal parameters
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