BossBaier570

Every time a appropriate SQL statement is prepared Oracle must determine how to retrieve the required data. This choice can be made using one of two methods Rule-based Optimizer (RBO) - This process is used if the host has no internal research referring to the objects referenced by the statement. This process is desupported in future releases and will be not favoured by Oracle. Expense Based Optimizer (CBO) - This process can be used if inner statistics exist. Identify additional information on 9 Tips To Keep Windows XP Running Easy by browsing our provocative encyclopedia. The CBO checks many possible execution plans and selects the one with the lowest cost, where cost relates to system resources. An oracle CBO may have a knock on impact if an oracle init parameter optimizer_index_cost_adj is placed to a wrong value. While dealing with a press customer using SAP CRM/BW applications on top of oracle database layer I came across this issue. The sum total database size was over 4 tera Bytes. I've found a worst performing SQL for analysis here. A view VBAP_VAPMA' is based on VBAP and VAPMA tables, VBAP outlined in top wait segments constantly. I could see optimizer_index_cost_adj is favouring index scans even if they are worst singer over FULL dining 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 Present price Optimizer_index_cost_adj is placed for 10. If you are interested in politics, you will possibly choose to learn about http://surfline.com/company/bios/index.cfm. Placing '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 E 312568.2 INDEX RANGE SCAN SAPR3.VBAP~Z3 1-5 M 100758 INDEX ARRAY SCAN SAPR3.VAPMA~Z01 1 3 Optimizer_index_cost_adj=100 (Oracle suggested 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 K 174 K 564153 INDEX RANGE SCAN SAPR3.VAPMA~Z01 1 3 I will do simple calculations on how Oracle is costing delivery prices here. Please note these are not exact formulas. Approx Full Dining table Check Charge 484,193 Unadjusted Cost here is calculated as 'IO + CPU/1000 + NetIO*1.5' but a simple 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 performance cost Increase DB_FILE_MULTIBLOCK_READCOUNT to 32 + Reorg of desk, cost of 'FULL Scan' may drop to 82,000 providing 5-fold increase in IO. Price of an Index Scan 149,483 is Adjusted price It's utilizing a non-unique index 'SAPR3.VBAP~Z3' defined on tips MANDT, ZZBU_DIR, ZZBU_EDITION. You will find only 160 distinct values on this index out-of 15.9 million rows - 'select MANDT, ZZBU_DIR, ZZBU_EDITION from SAPR3.vbap' Catalog Range Scan Cost = blevel + (Avg leaf blk per crucial * (num_rows * selectivity) )= 1,188,451 (Actual Value) > than FTS We have set Optimizer_index_cost_adj=10 so true cost we set is = 1,188,451*10/100= 118845.1 that will be a large number of actual overhead Efforts must be included by final value of index cost for accessing information blocks = Previous Cost + (Avg_data_blks_per_key * (Clustering_fact / Total Dining table blks) )= 149,483 Conclusion We must allow oracle optimizer choose a best path for performance than requiring it to choose indexes constantly. Be taught new information on our related encyclopedia by clicking www.surfline.com/company/bios/index.cfm. Putting default value-for 'optimizer_index_cost_adj' must be used with up-to-date stats as charge based optmizer is heavily dependent on right stats. http://OracleDbaSupport.co.uk is just a of Sagar Patil, an unbiased oracle consultant with an excellent understanding of the way the Oracle database motor & Oracle Applications work together.. Discover more on Understanding Paid URL Inclusion – Can Ex Back by visiting our telling article directory.