{"id":494,"date":"2022-02-22T19:37:24","date_gmt":"2022-02-22T19:37:24","guid":{"rendered":"https:\/\/maboc.nl\/?p=494"},"modified":"2022-02-22T19:37:24","modified_gmt":"2022-02-22T19:37:24","slug":"db2-cost-calculation-3-index-only-revisited","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=494","title":{"rendered":"DB2 : Cost Calculation 3 : index only revisited"},"content":{"rendered":"

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

Why<\/strong>
\nIn the DB2 : Cost Calculation 2 : index only<\/a> post I noticed something upsetting, well, at least it was upsetting to me.<\/p>\n

We looked at the explain plan for looking up 1 value by an index. The index had nlevel=3.
\nSo there is a root-node, some intermediate-nodes and finally finally some leaf-nodes.
\nThat suggests to me that an index scan at least have to do 3 IO’s to get at the leaf-nodes. However…the plan suggests 2:<\/p>\n

\r\nAccess Plan:\r\n-----------\r\n        Total Cost:             13.5334\r\n        Query Degree:           1\r\n\r\n\r\n        Rows \r\n       RETURN\r\n       (   1)\r\n        Cost \r\n         I\/O \r\n         |\r\n          1 \r\n       IXSCAN\r\n       (   2)\r\n       13.5334 \r\n          2 \r\n         |\r\n       100000 \r\n   INDEX: SYSIBM  \r\n SQL220220161847690\r\n         Q1\r\n<\/pre>\n
How on earth can there be only 2 IO’s.
\nCould it be that the optimizer is a bit off?
\nCan we maybe look at what is actually happening?
\nYes we can…with db2caem  (DB2 Capture Activity Event Monitor)<\/p>\n
DB2caem captures what DB2 is actually doing when for instance executing a query. In the background db2caem is doing a lot of stuff you should otherwise be doing manually yourself. No worries for that now \ud83d\ude42 <\/p>\n
Let’s go for it: <\/p>\n
\r\ndb2caem -d tinus -o \/home\/db2inst1\/scripts\/cost\/indscn\/db2caem\/ -st \"select n1 from ind1 where n1=100\"\r\n\r\n      ____________________________________________________________________\r\n\r\n                      _____     D B 2 C A E M     _____\r\n\r\n                DB2 Capture Activity Event Monitor data tool\r\n                              I      B      M\r\n\r\n\r\n          The DB2CAEM Tool is a utility for capturing the activity event\r\n         monitor data with details, section and values, as well as actuals.\r\n      ____________________________________________________________________\r\n\r\n\r\n________________________________________________________________________________\r\n\r\nDBT7041I  \"db2caem\": The db2caem utility is connecting to the following database: \"tinus\".\r\n\r\n\r\nDBT7038I  \"db2caem\": The db2caem utility successfully connected to the following database: \"tinus\".\r\n\r\n\r\nDBT7042I  \"db2caem\": The SQL statement \"select n1 from ind1 where n1=100\" is being issued.\r\n\r\n\r\nDBT7043I  \"db2caem\": The db2caem utility is disconnecting from the following database: \"tinus\".\r\n\r\n\r\nDBT7039I  \"db2caem\": The db2caem utility successfully disconnected from the following database: \"tinus\".\r\n\r\n\r\nDBT7000I  db2caem completed. Output path: \"\/home\/db2inst1\/scripts\/cost\/indscn\/db2caem\/DB2CAEM_2022-02-22-20.24.37.593503\".<\/pre>\n
In the given directory db2caem places multiple output-files. At a later moment I might write a post about them. For now I just look at the file db2caem.exfmt.<\/p>\n
This file looks quite similar to the output you get from formatting explain plans with db2exfmt. (also the name allready suggests so :-))<\/p>\n
I take a look at the graph part:<\/p>\n
Access Plan:\r\n-----------\r\n        Total Cost:             13.5334\r\n        Query Degree:           1\r\n\r\n\r\n        Rows\r\n     Rows Actual\r\n       RETURN\r\n       (   1)\r\n        Cost\r\n         I\/O\r\n         |\r\n          1\r\n          1\r\n       IXSCAN\r\n       (   2)\r\n       13.5334\r\n         NA\r\n         |\r\n         -1\r\n         NA\r\n   INDEX: SYSIBM\r\n SQL220220161847690\r\n         Q1\r\n\r\n<\/pre>\n
Uhmmmm I can see that I now also have actual rows versus estimated rows. But…no I\/O information. This did not help at all.<\/p>\n
Now, when I scroll down to bottom of the file, I see:<\/p>\n
Runtime statistics for objects Used in Access Plan:\r\n-----------------------------------------------------\r\n\r\n        Schema: SYSIBM\r\n        Name:   SQL220220161847690\r\n        Type:   Index\r\n\r\n                        Member 0\r\n                        ---------\r\n\r\n                                Metrics\r\n                                --------------------\r\n                                object_index_l_reads:3\r\n                                object_index_lbp_pages_found:3\r\n                                object_index_gbp_indep_pages_found_in_lbp:3\r\n<\/pre>\n
Ah…information about the actual I\/O’s performed.<\/p>\n
I now see 3 (logical) I\/O’s for the index, and that is exactly what I was aiming for!<\/p>\n
My initial problem is solved, but I now have a small trust-issue with the optimizer \ud83d\ude41
\nMaybe we can get rid of this trust issue when this series evolve (I really do not know right now)<\/p>\n","protected":false},"excerpt":{"rendered":"
Index Why In the DB2 : Cost Calculation 2 : index only post I noticed something upsetting, well, at least it was upsetting to me. We looked at the explain plan for looking up 1 value by an index. The index had nlevel=3. So there is a root-node, some intermediate-nodes and finally finally some leaf-nodes. […]<\/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,79,49],"tags":[82,65,80,14],"class_list":["post-494","post","type-post","status-publish","format-standard","hentry","category-cost","category-db2","category-explain","category-sql","tag-cost","tag-db2","tag-explain","tag-sql"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/494","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=494"}],"version-history":[{"count":6,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/494\/revisions"}],"predecessor-version":[{"id":503,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/494\/revisions\/503"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=494"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=494"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=494"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}