Oracle Cost Based Optimizer Effect Of Optimizer Index Cost Adj Parameter

Oracle Cost Based Optimizer & Effect of Optimizer_index_cost_adj Parameter

Summary: Oracle Tuning, Oracle Performance, Oracle Optimizer, Optimizer_index_cost_adj, Orale Dba

Oracle Tuning, Oracle Performance, Oracle Optimizer, Optimizer_index_cost_adj, Orale Dba

Article Body: 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 optimizer_index_cost_adj is set to a wrong value. I came across this issue while working with a media client using SAP CRM/BW applications on top of oracle database layer. The total database size was in excess of tera Bytes.

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 segments consistently. I could see optimizer_index_cost_adj 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", "ZZAD_LINE_STATUS", "ZZCDO", "ZZCDO_P", "ZZKONDM_P" FROM SAPR."VBAP_VAPMA" WHERE "MANDT" = :a AND "AEDAT" > :a AND "AUART" = :a AND "KONDM" = :a AND "VKORG" = :a AND "ZZCDO" >= :a

Current value Optimizer_index_cost_adj is set for . Setting "Optimizer_index_cost_adj= changes execution plan from index "VBAP~Z" to Full table scan.

Optimizer_index_cost_adj= SELECT STATEMENT Optimizer Mode=CHOOSE TABLE ACCESS BY INDEX ROWID SAPR.VAPMA . NESTED LOOPS . TABLE ACCESS BY INDEX ROWID SAPR.VBAP K K . INDEX RANGE SCAN SAPR.VBAP~Z M INDEX RANGE SCAN SAPR.VAPMA~Z Optimizer_index_cost_adj= (Oracle recommended Default Value) SELECT STATEMENT Optimizer Mode=CHOOSE TABLE ACCESS BY INDEX ROWID SAPR.VAPMA NESTED LOOPS TABLE ACCESS FULL SAPR.VBAP K K INDEX RANGE SCAN SAPR.VAPMA~Z

I will do simple calculations on how Oracle is estimating execution costs here. Please note these are not precise formulas.

Approx Full Table Scan Cost : , Unadjusted Cost here is calculated as "IO + CPU/ + NetIO." but a simple formula would be (No of blocks/DB_FILE_MULTIBLOCK_READCOUNT)

(No of blocks/DB_FILE_MULTIBLOCK_READCOUNT)= ,, blocks/ = ,

How to drop execution cost : Increase DB_FILE_MULTIBLOCK_READCOUNT to + Reorg of table , cost of "FULL Scan" will drop to , giving fold increase in IO.

Cost of an Index Scan : , is Adjusted value It is using a non-unique index "SAPR.VBAP~Z" defined on columns MANDT, ZZBU_DIR, ZZBU_EDITION. There are only distinct values on this index out of . million rows - "select MANDT, ZZBU_DIR, ZZBU_EDITION from SAPR.vbap"

Index Range Scan Cost = blevel + (Avg leaf blk per key (num_rows selectivity))= ,, (Actual Value) > than FTS We have set Optimizer_index_cost_adj= so real cost we set is = ,,/= . which is % of actual overhead

Final value of index cost must include efforts for accessing data blocks = Previous Cost + (Avg_data_blks_per_key (Clustering_fact / Total Table blks))= ,

Conclusion: 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 "optimizer_index_cost_adj" must be followed with up-to-date stats as cost based optmizer is heavily dependent on right stats.

http://OracleDbaSupport.co.uk 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.