Extract(Read) > Transform(Test&Apply) > Load(Learn) 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

About these ads

3 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 | Reply

  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 | Reply

  3. [...] of a column in a table could affect performance, and that is why the optimizer generates a different CPU_COST component for each column in a table.  I did not have an independent test case to verify the results that [...]

    Pingback by Column Order in a Table – Does it Matter? 1 « Charles Hooper's Oracle Notes — May 22, 2010 @ 4:00 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: