{"id":2492,"date":"2019-04-14T15:54:20","date_gmt":"2019-04-14T15:54:20","guid":{"rendered":"https:\/\/nenadnoveljic.com\/blog\/?p=2492"},"modified":"2019-04-17T05:10:54","modified_gmt":"2019-04-17T05:10:54","slug":"join-permutations-heatmaps","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/","title":{"rendered":"Join Permutations Heatmaps"},"content":{"rendered":"

There are n! possible ways to order n tables in a join. Of course, the optimizer won’t cost all of the permutations to find the best plan. It will rather limit its search space in order to produce a good enough plan within a reasonable amount of time.<\/p>\n

The considered permutations are tracked in the CBO trace, like for example:<\/p>\n

...\nJoin order[3]:  T1[T1]#0  T2[T2]#1  T3[T3]#2  T4[T4]#3  T5[T5]#4  T6[T6]#5  T7[T7]#6  T8[T8]#8  T8[T8]#7  T10[T10]#9\n...<\/code><\/pre>\n
Generally, the tables with lower cardinality tend to be joined first (“Considering cardinality-based initial join order.”)<\/p>\n
I’d like to share the Python script<\/a> for aggregating and visualizing the number of times a table was considered on each position in the join. Notice that the scripts relies on some non-default Python modules: pandas, seaborn and numpy.<\/p>\n
I wrote it, at first, for experimental purposes. But then it turned out to be useful for explaining why sometimes a plan with a lower cost wasn’t chosen. In such case, you can generate the heatmap and check whether the leading tables from the better plan were even considered as leading tables in the suboptimal plan. By the way, that’s exactly what can happen with some query transformations.<\/p>\n
To demonstrate the usage, I’ll create 10 tables – t1 having the lowest cardinality and t10 the highest.<\/p>\n
declare\n  i integer ;\nbegin\n  for i in 1..10\n  loop\n    execute immediate\n      'create table t' || i ||\n      ' as select level n from dual connect by level <= ' || power( 4, i) \n  ;\n  end loop ;\nend ;\n\/<\/code><\/pre>\n
Then I’ll generate the CBO trace for the cartesian join of these 10 tables:<\/p>\n
alter session set tracefile_identifier = 'JOIN_ORDER' ;\n\nALTER SESSION SET EVENTS='10053 trace name context forever, level 1' ;\n\nexplain plan for select t10.* from t1,t2,t3,t4,t5,t6,t7,t8,t8,t10 ;\n\nALTER SESSION SET EVENTS '10053 trace name context off';<\/code><\/pre>\n
The simplest way to produce the heatmap is as follows:<\/p>\n
python join_permutations_heatmaps.py CBO_trace_file<\/code><\/pre>\n
\"\"<\/a><\/p>\n
By the way, in the graph above, we can clearly see that the tables with fewer rows are the ones mainly considered for the leading positions.<\/p>\n
If you’re an “ASCII type of person” you can specify the –text parameter to get the output in the text format:<\/p>\n
python join_permutations_heatmaps.py --text CBO_trace_file\n\n            1     2     3    4    5    6    7    8    9    10\nT1[T1]    1031    62     0    0    0    0   12   16   12   22\nT2[T2]      62  1031     0    0    0    0   14   16   16   16\nT3[T3]       0     0  1093    0    0    0   24   12   18    8\nT4[T4]       0     0     0  813  120   68   36   40   39   39\nT5[T5]       0     0     0  280  213  162  126  126  124  124\nT6[T6]       0     0     0    0  302  232  168  153  150  150\nT7[T7]       0     0     0   62  160  189  186  186  186  186\nT8[T8]      31    31    62    0  240  336  395  404  406  405\nT8[T8]      31    31    62    0  240  336  395  404  406  405\nT10[T10]    31    31     0    0  120  168  194  202  204  205<\/code><\/pre>\n
Last but not least, complex queries may contain many query blocks, each of which is costed separately. By default, the heatmaps will be generated for all of them. However, you can limit the scope by specifiying a query block to focus on: –qb QB.<\/p>\n","protected":false},"excerpt":{"rendered":"
Generating heatmaps for join permutations using Python Continue Reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[11,5],"tags":[],"class_list":["post-2492","post","type-post","status-publish","format-standard","hentry","category-cost-based-optimizer","category-oracle"],"yoast_head":"\nJoin Permutations Heatmaps - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"Generating heatmaps for join permutations\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Join Permutations Heatmaps - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"Generating heatmaps for join permutations\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-14T15:54:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-04-17T05:10:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2019\/04\/heatmap-300x210.png\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Join Permutations Heatmaps\",\"datePublished\":\"2019-04-14T15:54:20+00:00\",\"dateModified\":\"2019-04-17T05:10:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/\"},\"wordCount\":324,\"commentCount\":2,\"image\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/04\\\/heatmap-300x210.png\",\"articleSection\":[\"cost based optimizer\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/\",\"name\":\"Join Permutations Heatmaps - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/04\\\/heatmap-300x210.png\",\"datePublished\":\"2019-04-14T15:54:20+00:00\",\"dateModified\":\"2019-04-17T05:10:54+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"Generating heatmaps for join permutations\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/#primaryimage\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/04\\\/heatmap.png\",\"contentUrl\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/04\\\/heatmap.png\",\"width\":1362,\"height\":952},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/join-permutations-heatmaps\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Join Permutations Heatmaps\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Join Permutations Heatmaps - All-round Database Topics","description":"Generating heatmaps for join permutations","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/","og_locale":"en_US","og_type":"article","og_title":"Join Permutations Heatmaps - All-round Database Topics","og_description":"Generating heatmaps for join permutations","og_url":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/","og_site_name":"All-round Database Topics","article_published_time":"2019-04-14T15:54:20+00:00","article_modified_time":"2019-04-17T05:10:54+00:00","og_image":[{"url":"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2019\/04\/heatmap-300x210.png","type":"","width":"","height":""}],"author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Join Permutations Heatmaps","datePublished":"2019-04-14T15:54:20+00:00","dateModified":"2019-04-17T05:10:54+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/"},"wordCount":324,"commentCount":2,"image":{"@id":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/#primaryimage"},"thumbnailUrl":"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2019\/04\/heatmap-300x210.png","articleSection":["cost based optimizer","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/","url":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/","name":"Join Permutations Heatmaps - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/#primaryimage"},"image":{"@id":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/#primaryimage"},"thumbnailUrl":"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2019\/04\/heatmap-300x210.png","datePublished":"2019-04-14T15:54:20+00:00","dateModified":"2019-04-17T05:10:54+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"Generating heatmaps for join permutations","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/#primaryimage","url":"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2019\/04\/heatmap.png","contentUrl":"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2019\/04\/heatmap.png","width":1362,"height":952},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/join-permutations-heatmaps\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Join Permutations Heatmaps"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2492","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=2492"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2492\/revisions"}],"predecessor-version":[{"id":2506,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2492\/revisions\/2506"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=2492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=2492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=2492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}