10053, row size and the magic of 10

A friend of mine asked how CBO computes row size. I believe his question is based on the discussion started here. I was not aware about the formula -- row size = 12 + sum(avg_col_len) + (n - 1) -- mentioned in the book of Jonathan Lewis. Although I bought the book quite some time ago I unfortunately did not have time to read it all.

Table Stats:
 Table: ROW_SIZE  Alias: T1
   #Rows: 1  #Blks:  5  AvgRowLen:  2.00
...
ORDER BY sort
   SORT resource      Sort statistics
     Sort width:         226 Area size:      198656 Max Area size:    39845888
     Degree:               1
     Blocks to Sort:       1 Row size:           13 Total Rows:              1
     Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
     Total IO sort cost: 0      Total CPU sort cost: 7045389
     Total Temp space used: 0

So, I was kinda curious where from that formula comes from and reviewed that chapter 13 of the book. Jonathan tried to come up with some formula basing on his observations of how the sorting works. What are n and 12 here? According to the book (page 372):

"Row size: The optimizer’s estimate of the average size of the rows to be sorted. Allowing for a couple of little adjustments and variations, this is derived from column avg_col_length of view user_tab_columns, and is usually (12 + sum(avg_col_length) + (n – 1)) (where n is the number of columns to be sorted). The fixed value 12 covers the cost of the tree node quite nicely, but the rest of the formula seems inappropriate as we have already seen that our 6 bytes of column data had a 2-byte column overhead and a 4-byte row overhead added. It’s just another spot where the model for calculation doesn’t quite agree with the run-time activity."

Unfortunately, simple tests show that the formula above does not work in most cases i.e. indeed, one could say that "the model for calculation doesn’t quite agree with the run-time activity". Although I'm also not sure that I completely understand some part of that sentence (bold). Even if we start from the beginning -- the value of 12 seems to be incorrect at least for the current Oracle versions. But what is the real formula then? Let's try to guess it.

I'm using 10.2.0.2.0 Oracle. The row_size.sql below generated a huge 10053 trace file. We have only one row in the table that is being updated on every iteration and increased in size from 1 to to 4000 bytes.

DROP TABLE row_size PURGE;
CREATE TABLE row_size(c VARCHAR2(4000));
ALTER SESSION SET tracefile_identifier='row_size' workarea_size_policy=manual sort_area_size=65535;
DECLARE
  l_row                            row_size.c%TYPE;
BEGIN
  DELETE row_size;
  INSERT INTO row_size VALUES ('');
  COMMIT;
  FOR i IN 1..4000
  LOOP
    UPDATE row_size SET c = c || 'A';
    dbms_stats.gather_table_stats(
      ownname => USER
    , tabname => 'ROW_SIZE'
    , no_invalidate => FALSE
    , force => TRUE
    );
    EXECUTE IMMEDIATE 'ALTER SESSION SET events=''10053 TRACE NAME CONTEXT FOREVER, LEVEL 1''';
    EXECUTE IMMEDIATE 'SELECT c FROM row_size t' || TO_CHAR(i, 'FM9999') || ' ORDER BY c' INTO l_row;
    EXECUTE IMMEDIATE 'ALTER SESSION SET events=''10053 TRACE NAME CONTEXT OFF''';
  END LOOP;
END;
/

After the file is generated (i.e. the facts are gathered) we have to analyze it. Basically the idea here is very simple we have to find the average row size and the computed row size and try to find some correlation between them. For that purpose I'm going to employ a perl script. My first and very simple guess was that the row size ($rowsz) is computed basing on average size ($avgrsz) of the sorted values plus something. I decided to compute the something starting from the calculating from 1% to 20% of $avgrsz and checking for the difference ($diff) between $avgrsz and $rowsz. The script also visualizes how a given percent of $avgrsz is close to $diff; I decided that absolute difference here must be about 25 (<= 25 to be precise):

#!/usr/bin/perl

use POSIX;

$avgrsz = -1;
$rowsz = 0;
$i = 0;
$diff = 0;
$pdiff = -1;
$MAX_PERCENT_OF_AVGRSZ = 20;
$MAX_DEV = 25;
$bold = `tput bold`;
$norm = `tput sgr0`;

if ($ARGV[0] eq "-html") {
  shift @ARGV;
  $bold = "<b>";
  $norm = "</b>";
  print "<html><head><title>CBO: Row size computation</title></head><body><pre>"
}

printf("%35s PERCENTS OF AVERAGE ROW SIZE\n", " ");
printf("  AVGSZ ROWSZ DIFF M");
for ($i = 1; $i <= $MAX_PERCENT_OF_AVGRSZ; $i++) {
  printf("%3d%%", $i);
}
printf("\n");

while(<>) {
  if (/AvgRowLen:/) {
    s/AvgRowLen:\s+(\d+\.\d+)/$1/;
    $avgrsz = $1;
  }
  if (/Row size:/ && $avgrsz != -1) {
    s/Row size:\s+(\d+)\s+Total/$1/;
    $rowsz = $1;
    $diff = $rowsz - $avgrsz;
    printf("%s%3.2f %5d %4d %1s",
      ($pdiff != $diff && $pdiff != -1)?"\n":"", $avgrsz,
      $rowsz, $diff, ($pdiff != $diff && $pdiff != -1)?"*":"");
    for ($i = 1; $i <= $MAX_PERCENT_OF_AVGRSZ; $i++) {
      $percent = ceil($avgrsz / 100 * $i);
      printf("%s%4d%s", (abs($percent - $diff) <= $MAX_DEV)?$bold:$norm, $percent, $norm);
    }
    printf("\n");
    $pdiff = ($pdiff != $diff)?$diff:$pdiff;
    $avgrsz = -1;
  }
}

printf("  AVGSZ ROWSZ DIFF M");
for ($i = 1; $i <= $MAX_PERCENT_OF_AVGRSZ; $i++) {
  printf("%3d%%", $i);
}
printf("\n");
printf("%35s PERCENTS OF AVERAGE ROW SIZE\n", " ");

if ($ARGV[1] eq "-html") {
  print "</pre></body>"
}

After the execution of the script one can see that we somehow meet the average around 10%. It's also interesting that if we take any $avgrsz value around 4000, e.g. 3994.00 we can notice that 4404 - (3994.00 + 400) = 10. Let's pick 2413.00: 2665 - (2413.00 + 242) = 10... now let's take 17.00: 29 - (17.00 + 2) = 10...

It's a magic of 10, isn't it? So looks like the formula is: X + ceil(X/10) + 10, where X is the sum of average lengths of the сolumns [key and non-key ones] that are subject of sorting.

To check this out one can put an extra line in in the code:

    printf("\n");
    $pdiff = ($pdiff != $diff)?$diff:$pdiff;
    die "Wrong guess!" if ($avgrsz + ceil($avgrsz / 10) + 10 != $rowsz);
    $avgrsz = -1;

I do not have old versions of Oracle handy but 11g :-) so those who are curious can check what's going on the old ones.

Happy Holidays to Everybody!