Oracle Optimizer: Moving to and working with CBO - Part 2 - Page 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.