H.Tonguç Yılmaz – Oracle Blog

September 17, 2007

Column retrieval cost in CBO calculation

Filed under: Oracle Concepts and Architecture, Oracle Performance — H.Tonguç Yılmaz @ 1:59 pm

Same database, same table, same column, same data, same statistics but where does this difference come from;
Code Listing 141 – Column retrieval cost in CBO calculation demo

Summary –
for column c1 : elapsed time – 00.07 / cost – 5104 / consistent gets – 18575
for column c2 : elapsed time – 00.10 / cost – 5105 / consistent gets – 26811

Joze Senegacnik in his Optimizing for Performance 2 Day Seminar at Istanbul, Turkcell Academy 23-23/08/2007 mentioned that each subsequently parsed column introduces a cost of 20 cpu cycles regardless if it will be extracted or not;

“The answer is the cost of LIO. A CBO trace (event 10053) shows a difference in CPU cost – additional 20 cycles for the second column. In my presentation “Access Path Optimization” presented at UKOUG, Collaborate06 I have a demo case with a table with 250 columns where the difference between selecting from the first and last column is almost 50% increased elapsed time with no PIO, only LIO.

It is important to know that the CBO, when using the new cost model which includes also the CPU time, is aware of the fact of increased CPU consumption for each column retrieved. In reality the kernel has to parse the row data due to the variable length of columns (either VARCHAR2 or NUMBER) and it adds 20 cycles per row retrieved. For instance: retrieving first column costs 20, retrieving tenth column costs 200 – and kernel has to walk through the row data across all columns to finally reach the tenth column – and this is the difference.”

Additional readings :
Optimizer debug trace event – 10053 trace file
How to migrate to system statistics with DBMS_STATS

2 Comments »

  1. [...] unknown wrote an interesting post today onHere’s a quick excerptSame database, same table, same column, same data, same statistics but where does this difference come from; Code Listing 141 – Column retrieval cost in CBO calculation demo. Summary – for column c1 : elapsed time – 00.07 / cost – 5104 … [...]

    Pingback by Mike’s Musings » Column retrieval cost in CBO calculation — September 17, 2007 @ 3:36 pm |

  2. [...] chapters has important performance tips for the Oracle database application developers, for example column retrieval cost in CBO calculation and the following example on best practices in datatype selection are in my opinion some of the [...]

    Pingback by On Troubleshooting Oracle Performance book by Christian Antognini « H.Tonguç Yılmaz - Oracle Blog — February 22, 2009 @ 9:01 pm |


RSS feed for comments on this post. TrackBack URI

Leave a comment

You must be logged in to post a comment.

Blog at WordPress.com.