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

August 26, 2003

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.