HelmsBurkhalter209

Each time a valid SQL statement is prepared Oracle needs to determine how exactly to get the required knowledge. This decision could be made using 1 of 2 methods Rule Based Optimizer RBO - This technique can be used if the machine has no statistics associated with the objects referenced by the record. This technique is no further favoured by Oracle and is likely to be desupported in future releases. Cost Based Optimizer CBO - This method is used if central data exist. The CBO checks a few possible execution plans and selects the one with the cheapest cost, where cost relates to system resources. If an oracle init parameter optimizer_index_cost_adj is about to a wrong value an oracle CBO may have a knock on impact. I stumbled upon this dilemma while dealing with a press consumer using SAP CRM/BW applications on top of oracle database layer. Dig up further on surfline.com/company/bios/index.cfm by navigating to our telling link. The sum total database size was over 4 tera Bytes. Be taught more on our favorite related website by going to surfline.com/company/bios/index.cfm. 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 sectors constantly. I could see optimizer_index_cost_adj is favouring catalog tests even though theyre worst singer over FULL table scan. Ive 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 value Optimizer_index_cost_adj is defined for 10. Setting Optimizer_index_cost_adj=100 changes execution plan from index VBAPZ3 to Full dining table scan. Optimizer_index_cost_adj=10 SELECT ASSERTION Optimizer Mode=CHOOSE 2 313894 TABLE ACCESS BY INDEX ROWID SAPR3.VAPMA 1 4-9.4 NESTED LOOPS 2 206 313893.8 TABLE ACCESS BY INDEX ROWID SAPR3.VBAP 3 E 17-4 K 312568.2 INDEX RANGE SCAN SAPR3.VBAPZ3 1-5 M 100758 INDEX ARRAY SCAN SAPR3.VAPMAZ01 1 3 Optimizer_index_cost_adj=100 Oracle recommended 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 E 564153 INDEX RANGE SCAN SAPR3.VAPMAZ01 1 3 Ill do simple calculations on what Oracle is estimating execution prices here. Please note these are not correct formulas. Copyright is a dynamite online database for further concerning how to allow for it. Around Complete Table Check Cost 484,193 Unadjusted Cost here is determined as IO + CPU/1000 + NetIO*1.5 but a simple system 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 table, cost of FULL Scan will drop to 82,000 giving 5 fold increase in IO. Charge of an Index Scan 149,483 is Adjusted importance It is using a non-unique index SAPR3.VBAPZ3 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 List Range Scan Cost = blevel + Avg leaf blk per critical * num_rows * selectivity = 1,188,451 Actual Value than FTS Weve set Optimizer_index_cost_adj=10 therefore true cost we set is = 1,188,451*10/100= 118845.1 which can be 10 of actual overhead Efforts must be included by final value of index cost for accessing information blocks = Past Cost + Avg_data_blks_per_key * Clustering_fact / Total Dining table blks = 149,483 Conclusion We have to allow oracle optimizer choose a best path for performance than making it to choose indexes all the time. Navigating To http://surfline.com/company/bios/index.cfm seemingly provides tips you could use with your girlfriend. Putting default value-for optimizer_index_cost_adj should be followed with up-to-date stats as charge based optmizer is heavily determined by right stats. http://OracleDbaSupport.co.uk is really a of Sagar Patil, an unbiased oracle guide with an excellent understanding of the way the Oracle database engine Oracle Applications work together..