 |
|
The push_join_predicate
Parameter
Oracle Tips by
Burleson
|
Prior to Oracle8i, the
push_join_predicate parameter is used when views participate in a
table join. This parameter is obsolete starting with Oracle8i.
It controls whether the optimizer will attempt to push join predicates
into a view. Predicates can only be pushed if push_join_predicate=true
and the prerequisites conditions are met. The view must contain one of
the following conditions:
Note that prior to Oracle8i, this
feature is automatically enabled by setting the
optimizer_features_enable parameter. In Oracle8i and
beyond, push_join_predicate is the default behavior.
The optimizer_index_caching Parameter
The optimizer_index_caching parameter is
a percentage parameter with valid values between zero and 100. This
parameter lets you adjust the behavior of the cost-based optimizer to
select nested loop joins more often or less often. The cost of
executing a nested loop join where an index is used to access the
inner table is highly dependent on the caching of that index in the
buffer cache. The amount of index caching depends on factors, such as
the load on the system and the block access patterns of different
users, that the optimizer cannot predict. Of course, you may cache an
index by placing the data block in the KEEP pool, thereby ensuring
that the blocks are always cached.
Setting optimizer_index_caching to a
higher percentage makes nested loop joins look less expensive to the
optimizer, which will be more likely to pick nested loop joins over
hash or sort merge joins.
The default value for the
optimizer_index_caching parameter is 0, which gives the highest
preference to hash joins and sort merge joins. Resetting this
parameter can be very dangerous if you are not using stored outlines
because it could change the execution plans for thousands of SQL
statements. Also, because the cost-based optimizer will generally only
invoke sort merge joins when there are no indexes on the joined
tables, this parameter has the most effect on the invocation of hash
joins.
Initialization Parameters Affecting CBO Behavior for Hash Joins
The hash join is one of the trickiest to
implement because of the dependencies with the Oracle initialization
parameters. Here is a brief summary of the hash join parameters. For
more information on hash join hints, see Chapter 12, and for details
on tuning hash joins, see Chapter 16.
The hash_area_size Parameter
The hash_area_size parameter specifies
the maximum amount of memory, in bytes, to be used for the hash join.
If this parameter is not set, its value defaults to twice the value of
the sort_area_size parameter. Unlike many other initialization
parameters that you cannot change without bouncing the database,
hash_area_size is changed with the alter session set
hash_area_size=nnn command.
The RAM for hash_area_size is allocated
from the User Global Area (UGA) of a user process. For dedicated
server connections, this is part of process memory (PGA). For
multithreaded servers, the UGA is allocated from either the Shared
Pool or the Large Pool, if the Large Pool is configured.
The hash_area_size is allocated for a
process that executes a hash join, and it does not cause increased RAM
consumption for queries that are not executing hash joins. As a
general rule, the hash_area_size should be set to the size of
the smaller table in the query, because the contents of this table
will be hashed into the RAM region.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
