Oracle-cost-based-optimizer-effect-of-optimizerindexcostadj-parameter137

Oracle Cost Based Optimizer & Effect of Optimizer_index_cost_adj Parameter

Every time a appropriate SQL statement is prepared Oracle needs to determine how to access the required data. This decision may be made using one of two methods:

Rule Based Optimizer (RBO) - This method is used if the server has no central research referring to the objects referenced by the statement. This technique is no longer favoured by Oracle and is likely to be desupported in future releases.

Price Based Optimizer (CBO) - This technique can be used if inner data are present. The CBO checks a few possible execution plans and chooses the one with the lowest cost, where cost pertains to system resources.

An oracle CBO may have a knock on influence if an oracle init parameter optimizer_index_cost_adj is ready to a wrong value. I stumbled upon this problem while dealing with a media consumer using SAP CRM/BW applications on the top of oracle database layer. The full total database size was more than 4 tera Bytes. To get another interpretation, we recommend you take a gander at: http://surfline.com/company/bios/index.cfm.

I've picked-up a worst performing SQL for investigation here. A view ''VBAP_VAPMA' is based on VBAP and VAPMA tables, VBAP listed in top wait portions regularly. If you hate to get supplementary information on go, there are many online resources people could pursue. I could see optimizer_index_cost_adj is favouring list tests even when they are worst singer over FULL table scan. I've done some calculations to prove the idea.

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 price Optimizer_index_cost_adj is defined for 10. Placing 'Optimizer_index_cost_adj=100 improvements execution plan from list 'VBAP~Z3' to Full table scan. I discovered via by browsing books in the library.

Optimizer_index_cost_adj=10

SELECT ASSERTION 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 E 17-4 E 312568.2 INDEX RANGE SCAN SAPR3.VBAP~Z3 15 M 100758 INDEX ARRAY SCAN SAPR3.VAPMA~Z01 1 3

Optimizer_index_cost_adj=100 (Oracle encouraged Default Value)

SELECT STATEMENT Optimizer Mode=CHOOSE 2 577409

DINING table ACCESS BY INDEX ROWID SAPR3.VAPMA 1 49 4

NESTED LOOPS 2 206 577409 TABLE ACCESS FULL SAPR3.VBAP 3 E 174 K 564153 INDEX RANGE SCAN SAPR3.VAPMA~Z01 1 3

I'll do simple calculations on what Oracle is calculating delivery prices here. Please be aware these are not correct formulas. Discover supplementary info on the affiliated web resource by going to www.surfline.com/company/bios/index.cfm.

Around Complete Table Scan Price : 484,193 Unadjusted

Price here's calculated as 'IO + CPU/1000 + NetIO*1.5' but an easy 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 providing 5 fold increase in IO.

Cost of an Index Scan : 149,483 is Adjusted price

It's employing a non-unique index 'SAPR3.VBAP~Z3' defined on columns MANDT, ZZBU_DIR, ZZBU_EDITION.

You will find only 160 different 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 * (num_rows * selectivity) )= 1,188,451 (Actual Value) > than FTS

We've set Optimizer_index_cost_adj=10 therefore true cost we set is = 1,188,451*10/100= 118845.1 which can be a huge number of actual overhead

Final value of index cost should include efforts for accessing data blocks =

Past Cost + (Avg_data_blks_per_key * (Clustering_fact / Total Table blks) )= 149,483

Conclusion:

We need to allow oracle optimizer determine a best path for execution than making it to choose all the time to indexes. Getting default value-for 'optimizer_index_cost_adj' should be adopted with up-to-date stats as price based optmizer is heavily influenced by right stats.

http://OracleDbaSupport.co.uk is just a blog site of Sagar Patil, a completely independent oracle consultant with a great understanding of the way the Oracle database motor & Oracle Applications interact..