The Homeopathic Blog

Set Blk

Oracle Cost Based Optimizer & Effect of Optimizer_index_cost_adj Parameter

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

• Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.

Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.

An oracle CBO will have a knock on effect if an oracle init parameter “optimizer_index_cost_adj” is set to a wrong value. I came across this issue while working with a media client using SAP CRM/BW applications on top of oracle database layer. The total database size was in excess of 4 tera Bytes.

I have picked up a worst performing SQL for analysis here. A view ""VBAP_VAPMA" is based on VBAP and VAPMA tables, VBAP listed in top wait segments consistently. I could see optimizer_index_cost_adj is favouring index scans even if they are worst performer over FULL table scan. I have done some calculations to prove the point.

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

Current value Optimizer_index_cost_adj is set for 10. Setting "Optimizer_index_cost_adj=100” changes execution plan from index "VBAP~Z3" to Full table scan.

Optimizer_index_cost_adj=10

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 do simple calculations on how Oracle is estimating execution costs here. 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 would be (No of blocks/DB_FILE_MULTIBLOCK_READCOUNT)

(No of blocks/DB_FILE_MULTIBLOCK_READCOUNT)= 3,873,549 blocks/8 = 484,193

How to drop execution cost : 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

We have set Optimizer_index_cost_adj=10 so 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 default value for "optimizer_index_cost_adj" must be followed with up-to-date stats as cost based optmizer is heavily dependent on right stats.

http://OracleDbaSupport.co.uk is a blog site of Sagar Patil, an independent oracle consultant with a great understanding of how the Oracle database engine & Oracle Applications work together.

About the Author

What belly dance costume do you think is nicer???????????????????

This one : http://cgi.ebay.ca/Belly-Dance-Harem-Cabaret-Costume-Bustier-Skirt-Blk_W0QQitemZ370326079348QQcmdZViewItemQQptZAU_DanceWear?hash=item5639278774

or this one: http://cgi.ebay.ca/Black-BRAND-NEW-EMBROIDERED-BELLY-DANCE-COSTUME-Set_W0QQitemZ160399732081QQcategoryZ314QQcmdZViewItemQQ_trksidZp4340.m263QQ_trkparmsZalgo=SI&its=I%2BC&itu=UCI&otn=10&ps=63

i like the first one better =]