Oracle Performance Tuning | Effect of Optimizer_index_cost_adj parameter on Oracle Execution Plans

I have a SAP system with optimizer_index_cost_adj set to 10. Let’s look at Oracle execution plans and the resulting execution costs.

A value for “optimizer_index_cost_adj” =10 which will always favour index scans over full table scans. There are certain SQLs which will be better off with FULL Scan (with increased DB_FILE_MULTIBLOCK_READ_COUNT) over index scans.

select
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
from
v$system_event a,
v$system_event b
where
a.event = ‘db file scattered read’
and
b.event = ‘db file sequential read’;

This system event indicate scattered reads (Full table Scans) are done on avg at 5.9 MilliSec while Index scans are done at 94 Milli secs.
We need to push oracle optimizer to evaluate what is better ? INDEX and FULL table scans but at a moment due to above parameter oracle will always go for index even though full scan could be quicker and less expensive. We are in a situation where we are almost using a RULE based optimizer.

I have spooled details on where oracle is spending it’s most time..
Scattered Reads - Most FULL table Scans which are not happening much
Sequential Reads - Index Scans which are very frequent

I have spooled avg value for OPTIMIZER_INDEX_COST_ADJ parameter using statspack system wait events.The calculations are purely based on time taken for “scattered and sequential reads”. I can say it’s value should be set to 86 looking at last 4 days statspack data.

Example on how FTS can be quicker than Index Scans

I have picked up a worst performer for analysis here. Following view “”VBAP_VAPMA”2 is based on VBAP and VAPMA tables, VBAP listed in top wait segments consistently. As we know Optimizer_index_cost_adj is favouring index scans even if they are worst performer over FULL table scan. I have done some calcualtions below.

SELECT “AEDAT”, “AUART”, “ERDAT”, “ERNAM”, “KONDM”, “KUNNR”, “MATKL”, “MATNR”,
“NETWR”, “POSNR”, “VBELN”, “VKORG”, “WAERK”, “ZZAD_LINE_STATUS”,
“ZZCDO”, “ZZCDO_P”, “ZZKONDM_P”
FROM SAPR3.”VBAP_VAPMA”
WHERE “MANDT” = :a0
AND “AEDAT” > :a1
AND “AUART” = :a2
AND “KONDM” = :a3
AND “VKORG” = :a4
AND “ZZCDO” >= :a5

Setting “Optimizer_index_cost_adj=100 changes execution plan from index “VBAP~Z3″ to Full table sacn.

Optimizer_index_cost_adj=10 (Currently Set)
 SELECT STATEMENT Optimizer Mode=CHOOSE 2 313894
 TABLE ACCESS BY INDEX ROWID SAPR3.VAPMA 1 49 .4
 NESTED LOOPS 2 206 313893.8
 TABLE ACCESS BY INDEX ROWID SAPR3.VBAP 3 K 174 K 312568.2
 INDEX RANGE SCAN SAPR3.VBAP~Z3 15 M 100758
 INDEX RANGE SCAN SAPR3.VAPMA~Z01 1 3

Optimizer_index_cost_adj=100 (Oracle recommended Default Value)

SELECT STATEMENT Optimizer Mode=CHOOSE 2 577409
 TABLE ACCESS BY INDEX ROWID SAPR3.VAPMA 1 49 4
 NESTED LOOPS 2 206 577409
 TABLE ACCESS FULL SAPR3.VBAP 3 K 174 K 564153
 INDEX RANGE SCAN SAPR3.VAPMA~Z01 1 3

I will carry a simple calculations on how Oracle will estimate execution costs. Please note these are not precise formulas.

Approx Full Table Scan Cost : 484,193 Unadjusted
Cost here is calculated as “IO + CPU/1000 + NetIO*1.5″ but a simple formula is (No of blocks/DB_FILE_MULTIBLOCK_READCOUNT)
(No of blocks/DB_FILE_MULTIBLOCK_READCOUNT)= 3,873,549 blocks/8 = 484,193

If we increase DB_FILE_MULTIBLOCK_READCOUNT to 32 + Reorg of table , cost of “FULL Scan” will drop to 82,000 giving 5 fold increase in IO.

Cost of an Index Scan : 149,483 is Adjusted value
It is using a non-unique index “SAPR3.VBAP~Z3″ defined on columns MANDT, ZZBU_DIR, ZZBU_EDITION.
There are only 160 distinct values on this index out of 15.9 million rows -

“select MANDT, ZZBU_DIR, ZZBU_EDITION from SAPR3.vbap”

Index Range Scan Cost = blevel + (Avg leaf blk per key * (num_rows * selectivity))= 1,188,451 (Actual Value) > than FTS

Since we have set Optimizer_index_cost_adj=10, real cost we set is = 1,188,451*10/100= 118845.1 which is 10% of actual overhead

Final value of index cost must include efforts for accessing data blocks = Previous Cost + (Avg_data_blks_per_key * (Clustering_fact / Total Table blks))= 149,483

Conclusion:
We need to let oracle optimizer decide a best path for execution than forcing it to choose indexes all the time. Putting defualt value for “optimizer_index_cost_adj” must be followed with up-to-date stats as cost based optmizer is heavily dependent on right stats.