Column retrieval cost in CBO calculation

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

Advertisement