| Whenever a valid SQL statement is processed Oracle | | | | Optimizer_index_cost_adj=10 |
| has to decide how to retrieve the necessary data. | | | | SELECT STATEMENT Optimizer Mode=CHOOSE 2 |
| This decision can be made using one of two | | | | 313894 TABLE ACCESS BY INDEX ROWID |
| methods: | | | | SAPR3.VAPMA 1 49 .4 NESTED LOOPS 2 206 |
| • Rule Based Optimizer (RBO) - This method is | | | | 313893.8 TABLE ACCESS BY INDEX ROWID |
| used if the server has no internal statistics relating to | | | | SAPR3.VBAP 3 K 174 K 312568.2 INDEX RANGE |
| the objects referenced by the statement. This | | | | SCAN SAPR3.VBAP~Z3 15 M 100758 INDEX RANGE |
| method is no longer favoured by Oracle and will be | | | | SCAN SAPR3.VAPMA~Z01 1 3 |
| desupported in future releases. | | | | Optimizer_index_cost_adj=100 (Oracle |
| Cost Based Optimizer (CBO) - This method is used if | | | | recommended Default Value) |
| internal statistics are present. The CBO checks | | | | SELECT STATEMENT Optimizer Mode=CHOOSE 2 |
| several possible execution plans and selects the one | | | | 577409 |
| with the lowest cost, where cost relates to system | | | | TABLE ACCESS BY INDEX ROWID SAPR3.VAPMA 1 |
| resources. | | | | 49 4 |
| An oracle CBO will have a knock on effect if an | | | | NESTED LOOPS 2 206 577409 TABLE ACCESS FULL |
| oracle init parameter | | | | SAPR3.VBAP 3 K 174 K 564153 INDEX RANGE SCAN |
| “optimizer_index_cost_adj” is set to a | | | | SAPR3.VAPMA~Z01 1 3 |
| wrong value. I came across this issue while working | | | | I will do simple calculations on how Oracle is |
| with a media client using SAP CRM/BW applications | | | | estimating execution costs here. Please note these |
| on top of oracle database layer. The total database | | | | are not precise formulas. |
| size was in excess of 4 tera Bytes. | | | | Approx Full Table Scan Cost : 484,193 Unadjusted |
| I have picked up a worst performing SQL for analysis | | | | Cost here is calculated as "IO + CPU/1000 + |
| here. A view ""VBAP_VAPMA" is based on VBAP and | | | | NetIO*1.5" but a simple formula would be (No of |
| VAPMA tables, VBAP listed in top wait segments | | | | blocks/DB_FILE_MULTIBLOCK_READCOUNT) |
| consistently. I could see optimizer_index_cost_adj is | | | | (No of blocks |
| favouring index scans even if they are worst | | | | DB_FILE_MULTIBLOCK_READCOUNT)= 3,873,549 |
| performer over FULL table scan. I have done some | | | | blocks/8 = 484,193 |
| calculations to prove the point. | | | | How to drop execution cost : Increase |
| SELECT "AEDAT", "AUART", "ERDAT", "ERNAM", | | | | DB_FILE_MULTIBLOCK_READCOUNT to 32 + Reorg |
| "KONDM", "KUNNR", "MATKL", "MATNR", "NETWR", | | | | of table , cost of "FULL Scan" will drop to 82,000 |
| "POSNR", "VBELN", "VKORG", "WAERK", | | | | giving 5 fold increase in IO. |
| "ZZAD_LINE_STATUS", "ZZCDO", "ZZCDO_P", | | | | Cost of an Index Scan : 149,483 is Adjusted value |
| "ZZKONDM_P" | | | | It is using a non-unique index "SAPR3.VBAP~Z3" |
| FROM SAPR3."VBAP_VAPMA" | | | | defined on columns MANDT, ZZBU_DIR, |
| WHERE "MANDT" = :a0 | | | | ZZBU_EDITION. |
| AND "AEDAT" > :a1 | | | | There are only 160 distinct values on this index out |
| AND "AUART" = :a2 | | | | of 15.9 million rows - "select MANDT, ZZBU_DIR, |
| AND "KONDM" = :a3 | | | | ZZBU_EDITION from SAPR3.vbap" |
| AND "VKORG" = :a4 | | | | Index Range Scan Cost = blevel + (Avg leaf blk per |
| AND "ZZCDO" >= :a5 | | | | key * (num_rows * selectivity))= 1,188,451 (Actual |
| Current value Optimizer_index_cost_adj is set for 10. | | | | Value) > than FTS |
| Setting "Optimizer_index_cost_adj=100” changes | | | | We have set Optimizer_index_cost_adj=10 so real |
| execution plan from index "VBAP~Z3" to Full table | | | | cost we set is = 1,188,451*10/100= 118845. |
| scan. | | | | |