Oracle-cost-based-optimizer-effect-of-optimizerindexcostadj-parameter62

Oracle Cost Based Optimizer & Effect of Optimizer_index_cost_adj Parameter

Every time a appropriate SQL statement is processed Oracle needs to determine how exactly to access the required knowledge. This choice could be made using 1 of 2 methods:

Rule Based Optimizer (RBO) - This method can be used if the server has no central statistics regarding the objects called by the record. This process is desupported in future releases and is going to be no more favoured by Oracle.

Price Based Optimizer (CBO) - This process can be used if inner statistics exist. If people need to dig up further about logo, there are lots of resources people could investigate. 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 defined to a wrong value an oracle CBO could have a knock-on influence. While working with a press client using SAP CRM/BW programs at the top of oracle database layer I came across this dilemma. To read additional info, we understand people check out: click here for. The sum total database size was in excess of 4 tera Bytes.

I've picked-up a worst performing SQL for analysis here. A view ''VBAP_VAPMA' is founded on VBAP and VAPMA tables, VBAP listed in top wait portions consistently. I could see optimizer_index_cost_adj is favouring list runs even though they are worst performer over FULL table scan. This pictorial www.surfline.com/company/bios/index.cfm paper has several stirring suggestions for when to mull over it. For different viewpoints, consider checking out: surfline.com. I've 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 10. Setting 'Optimizer_index_cost_adj=100 changes execution plan from index 'VBAP~Z3' to Full 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 17-4 K 312568.2 INDEX RANGE SCAN SAPR3.VBAP~Z3 15 M 100758 INDEX ASSORTMENT SCAN SAPR3.VAPMA~Z01 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 DINING table ACCESS FULL SAPR3.VBAP 3 K 17-4 E 564153 INDEX RANGE SCAN SAPR3.VAPMA~Z01 1 3

I'll do simple calculations on how Oracle is estimating performance costs here. Please note these are not correct formulas.

Around Whole Dining table Check Price : 484,193 Unadjusted

Price here's calculated as 'IO + CPU/1000 + NetIO*1.5' but a straightforward formula 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 performance cost : Increase DB_FILE_MULTIBLOCK_READCOUNT to 32 + Reorg of desk, cost of 'FULL Scan' may drop to 82,000 giving 5-fold increase in IO.

Price of an Index Scan : 149,483 is Adjusted price

It's utilizing a non-unique index 'SAPR3.VBAP~Z3' defined on tips MANDT, ZZBU_DIR, ZZBU_EDITION.

You can find only 160 distinct values on this index out-of 15.9 million lines - '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've set Optimizer_index_cost_adj=10 so real price we set is = 1,188,451*10/100= 118845.1 which is 10% 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 allow oracle optimizer determine a best course for execution than forcing it to choose indexes constantly. Putting default value for 'optimizer_index_cost_adj' should be adopted with up-to-date stats as charge based optmizer is heavily dependent on right stats.

http://OracleDbaSupport.co.uk is a website of Sagar Patil, a completely independent oracle expert with a great understanding of the way the Oracle database motor & Oracle Applications come together..