Whenever A Valid SQL Statement Is Processed Oracle Has To Decide How To Retrieve The Necessary Data. This Decision Can Be Made Using One Of Two Methods: Rule Based Optimizer (RBO) - This Method Is Used If The Server Has No Internal Statistics Relating To The Objects Referenced By The Statement. This Method Is No Longer Favoured By Oracle And Will Be Desupported In Future Releases.Cost Based Optimizer (CBO) - This Method Is Used If Internal Statistics Are Present. The CBO Checks Several Possible Execution Plans And Selects The One With The Lowest Cost, Where Cost Relates To System Resources.An Oracle CBO Will Have A Knock On Effect If An Oracle Init Parameter Optimizerindexcostadj Is Set To A Wrong Value. I Came Across This Issue While Working With A Media Client Using SAP CRMBW Applications On Top Of Oracle Database Layer. The Total Database Size Was In Excess Of 4 Tera Bytes.I Have Picked Up A Worst Performing SQL For Analysis Here. A View ""VBAPVAPMA" Is Based On VBAP And VAPMA Tables, VBAP Listed In Top Wait Segments Consistently. I Could See Optimizerindexcostadj Is Favouring Index Scans Even If They Are Worst Performer Over FULL Table Scan. I Have Done Some Calculations To Prove The Point.SELECT "AEDAT", "AUART", "ERDAT", "ERNAM", "KONDM", "KUNNR", "MATKL", "MATNR", "NETWR", "POSNR", "VBELN", "VKORG", "WAERK", "ZZADLINESTATUS", "ZZCDO", "ZZCDOP", "ZZKONDMP"FROM SAPR3."VBAPVAPMA"WHERE "MANDT" :a0AND "AEDAT" :a1AND "AUART" :a2AND "KONDM" :a3AND "VKORG" :a4AND "ZZCDO" :a5Current Value Optimizerindexcostadj Is Set For 10. Setting "Optimizerindexcostadj100 Changes Execution Plan From Index "VBAPZ3" To Full Table Scan.Optimizerindexcostadj10SELECT STATEMENT Optimizer ModeCHOOSE 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 K 312568.2 INDEX RANGE SCAN SAPR3.VBAPZ3 15 M 100758 INDEX RANGE SCAN SAPR3.VAPMAZ01 1 3Optimizerindexcostadj100 (Oracle Recommended Default Value)SELECT STATEMENT Optimizer ModeCHOOSE 2 577409TABLE ACCESS BY INDEX ROWID SAPR3.VAPMA 1 49 4NESTED LOOPS 2 206 577409 TABLE ACCESS FULL SAPR3.VBAP 3 K 174 K 564153 INDEX RANGE SCAN SAPR3.VAPMAZ01 1 3I Will Do Simple Calculations On How Oracle Is Estimating Execution Costs Here. Please Note These Are Not Precise Formulas.Approx Full Table Scan Cost : 484,193 UnadjustedCost Here Is Calculated As "IO CPU1000 NetIO1.5" But A Simple Formula Would Be (No Of BlocksDBFILEMULTIBLOCKREADCOUNT)(No Of BlocksDBFILEMULTIBLOCKREADCOUNT) 3,873,549 Blocks8 484,193How To Drop Execution Cost : Increase DBFILEMULTIBLOCKREADCOUNT To 32 Reorg Of Table , Cost Of "FULL Scan" Will Drop To 82,000 Giving 5 Fold Increase In IO.Cost Of An Index Scan : 149,483 Is Adjusted ValueIt Is Using A Non-unique Index "SAPR3.VBAPZ3" Defined On Columns MANDT, ZZBUDIR, ZZBUEDITION.There Are Only 160 Distinct Values On This Index Out Of 15.9 Million Rows - "select MANDT, ZZBUDIR, ZZBUEDITION From SAPR3.vbap"Index Range Scan Cost Blevel (Avg Leaf Blk Per Key (numrows Selectivity)) 1,188,451 (Actual Value) Than FTSWe Have Set Optimizerindexcostadj10 So Real Cost We Set Is 1,188,45110100 118845.1 Which Is 10 Of Actual OverheadFinal Value Of Index Cost Must Include Efforts For Accessing Data Blocks Previous Cost (Avgdatablksperkey (Clusteringfact Total Table Blks)) 149,483Conclusion:We Need To Let Oracle Optimizer Decide A Best Path For Execution Than Forcing It To Choose Indexes All The Time. Putting Default Value For "optimizerindexcostadj" Must Be Followed With Up-to-date Stats As Cost Based Optmizer Is Heavily Dependent On Right Stats.a Href"http:OracleDbaSupport.co.uk"http:OracleDbaSupport.co.uka Is A Blog Site Of Sagar Patil, An Independent Oracle Consultant With A Great Understanding Of How The Oracle Database Engine Oracle Applications Work Together.
