OzunaBill788

Whenever a valid SQL statement is processed Oracle must decide how exactly to obtain the required data. This decision can be made using 1 of 2 methods Rule-based Optimizer RBO - This technique can be used if the machine has no research regarding the objects called by the statement. This technique is desupported in future releases and is going to be not favoured by Oracle. Expense Based Optimizer CBO - This technique can be used if internal statistics exist. The CBO checks several possible execution plans and chooses the one with the lowest 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 effect. I came across this dilemma while dealing with a media consumer using SAP CRM/BW applications on the top of oracle database level. The total database size was more than 4 tera Bytes. Ive picked up a worst performing SQL for analysis here. A view VBAP_VAPMA is dependant on VBAP and VAPMA tables, VBAP outlined in top wait sections regularly. I could see optimizer_index_cost_adj is favouring catalog scans even when theyre worst performer over FULL dining table scan. Ive done some calculations to prove the point. 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. To compare more, please consider checking out MackayAubin715 - Retrovirus Editor. Dig up more on an affiliated wiki by clicking surfline.com/company/bios/index.cfm. Setting Optimizer_index_cost_adj=100 changes execution plan from index 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 4-9.4 NESTED LOOPS 2 206 313893.8 TABLE ACCESS BY INDEX ROWID SAPR3.VBAP 3 K 174 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 advised Default Value SELECT STATEMENT Optimizer Mode=CHOOSE 2 577409 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.VAPMAZ01 1 3 I will do simple calculations how Oracle is estimating execution prices here. Please be aware these are not specific formulas. Dig up more about more information by navigating to our engaging portfolio. Around Total Table Scan Cost 484,193 Unadjusted Cost listed 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 delivery cost Increase DB_FILE_MULTIBLOCK_READCOUNT to 32 + Reorg of desk, cost of FULL Scan can drop to 82,000 providing 5-fold increase in IO. Price of an Index Scan 149,483 is Adjusted value It is utilizing a non-unique index SAPR3.VBAPZ3 defined on columns MANDT, ZZBU_DIR, ZZBU_EDITION. There are only 160 different values with this list 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 We have set Optimizer_index_cost_adj=10 so real charge 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 = Past Cost + Avg_data_blks_per_key * Clustering_fact / Total Table blks = 149,483 Conclusion We have to let oracle optimizer decide a best way for execution than requiring it to select all the time to indexes. Discover more on an affiliated article directory by going to What is Glycemic? Glycemic Index - Stougaard B.. Adding default value for optimizer_index_cost_adj should be used with up-to-date stats as cost based optmizer is heavily determined by right stats. http://OracleDbaSupport.co.uk can be a of Sagar Patil, an independent oracle guide with an excellent knowledge of the way the Oracle database motor Oracle Applications work together..