Oracle Tips by
In Oracle7 and Oracle8, the
hash_join_enabled parameter must be set to TRUE to invoke a hash
join. In Oracle8i hash_joined_enabled=true is the
The hash_multiblock_io_count Parameter
Because hash joins often read sequential blocks
from the TEMP tablespace, the hash_multiblock_io_count
parameter allows multiblock reads against the TEMP tablespace. It is
advisable to set the NEXT extent size to greater than the value for
hash_multiblock_io_count to reduce disk I/O. This is the same
behavior we see when setting the db_file_multiblock_read_count
parameter for data tablespaces, except that this applies only to the
multiblock access of segments in the TEMP tablespace.
The star_transformation_enabled Parameter
parameter determines whether a cost-based query transformation will be
applied to star queries. If set to TRUE, the optimizer will consider
performing a cost-based query transformation on the n-way table join.
This parameter also relates to the optimizer_search_limit
parameter, as this parameter must be LESS THAN the number of tables in
the n-way table join in order for the optimizer to consider a star
join. With the default value of five, only queries that join six or
more tables are eligible for star optimization.
Also, star optimization requires that a fully
concatenated index exist on the fact table. In other words, if you
have a fact table with 10 dimension tables, you must have a single
index on the fact table with all 10 foreign keys into the dimension
The optimizer_feature_enable Parameter
The optimizer_feature_enable parameter
is a meta-parameter set to the defaults for other initialization
parameters. Introduced in Oracle 8.0.4, it is a generic parameter that
is set to the current version of your database.
The value for optimizer_feature_enable
is set for your release of Oracle (optimizer_feature_enable
=8.1.6), and it will default to your database version for Oracle
releases greater than 8.0.4. The optimizer_feature_enable is a
meta-parameter that set the values of many other initialization
parameters. When optimizer_feature_enable is set, the following
initialization parameters are set to TRUE.
Of course, you will always want these features,
so it is best to leave this parameter at the existing setting.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.