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.
According to the
Oracle documentation "OPTIMIZER_INDEX_CACHING favors using selective
indexes. That is, if you use a relatively low value for this parameter,
the optimizer effectively models the caches of all non-leaf index
blocks. In this case, the optimizer bases the cost of using this index
primarily on the basis of its selectivity. Thus, by setting
OPTIMIZER_INDEX_CACHING to a low value, you achieve the desired modeling
of the index caching without over using possibly undesirable indexes
that have poor selectivity."
|
