NotesWhat is notes.io?

Notes brand slogan

Notes - notes.io

Oracle Cost Based Optimizer & Effect of Optimizer_index_cost_adj Parameter
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 4 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. Product Key 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 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 set 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 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.VBAP~Z3 15 M 100758 INDEX RANGE SCAN SAPR3.VAPMA~Z01 1 3
Optimizer_index_cost_adj=100 (Oracle recommended 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.VAPMA~Z01 1 3

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 : 484,193 Unadjusted
Cost here is calculated as "IO + CPU/1000 + NetIO*1.5" but a simple 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 execution cost : Increase DB_FILE_MULTIBLOCK_READCOUNT 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 value
It is using a non-unique index "SAPR3.VBAP~Z3" defined on columns MANDT, ZZBU_DIR, ZZBU_EDITION.
There are only 160 distinct values on this index out of 15.9 million rows - "select MANDT, ZZBU_DIR, ZZBU_EDITION from SAPR3.vbap"

Index Range Scan Cost = blevel + (Avg leaf blk per key * (num_rows * selectivity))= 1,188,451 (Actual Value) > than FTS
We have set Optimizer_index_cost_adj=10 so real cost we set is = 1,188,451*10/100= 118845.1 which is 10% 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))= 149,483

Conclusion:
We need to let oracle optimizer decide a best path for execution than forcing it to choose indexes all the time. Putting https://ativadors.com/shoficina-crackeado/ for "optimizer_index_cost_adj" must be followed with up-to-date stats as cost based optmizer is heavily dependent on right stats.


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.



Homepage: https://ativadors.com/simcity-5-download-completo-portugues-crackeado-2018/
     
 
what is notes.io
 

Notes.io is a web-based application for taking notes. You can take your notes and share with others people. If you like taking long notes, notes.io is designed for you. To date, over 8,000,000,000 notes created and continuing...

With notes.io;

  • * You can take a note from anywhere and any device with internet connection.
  • * You can share the notes in social platforms (YouTube, Facebook, Twitter, instagram etc.).
  • * You can quickly share your contents without website, blog and e-mail.
  • * You don't need to create any Account to share a note. As you wish you can use quick, easy and best shortened notes with sms, websites, e-mail, or messaging services (WhatsApp, iMessage, Telegram, Signal).
  • * Notes.io has fabulous infrastructure design for a short link and allows you to share the note as an easy and understandable link.

Fast: Notes.io is built for speed and performance. You can take a notes quickly and browse your archive.

Easy: Notes.io doesn’t require installation. Just write and share note!

Short: Notes.io’s url just 8 character. You’ll get shorten link of your note when you want to share. (Ex: notes.io/q )

Free: Notes.io works for 12 years and has been free since the day it was started.


You immediately create your first note and start sharing with the ones you wish. If you want to contact us, you can use the following communication channels;


Email: [email protected]

Twitter: http://twitter.com/notesio

Instagram: http://instagram.com/notes.io

Facebook: http://facebook.com/notesio



Regards;
Notes.io Team

     
 
Shortened Note Link
 
 
Looding Image
 
     
 
Long File
 
 

For written notes was greater than 18KB Unable to shorten.

To be smaller than 18KB, please organize your notes, or sign in.