Use of optimizer_index_cost_adj Parameter in Oracle
Optimizer_index_cost_adj parameter is an initialization parameter that can be very useful for SQL tuning. It is a numeric parameter with values from zero to 10,000 and a default value of 100.
General Setting for OLTP environment:
Alter system Set optimizer_index_caching=80 scope=both;
alter system set optimizer_index_cost_adj=25 scope=both;
OPTIMIZER_INDEX_COST_ADJ is used for choosing the access path selection to be more or less index over a full table scan. As value is making below 100 then it will go to choose index path instead of full table scan.
For OLTP systems, setting parameter to a smaller value (around 25) may result in performance gains as SQL statements change from large-table full-table scans to index range scans.
Note: Value 100 means optimizer has equal rights to choose from multiple path or index path.
A single block I/O as performed typically by an index range scan is measured via the “db file sequential read” wait event while the multi-block I/O as typically performed during a FTS is measured via the “db file scattered read” wait event.
For Better Result
In order to determine these wait events during a specific time period to perhaps better reflect typical loads during these times, one could also simply run a Statspack or an AWR report and look at the wait event section of the report.
Find optimal value from v$system_event with following formula: (it better to get from AWR or Statspack report)
Optimizer_index_cost_adj = (Avg waits DB file Sequential read/Avg waits DB file scattered read) * 100
alter session set optimizer_index_cost_adj = 50;
Note: For example, a setting of 50 makes the index access path look half as expensive as normal.
Following query give parameter value according to current waits:
- scattered read (full table scans) are fast at 13ms (c3)
- sequential reads (index probes) take much longer 86ms (c4)
- starting setting for optimizer_index_cost_adj at 36:
a.total_waits /(a.total_waits + b.total_waits)*100 c3,
b.total_waits /(a.total_waits + b.total_waits)*100 c4,
(b.average_wait / a.average_wait)*100 c5
a.event = 'db file scattered read'
b.event = 'db file sequential read';
C1 C2 C3 C4 C5 ------- ------ --- --- --- 13,824 5,072 13 86 36
We always expert scattered reads (full-table scans) to be far faster than sequential reads (index probes) because of Oracle sequential pre fetch (see db_file_multiblock_read_count):
select value from sys.v_$parameter where name = 'db_file_multiblock_read_count';
alter session set db_file_multiblock_read_count = 32768;
Another query to find the optimal value
set pages 80
set lines 130
col c1 heading 'Average Waits for|Full Scan Read I/O' format 999999.999
col c2 heading 'Average Waits for|Index Read I/O' format 999999.999
col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format 999.99
col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format 999.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 99999
select a.snap_id "Snap",
(sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c3,
(sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c4,
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 c5
where a.snap_id = b.snap_id
and a.event_name = 'db file scattered read'
and b.event_name = 'db file sequential read';