OPTIMIZER_MODE, OPTIMIZER_INDEX_COST_ADJ, and OPTIMIZER_INDEX_CACHING
Read this to learn about OPTIMIZER_MODE, OPTIMIZER_INDEX_COST_ADJ, and OPTIMIZER_INDEX_CACHING.
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.
