{"id":451,"date":"2022-02-11T21:39:35","date_gmt":"2022-02-11T21:39:35","guid":{"rendered":"https:\/\/maboc.nl\/?p=451"},"modified":"2022-02-22T18:58:07","modified_gmt":"2022-02-22T18:58:07","slug":"db-2-cost-calculations-1-table-scan","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=451","title":{"rendered":"DB 2 : Cost calculations 1 : Table Scan"},"content":{"rendered":"

Index<\/a><\/p>\n

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

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.<\/p>\n

What is cost?<\/strong>
\nThe 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.<\/p>\n

The total Cost is composed of I\/O Cost and CPU Cost.
\nIn 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)<\/p>\n

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.<\/p>\n

Pages<\/strong>
\nDB2 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.
\nSo we should have a focus on pages (not rows) when discussing cost of I\/O in a DB2 system.
\nI can easily imagine tables which are very narrow (a few columns of char or integer), or very width (a few varchar(255) columns).
\nThe 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.<\/p>\n

Demo Table<\/strong>
\nTo play around we need to create some demonstration tables. I’ll just show one here.<\/p>\n

drop table cost1;\r\n\r\ncreate table cost1 (n1 int, n2 int, n3 int, t varchar(100));\r\n\r\ninsert into cost1 \r\n  WITH engine (rownum) AS (\r\n                            SELECT 1 AS rownum \r\n                            from   sysibm.sysdummy1\r\n                            UNION ALL\r\n                            SELECT rownum + 1 AS rownum\r\n                            FROM   engine\r\n                            where  rownum<10\r\n                          )\r\n  SELECT rownum, \r\n         round(rownum\/1000,0), rownum%1000, 't'\r\n  FROM   engine;\r\n\r\ncommit;\r\n\r\nrunstats on table cost1 and indexes all;\r\n<\/pre>\n
Thanks to recursive CTE (Common Table Expressions) I can create a table with an arbitrary number of rows. In the above example I\u00b4ll create a table with 10 rows.<\/p>\n
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:<\/p>\n
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.<\/p>\n
Look at some statistics<\/p>\n
select cast(tabname as varchar(10)) tabname,\r\n       npages,\r\n       mpages,\r\n       fpages,\r\n       card,\r\n       card\/npages rows_per_page\r\nfrom   syscat.tables\r\nwhere  lower(tabschema)=lower('db2inst1')\r\n       and lower(tabname) like lower('cost%');\r\n\r\n\r\nTABNAME    NPAGES    MPAGES    FPAGES    CARD      ROWS_PER_PAGE       \r\n---------- --------- --------- --------- --------- -------------\r\nCOST0              0         0         1         0            -1\r\nCOST1              1         0         1        10            10\r\nCOST2              2         0         2       100            50\r\nCOST3              9         0         9      1000           111\r\nCOST4             78         0        78     10000           128\r\nCOST5            776         0       776    100000           128\r\nCOST6           7756         0      7756   1000000           128\r\n<\/pre>\n
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:<\/p>\n
db2expln -t -g -d tinus -q \"select * from cost1\"\r\n\r\nOptimizer Plan:\r\n\r\n    Rows   \r\n  Operator \r\n    (ID)   \r\n    Cost   \r\n          \r\n    10    \r\n  RETURN  \r\n   ( 1)   \r\n  6.76828 \r\n    |     \r\n    10    \r\n  TBSCAN  \r\n   ( 2)   \r\n  6.76828 \r\n    |     \r\n    10    \r\n Table:   \r\n DB2INST1 \r\n COST1<\/pre>\n
The above is just the graphical representation of the explain plan. db2expln actually gives you must more information.<\/p>\n
-t : The statement is on one line (no separator)
\n-g : Also give a graphical representation of the execution plan
\n-d : The database on wich the explain operates
\n-q : The actual statement we want explained<\/p>\n
– As you can see at the very bottom of the graph, the source of the rows is table cost1 which has 10 rows.
\n– The first operation is a TBSCAN and has a cost of 6.76828
\n– 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.
\n– 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).<\/p>\n\n\n\n\n\n\n\n
Table<\/strong><\/td>\nCost0<\/strong><\/td>\nCost1<\/strong><\/td>\nCost2<\/strong><\/td>\nCost3<\/strong><\/td>\nCost4<\/strong><\/td>\nCost5<\/strong><\/td>\nCost6<\/strong><\/td>\n<\/tr>\n
Rows<\/strong><\/td>\n0<\/td>\n10<\/td>\n100<\/td>\n1000<\/td>\n10000<\/td>\n100000<\/td>\n1000000<\/td>\n<\/tr>\n
Pages<\/strong><\/td>\n1<\/td>\n1<\/td>\n2<\/td>\n9<\/td>\n78<\/td>\n776<\/td>\n7756<\/td>\n<\/tr>\n
Cost Table Scan<\/strong><\/td>\n6.76747<\/td>\n6.76828<\/td>\n13.5412<\/td>\n60.9737<\/td>\n71.2083<\/td>\n691.727<\/td>\n6917.09<\/td>\n<\/tr>\n
Cost\/Page<\/strong><\/td>\n6,76747<\/td>\n6,76828<\/td>\n6,7706<\/td>\n6,7749<\/td>\n0,9130<\/td>\n0,8914<\/td>\n0,8918<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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.<\/p>\n
The cost is almost the same for the table with 1, 2 and 9 pages (around 6,77\/page).\u00a0 Then there is a decrease in the cost to 0,89\/page.<\/p>\n
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)<\/p>\n\n\n\n\n\n\n\n
Table<\/strong><\/td>\nCost7<\/strong><\/td>\nCost8<\/strong><\/td>\n<\/tr>\n
Rows<\/strong><\/td>\n3900<\/td>\n4000<\/td>\n<\/tr>\n
Pages<\/strong><\/td>\n31<\/td>\n32<\/td>\n<\/tr>\n
Cost Table Scan<\/strong><\/td>\n210,053<\/td>\n28,5167<\/td>\n<\/tr>\n
Cost\/Page<\/strong><\/td>\n6,7759<\/td>\n0,8911<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The cut-off is 32 pages (the prefetch page count)<\/p>\n
Just for good measure, also look at a table with a different width. We’ll make the varchar column a 100 characters width.<\/p>\n
insert into cost2_1 \r\n  WITH engine (rownum) AS (\r\n                            SELECT 1 AS rownum \r\n                            from   sysibm.sysdummy1\r\n                            UNION ALL\r\n                            SELECT rownum + 1 AS rownum\r\n                            FROM   engine\r\n                            where  rownum<10\r\n                          )\r\n  SELECT rownum, \r\n         round(rownum\/1000,0), rownum%1000, rpad('t',100, 'x') \r\n  FROM   engine;\r\n<\/pre>\n
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.<\/p>\n
See what the tables look like now:<\/p>\n
TABNAME     NPAGES       MPAGES       FPAGES       CARD         ROWS_PER_PAGE       \r\n----------- ------------ ------------ ------------ ------------ -------------\r\nCOST2_0                0            0            1            0            -1\r\nCOST2_1                1            0            1           10            10\r\nCOST2_2                4            0            4          100            25\r\nCOST2_3               34            0           34         1000            29\r\nCOST2_7              131            0          131         3900            29\r\nCOST2_8              134            0          134         4000            29\r\nCOST2_4              334            0          334        10000            29\r\nCOST2_5             3335            0         3335       100000            29\r\nCOST2_6            33349            0        33349      1000000            29\r\n<\/pre>\n\n\n\n\n\n\n\n
Table<\/strong><\/td>\nCost2_0<\/strong><\/td>\nCost2_1<\/strong><\/td>\nCost2_2<\/strong><\/td>\nCost2_3<\/strong><\/td>\nCost2_4<\/strong><\/td>\nCost2_5<\/strong><\/td>\nCost2_6<\/strong><\/td>\n<\/tr>\n
Rows<\/strong><\/td>\n0<\/td>\n10<\/td>\n100<\/td>\n1000<\/td>\n10000<\/td>\n100000<\/td>\n1000000<\/td>\n<\/tr>\n
Pages<\/strong><\/td>\n1<\/td>\n1<\/td>\n4<\/td>\n34<\/td>\n334<\/td>\n3335<\/td>\n33349<\/td>\n<\/tr>\n
Cost Table Scan<\/strong><\/td>\n6,76747<\/td>\n6,76828<\/td>\n27,0724<\/td>\n35,0786<\/td>\n296,723<\/td>\n2946,83<\/td>\n29461,6<\/td>\n<\/tr>\n
Cost\/Page<\/strong><\/td>\n6,76747<\/td>\n6,76828<\/td>\n6,7681<\/td>\n1,032<\/td>\n0,8884<\/td>\n0,8836<\/td>\n0,8834<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Again, around the 32 pages mark, the cost\/page drops dramaticly
\nAnd 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.<\/p>\n
In a next article I would like to see how indices behave with respect to cost.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[81,64],"tags":[82,65,14],"class_list":["post-451","post","type-post","status-publish","format-standard","hentry","category-cost","category-db2","tag-cost","tag-db2","tag-sql"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/451","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=451"}],"version-history":[{"count":24,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/451\/revisions"}],"predecessor-version":[{"id":497,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/451\/revisions\/497"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=451"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=451"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=451"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}