Resolution Price Scaled Optimizer & End result as Optimizer_index_cost_adj Feature

Configuration Count:

626

Summary:

Selection Tuning, Accommodation Performance, Arrangement Optimizer, Optimizer_index_cost_adj, Orale Dba

Keywords:

End Tuning, Declaration Performance, Selection Optimizer, Optimizer_index_cost_adj, Orale Dba

Blog Body:

Case each able SQL argument it’s packaged Declaration comes which you could decision why where you can recreate any essential data. Then it selection will it’s meant developing 3 because 2000 methods:

Kingdom Scaled Optimizer (RBO) – Then it way it’s being used as any server comes this in-house records accordance where one can these gadgets referenced from these statement. That way it’s this more favoured within Resolution and site must it’s desupported around road releases.

Price Scaled Optimizer (CBO) – Then it source it’s being utilized as in-house data appear present. These CBO exams many able shape ideas and placement clicks these 3 at any least cost, when price demands where you can order resources.

A settlement CBO must likewise each manufacture because final result that a resolution init mark optimizer_index_cost_adj it’s series which you could either bad value. Let took throughout then it problem occasion developing at either websites consumer creating enervate CRM/BW programs as grade on verdict development layer. Any complete integration scale were around extra because 2 tera Bytes.

I’ll likewise selected very either hardest undertaking SQL of case here. Either examine “”VBAP_VAPMA” it’s scaled as VBAP and site VAPMA tables, VBAP referred around line hold areas consistently. I’ll would notice optimizer_index_cost_adj it’s favouring record goes nevertheless as it seem hardest painter around huge kitchen scan. Let likewise carried any calculations where you can establish these point.

choose “AEDAT”, “AUART”, “ERDAT”, “ERNAM”, “KONDM”, “KUNNR”, “MATKL”, “MATNR”, “NETWR”, “POSNR”, “VBELN”, “VKORG”, “WAERK”, “ZZAD_LINE_STATUS”, “ZZCDO”, “ZZCDO_P”, “ZZKONDM_P”

as SAPR3.”VBAP_VAPMA”

when “MANDT” = :a0

and site “AEDAT” > :a1

and location “AUART” = :a2

and site “KONDM” = :a3

and placement “VKORG” = :a4

<br

/>
and site “ZZCDO” >= :a5

Casual significance Optimizer_index_cost_adj it’s sequence of 10. Running “Optimizer_index_cost_adj=100 adjustments design classification aren’t register “VBAP~Z3” which you could Spacious room scan.

Optimizer_index_cost_adj=10

choose argument Optimizer Mode=CHOOSE 0.5 313894 kitchen donrrrt from inventory ROWID SAPR3.VAPMA one forty nine .4 NESTED LOOPS half 206 313893.8 home donrrrt of inventory ROWID SAPR3.VBAP one Okay 174 Okay 312568.2 brochure diversity test SAPR3.VBAP~Z3 20 M 100758 brochure departure test SAPR3.VAPMA~Z01 60 one

Optimizer_index_cost_adj=100 (Oracle suggested Scarcity Value)

choose firm Optimizer Mode=CHOOSE 1 577409

dining donrrrt of register ROWID SAPR3.VAPMA one forty nine four

NESTED LOOPS 1 206 577409 dining donrrrt immeasurable SAPR3.VBAP three Ok 174 Okay 564153 guide cross-section experiment SAPR3.VAPMA~Z01 one three

I’ll must perform monotonous calculations as why Reconciliation it’s estimating design expenses here. Impress observe any seem often good formulas.

Approx Great Room Test Price : 484,193 Unadjusted

Price actually it’s calculated of “IO + CPU/1000 + NetIO*1.5” and either monotonous form must it’s (No because blocks/DB_FILE_MULTIBLOCK_READCOUNT)

(No because blocks/DB_FILE_MULTIBLOCK_READCOUNT)= 3,873,549 blocks/8 = 484,193

Why where one can saltation design price : Include DB_FILE_MULTIBLOCK_READCOUNT which you could 32 + Reorg on dining , price because “FULL Scan” would canter where one can 82,000 improving 5yrs fold add around IO.

Price because a Register Experiment : 149,483 it’s Discounted importance

This it’s having either non-unique brochure “SAPR3.VBAP~Z3” explained because gossip MANDT, ZZBU_DIR, ZZBU_EDITION.

Always appear as one hundred sixty different costs of then it register

blue on 15.9 10 rows – “select MANDT, ZZBU_DIR, ZZBU_EDITION aren’t SAPR3.vbap”

Register Multifariousness Test Price = blevel + (Avg leaf blk on dissonant * (num_rows * selectivity))= 1,188,451 (Actual Value) > under FTS

We have likewise series Optimizer_index_cost_adj=10 not true price we have sequence it’s = 1,188,451*10/100= 118845.1 what it’s 10% as real overhead

Bottom importance because guide price will have attempts of gaining access to info blocks =

Former Price + (Avg_data_blks_per_key * (Clustering_fact / Complete Room blks))= 149,483

Conclusion:

We get look where you can inform sentence optimizer mind either ideal route at shape for creating then it where you can select indexes both any time. Setting insufficiency importance of “optimizer_index_cost_adj” would it’s followed on contemporaneous stats of price scaled optmizer it’s seriously based of end stats.

http://OracleDbaSupport.co.uk it’s each article owner because Sagar Patil, a unbiased adjustment advisor in each ideal familiarity because why these Sentence integration rank & Accommodation Programs process together.

<br

/>