OPTIMIZER_MODE, OPTIMIZER_INDEX_COST_ADJ, and
OPTIMIZER_INDEX_CACHING
Article by author Brian Carr
While its very common for DBAs to leave these
parameters at their default settings, setting these to
non-default values could have a great impact on query usage,
specifically in areas where full table scans are occurring.
OPTIMIZER_MODE
specifically is a key tuning parameter, as it tells the Oracle
optimizer how to focus on tuning goals. The default of
ALL_ROWS generally prefers full table scans
with MERGE joins as opposed to index
reads with NESTED LOOP joins.
OPTIMIZER_INDEX_CACHING
and OPTIMIZER_INDEX_COST_ADJ
are ‘tuning knobs’ that allow a DBA to push
Oracle toward index usage or full table scan usage.
OPTIMIZER_INDEX_COST_ADJ, for instance, is
a percentage based parameter with a default of 100. As it is
decreased, the cost of index usage directly decreases. If
OPTIMIZER_INDEX_COST_ADJ is set to a value
of 80, index usage will ‘cost’ 80% of the normal cost when being
evaluated by the optimizer.
|
|
