Use of optimizer index cost adj Parameter in Oracle

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.
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.average_wait c1,
b.average_wait c2,
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
v$system_event a,
v$system_event b
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.time_waited_micro)/sum(a.total_waits)/10000 c1,
sum(b.time_waited_micro)/sum(b.total_waits)/10000 c2,
(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(b.time_waited_micro)/sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 c5
dba_hist_system_event a,
dba_hist_system_event b
where a.snap_id = b.snap_id
and a.event_name = 'db file scattered read'
and b.event_name = 'db file sequential read';


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s