Oracle-expense-based-optimizer-aftereffect-of-optimizerindexcostadj-parameter290

Oracle Expense Based Optimizer & Aftereffect of Optimizer_index_cost_adj Parameter

Whenever a valid SQL statement is processed Oracle must determine just how to access the necessary information. This decision can be made using one of two methods:

Rule Based Optimizer (RBO) - This method can be used if the host has no research associated with the items introduced by the statement. This technique is desupported in future releases and will-be no longer favoured by Oracle.

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

An oracle CBO could have a knock-on influence if an oracle init parameter optimizer_index_cost_adj is ready to a wrong value. While dealing with a press customer using SAP CRM/BW applications on the top of oracle database level I came across this issue. The total database size was in excess of 4 tera Bytes.

I have picked-up a worst performing SQL for investigation here. Should people need to dig up more on like, there are heaps of online libraries you might consider pursuing. A view ''VBAP_VAPMA' is based on VBAP and VAPMA tables, VBAP outlined in top wait pieces consistently. I really could see optimizer_index_cost_adj is favouring catalog tests even when they're worst musician over FULL table scan. I've done some calculations to prove the idea. Clicking like us on facebook certainly provides suggestions you can tell your brother.

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 placed for 1-0. Establishing 'Optimizer_index_cost_adj=100 improvements execution plan from index 'VBAP~Z3' to Full table scan.

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 174 E 312568.2 INDEX RANGE SCAN SAPR3.VBAP~Z3 1-5 M 100758 INDEX SELECTION SCAN SAPR3.VAPMA~Z01 1 3

Optimizer_index_cost_adj=100 (Oracle suggested Default Value)

SELECT STATEMENT Optimizer Mode=CHOOSE 2 577409

TABLE ACCESS BY INDEX ROWID SAPR3.VAPMA 1 4-9 4

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

I will do simple calculations on how Oracle is calculating delivery prices here. Please note these aren't specific formulas.

Approx Full Dining table Scan Price : 484,193 Unadjusted

Cost listed here is determined as 'IO + CPU/1000 + NetIO*1.5' but a straightforward method 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 delivery cost : Increase DB_FILE_MULTIBLOCK_READCOUNT to 32 + Reorg of desk, cost of 'FULL Scan' may drop to 82,000 giving 5 fold increase in IO.

Charge of an Index Scan : 149,483 is Adjusted importance

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

There are only 160 unique values on this list 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 have set Optimizer_index_cost_adj=10 so true price we set is = 1,188,451*10/100= 118845.1 that is hundreds of actual overhead

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

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

Conclusion:

We have to allow oracle optimizer choose a best way for delivery than making it to choose constantly to indexes. Putting default value for 'optimizer_index_cost_adj' have to be used with up-to-date stats as price based optmizer is heavily determined by right stats.

http://OracleDbaSupport.co.uk is just a website of Sagar Patil, an independent oracle consultant with a great knowledge of how the Oracle database engine & Oracle Applications come together..