ShaeCalabrese335

Every time a valid SQL statement is processed Oracle has to decide how to get the required information. Identify more on this partner article - Navigate to this webpage mellissa Activity Streams EmployThis. This decision can be made using 1 of 2 methods Rule-based Optimizer RBO - This process can be used if the server has no inner research relating to the objects called by the record. This method is desupported in future releases and is likely to be no longer favoured by Oracle. Expense Based Optimizer CBO - This method can be used if internal research exist. The CBO checks many possible execution plans and chooses the one with the lowest cost, where cost pertains to system resources. If an oracle init parameter optimizer_index_cost_adj is ready to a wrong value an oracle CBO may have a knock-on effect. I stumbled upon this problem while working with a press consumer using SAP CRM/BW programs on top of oracle database level. The total database size was in excess of 4 tera Bytes. Ive found a worst performing SQL for investigation here. A view VBAP_VAPMA is founded on VBAP and VAPMA tables, VBAP listed in top wait pieces regularly. Be taught more about www.surfline.com/company/bios/index.cfm by browsing our surprising wiki. I could see optimizer_index_cost_adj is favouring index tests even if theyre worst singer over FULL table scan. I have done some calculations to prove the point. To get other interpretations, please check-out visit link. 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 1-0. If you think anything at all, you will seemingly require to discover about Nexopia Blog. Placing Optimizer_index_cost_adj=100 improvements execution plan from list VBAPZ3 to Full dining 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 E 174 K 312568.2 INDEX RANGE SCAN SAPR3.VBAPZ3 15 M 100758 INDEX ASSORTMENT SCAN SAPR3.VAPMAZ01 1 3 Optimizer_index_cost_adj=100 Oracle proposed Default Value SELECT STATEMENT Optimizer Mode=CHOOSE 2 577409 TABLE ACCESS BY INDEX ROWID SAPR3.VAPMA 1 49 4 NESTED LOOPS 2 206 577409 DINING table ACCESS FULL SAPR3.VBAP 3 K 174 K 564153 INDEX RANGE SCAN SAPR3.VAPMAZ01 1 3 I will do simple calculations how Oracle is estimating delivery charges here. Please note these are not correct formulas. Approximately Full Table Scan Cost 484,193 Unadjusted Cost here is calculated as IO + CPU/1000 + NetIO*1.5 but a straightforward system will 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. Charge of an Index Scan 149,483 is Adjusted importance It is employing a non-unique index SAPR3.VBAPZ3 defined o-n columns MANDT, ZZBU_DIR, ZZBU_EDITION. You can find only 160 distinct values with this list out of 15.9 million rows - select MANDT, ZZBU_DIR, ZZBU_EDITION from SAPR3.vbap Catalog 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 price we set is = 1,188,451*10/100= 118845.1 which is a huge number of actual overhead Efforts must be included by final value of index cost for accessing data blocks = Previous Cost + Avg_data_blks_per_key * Clustering_fact / Total Dining table blks = 149,483 Conclusion We need to let oracle optimizer decide a best way for performance than forcing it to select constantly to indexes. Getting default value-for optimizer_index_cost_adj has to be used with up-to-date stats as cost based optmizer is heavily dependent on right stats. http://OracleDbaSupport.co.uk is a of Sagar Patil, an independent oracle consultant with an excellent understanding of the way the Oracle database motor Oracle Applications interact..