DB 2 : Cost calculations 1 : Table Scan

Index

Why?
When you supply DB2 with a query DB2 has to make some serious decisions:
– Should indexes be utilized?
– Which indexes are most suitable?
– Which join operation should be used? (hash join/nested loop)
– Is all data needed at once or are the first 10 rows as quick as possible (the remainder comes later) okay?

The optimizer is the part of the DB2 engine which gives answers to all these questions. All possibilities are taken into account to determine an excution-plan for the specific query. To do this the optimizer considers a lot of possibilitiesand give them a weigth. The excution plan with the lowest weight (wich we will call cost) will be chosen as _the_ execution-plan for the query.

What is cost?
The optimizer needs a way to measure how long an operation takes. That can be I/O, that can be a calculation, it all costs time. The longer we have to wait, the more costly an operation is. We want to keep the cost as low as possible. The optimizer will try to find out an execution plan with lowest possible cost.

The total Cost is composed of I/O Cost and CPU Cost.
In the following examples I focus on the IO component of the cost. This is not to bad since I _only_ do Full Table Scan (TBScan)

As soon as we start to hit joins (hash join / nested loop / …) or sorts, the CPU Cost will increase, and I will also look at the CPU component of the cost.

Pages
DB2 is fetching pages from the datafiles!!! It is not fetching single rows, it fetches pages. If DB2 needs one single row it still fetches a whole page from the datafile. In-memory the exact row will be retrieved from the page.
So we should have a focus on pages (not rows) when discussing cost of I/O in a DB2 system.
I can easily imagine tables which are very narrow (a few columns of char or integer), or very width (a few varchar(255) columns).
The number of narrow rows in a page is much bigger compared to the number of width rows on a page. If rou need 1000 rows of a narrow table they may come from only 1 or 2 pages. For a very width table on the other hand 1000 rows may well be stored in 10000 pages. For DB2 the number of pages in respect to I/O is important, not the number of rows.

Demo Table
To play around we need to create some demonstration tables. I’ll just show one here.

drop table cost1;

create table cost1 (n1 int, n2 int, n3 int, t varchar(100));

insert into cost1 
  WITH engine (rownum) AS (
                            SELECT 1 AS rownum 
                            from   sysibm.sysdummy1
                            UNION ALL
                            SELECT rownum + 1 AS rownum
                            FROM   engine
                            where  rownum<10
                          )
  SELECT rownum, 
         round(rownum/1000,0), rownum%1000, 't'
  FROM   engine;

commit;

runstats on table cost1 and indexes all;

Thanks to recursive CTE (Common Table Expressions) I can create a table with an arbitrary number of rows. In the above example I´ll create a table with 10 rows.

For the purpose of this demonstration I actually created a few more tables (see below). After creating and filling the tables I collected statistics on them:

These table do not allready have indexes but that will come in future articles, and it’s my habit to collect basic statistics like this.

Look at some statistics

select cast(tabname as varchar(10)) tabname,
       npages,
       mpages,
       fpages,
       card,
       card/npages rows_per_page
from   syscat.tables
where  lower(tabschema)=lower('db2inst1')
       and lower(tabname) like lower('cost%');


TABNAME    NPAGES    MPAGES    FPAGES    CARD      ROWS_PER_PAGE       
---------- --------- --------- --------- --------- -------------
COST0              0         0         1         0            -1
COST1              1         0         1        10            10
COST2              2         0         2       100            50
COST3              9         0         9      1000           111
COST4             78         0        78     10000           128
COST5            776         0       776    100000           128
COST6           7756         0      7756   1000000           128

Now move on. Let’s see how the optimizer thinks we can get _all_ data from a table. There is more then 1 way to look at explain plans, but for the (quite simple) explain we perform now, the following will suffice:

db2expln -t -g -d tinus -q "select * from cost1"

Optimizer Plan:

    Rows   
  Operator 
    (ID)   
    Cost   
          
    10    
  RETURN  
   ( 1)   
  6.76828 
    |     
    10    
  TBSCAN  
   ( 2)   
  6.76828 
    |     
    10    
 Table:   
 DB2INST1 
 COST1

The above is just the graphical representation of the explain plan. db2expln actually gives you must more information.

-t : The statement is on one line (no separator)
-g : Also give a graphical representation of the execution plan
-d : The database on wich the explain operates
-q : The actual statement we want explained

– As you can see at the very bottom of the graph, the source of the rows is table cost1 which has 10 rows.
– The first operation is a TBSCAN and has a cost of 6.76828
– The first operation (the TBSCAN) has ID 2. In the other parts of the explain plan (which are not show here) this operation is labeled 2.
– The query is expected to return 10 rows and has a cost of 6,76828. The total cost consist of only the cost of the TBSCAN (which is almost completly I/O).

What is shown here that the cost increases if the number of pages increases. However if we look at the cost per page we see a ceratin drop in cost/page.

The cost is almost the same for the table with 1, 2 and 9 pages (around 6,77/page).  Then there is a decrease in the cost to 0,89/page.

At a certain point I need to start to tell about prefetching of pages, but that point is not yet. I’ ll suffice by telling that the prefetch page count is 32. Let’s close in to that 32 pages mark. Create a table with 3900 rows (31 pages) and 4000 rows (32 pages)

The cut-off is 32 pages (the prefetch page count)

Just for good measure, also look at a table with a different width. We’ll make the varchar column a 100 characters width.

insert into cost2_1 
  WITH engine (rownum) AS (
                            SELECT 1 AS rownum 
                            from   sysibm.sysdummy1
                            UNION ALL
                            SELECT rownum + 1 AS rownum
                            FROM   engine
                            where  rownum<10
                          )
  SELECT rownum, 
         round(rownum/1000,0), rownum%1000, rpad('t',100, 'x') 
  FROM   engine;

The text column isn’t 1 character width anymore but 100. The row is much longer, so less rows fit on a page, and thus we need more pages for the same number of rows.

See what the tables look like now:

TABNAME     NPAGES       MPAGES       FPAGES       CARD         ROWS_PER_PAGE       
----------- ------------ ------------ ------------ ------------ -------------
COST2_0                0            0            1            0            -1
COST2_1                1            0            1           10            10
COST2_2                4            0            4          100            25
COST2_3               34            0           34         1000            29
COST2_7              131            0          131         3900            29
COST2_8              134            0          134         4000            29
COST2_4              334            0          334        10000            29
COST2_5             3335            0         3335       100000            29
COST2_6            33349            0        33349      1000000            29

Again, around the 32 pages mark, the cost/page drops dramaticly
And again for the small tablesthe cost is around 6,76 and for the tables with more then 32 pages the cost is around 0,88.

In a next article I would like to see how indices behave with respect to cost.