{"id":2889,"date":"2019-09-01T16:43:15","date_gmt":"2019-09-01T16:43:15","guid":{"rendered":"https:\/\/nenadnoveljic.com\/blog\/?p=2889"},"modified":"2019-09-01T16:43:18","modified_gmt":"2019-09-01T16:43:18","slug":"cost-propagation-errors","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/","title":{"rendered":"Cost Propagation Errors"},"content":{"rendered":"

In my previous blog post<\/a> I described an optimizer bug where the cost of an operation in the execution plan was massively underestimated. This is how the execution plan looked like:<\/p>\n

-----------------------------------------------------------------------------------------\n| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |\n-----------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                |       |       |       |    48 (100)|          |\n|   1 |  NESTED LOOPS OUTER             |       |     1 |    29 |    48   (5)| 00:00:01 |\n|*  2 |   HASH JOIN                     |       |     1 |    16 |    48   (5)| 00:00:01 |\n|   3 |    VIEW                         |       |     1 |    13 |     2   (0)| 00:00:01 |\n|*  4 |     CONNECT BY WITHOUT FILTERING|       |       |       |            |          |\n|   5 |      TABLE ACCESS FULL          | T3    |     1 |    26 |     2   (0)| 00:00:01 |\n|   6 |    TABLE ACCESS FULL            | T1    |  1000K|  2929K|    45   (3)| 00:00:01 |\n|   7<\/span> |   VIEW PUSHED PREDICATE         |       |     1 |    13 |     0<\/span>   (0)|          |\n|   8 |    NESTED LOOPS                 |       |     1 |    16 |  1613   (1)| 00:00:01 |\n|*  9 |     INDEX UNIQUE SCAN           | PK_T2 |     1 |    13 |     0   (0)|          |\n|* 10 |     TABLE ACCESS FULL           | T4    |     1 |     3 |  1613   (1)| 00:00:01 |\n-----------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - access(\"T1\".\"N1\"=\"V2\".\"N1\")\n   4 - access(\"N2\"=PRIOR NULL)\n   9 - access(\"T2\".\"N1\"=\"T1\".\"N1\")\n  10 - filter((\"T4\".\"N1\"=\"T2\".\"N1\" AND \"T4\".\"N1\"=\"T1\".\"N1\"))<\/code><\/pre>\n
Apart from a ridiculous elapsed time, the problem revealed itself in the execution plan – the operation 7<\/span> had a lower cost than its child operation 8. That shouldn’t have happened, because the parent’s cost should also entail the costs of their children.<\/p>\n
But why not scan all execution plans in the AWR repository for such anomalies?<\/p>\n
The following query does exactly that – it simply identifies the operations with a lower cost than their children:<\/p>\n
set linesize 200\n\ncolumn P_OP format A30\ncolumn C_OP format A30\n\nselect\n    c.sql_id,c.plan_hash_value, round(max(s.elapsed_time_total)\/1e6) seconds, \n    p.id p_id, c.id c_id, p.cost p_cost, c.cost c_cost, c.cost - p.cost diff,\n    p.operation || ' ' || p.options p_op,\n    c.operation || ' ' || c.options c_op \n  from dba_hist_sql_plan c, dba_hist_sql_plan p, dba_hist_sqlstat s \n  where\n    c.dbid = ( select dbid from v$database ) and\n    c.dbid = p.dbid and \n    c.sql_id = p.sql_id and c.plan_hash_value = p.plan_hash_value and \n    c.parent_id = p.id and p.cost < c.cost \n    and c.sql_id = s.sql_id(+) and c.plan_hash_value = s.plan_hash_value(+)\n    and c.dbid = s.dbid\n  group by c.sql_id,c.plan_hash_value, c.id, c.depth, c.cost, c.parent_id,\n    c.operation, c.options, p.id, p.cost, p.operation, p.options\n  order by  \n--    max(s.elapsed_time_total) nulls first,\n    c.cost - p.cost, \n    c.depth\n;<\/code><\/pre>\n
Find below a sample output:<\/p>\n
...\nSQL_ID        PLAN_HASH_VALUE    SECONDS       P_ID       C_ID     P_COST     C_COST       DIFF P_OP                           C_OP\n------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------\n0dcpv2ccqsmhd       853671621        686          7          8       6019      22164      16145 VIEW                           SORT ORDER BY\n0dcpv2ccqsmhd      1815802073        176          7          8       6021      22172      16151 VIEW                           SORT ORDER BY\n0dcpv2ccqsmhd      1523297245        105          7          8       6241      22983      16742 VIEW                           SORT ORDER BY\n0dcpv2ccqsmhd      2908874411          2          7          8       6751      25024      18273 VIEW                           SORT ORDER BY\n390nnp8kb6y8d       150295021      55354         56         57          3      21306      21303 VIEW PUSHED PREDICATE          NESTED LOOPS\n1dpscuz0dpx17      1825909960          2          7          8       8252      30792      22540 VIEW                           SORT ORDER BY\n1dpscuz0dpx17      1716121194          0          7          8       8305      30988      22683 VIEW                           SORT ORDER BY\n1z4k7x6bybabx      3618098506        756         55         56          2      22756      22754 VIEW PUSHED PREDICATE          HASH JOIN OUTER\nfbrajakyvkb2p      3618098506        562         55         56          2      22756      22754 VIEW PUSHED PREDICATE          HASH JOIN OUTER\n0dcpv2ccqsmhd      2979690613          2          7          8      18390      67728      49338 VIEW                           SORT ORDER BY\n1dpscuz0dpx17      3226924111          6          6          7      18490      69448      50958 VIEW                           SORT ORDER BY\n1dpscuz0dpx17      3740554088         22          6          7      18508      69516      51008 VIEW                           SORT ORDER BY\n2g8gx4ja5fm7s      2606037132         41          7          8     448305     522853      74548 SORT JOIN                      VIEW\n20uhd3mr2kfvd      2591703555        222         21         27       7522     679670     672148 HASH JOIN                      VIEW\n9c1hczh7hsxf3      1171142012        226         20         26       7522     679688     672166 HASH JOIN                      VIEW\n44y663h05c264       338860802        153          2          3        513    1829850    1829337 NESTED LOOPS                   VIEW\n44y663h05c264      1209751238         50          2          3        513    1835472    1834959 NESTED LOOPS                   VIEW\n44y663h05c264      1765264009         89          2          3        513    1836176    1835663 NESTED LOOPS                   VIEW\nadhz0uzcu79tx      1472624336        997         91         92          3   96798591   96798588 VIEW PUSHED PREDICATE          MERGE JOIN OUTER<\/code><\/pre>\n
Columns for cost propagation errors query<\/h2>\n\n\n\n\n\t\n\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t
Column<\/th>Description<\/th>\n<\/tr>\n<\/thead>\n
SQL_ID<\/td>SQL ID<\/td>\n<\/tr>\n
PLAN_HASH_VALUE<\/td>execution plan hash value<\/td>\n<\/tr>\n
SECONDS<\/td>total elapsed seconds<\/td>\n<\/tr>\n
P_ID<\/td>parent step ID in the execution plan<\/td>\n<\/tr>\n
C_ID<\/td>child step ID in the execution plan<\/td>\n<\/tr>\n
P_COST<\/td>parent operation cost<\/td>\n<\/tr>\n
C_COST<\/td>child operation cost<\/td>\n<\/tr>\n
DIFF<\/td>difference between child and parent cost<\/td>\n<\/tr>\n
P_OP<\/td>parent operation name<\/td>\n<\/tr>\n
C_OP<\/td>child operation name<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n
The SECONDS column contains the total elapsed time. That’s certainly one dimension worth focusing on in order to identify candidates with the highest tuning potential. The other is the magnitude of the non-propagated cost (DIFF), which might indicate the operations where the optimizer made the largest mistakes.<\/p>\n
Last but not least, ensure that you licensed the Diagnostic and Tuning Pack before running the query.<\/p>\n
As an alternative, here’s the poor man’s way for obtaining similar information without any additional licenses:<\/p>\n
select\n    c.sql_id,c.plan_hash_value, round(max(s.elapsed_time)\/1e6) seconds, \n    p.id p_id, c.id c_id, p.cost p_cost, c.cost c_cost, c.cost - p.cost diff,\n    p.operation || ' ' || p.options p_op,\n    c.operation || ' ' || c.options c_op \n  from v$sql_plan c, v$sql_plan p, v$sqlarea s \n  where\n    c.sql_id = p.sql_id and c.plan_hash_value = p.plan_hash_value and\n    c.child_number = p.child_number and  \n    c.parent_id = p.id and p.cost < c.cost \n    and c.sql_id = s.sql_id(+) and c.plan_hash_value = s.plan_hash_value(+)\n  group by c.sql_id,c.plan_hash_value, c.id, c.depth, c.cost, c.parent_id,\n    c.operation, c.options, p.id, p.cost, p.operation, p.options\n  order by  \n--    max(s.elapsed_time) nulls first ,\n    c.cost - p.cost, \n    c.depth\n;<\/code><\/pre>\n
The query above gets the information from v$sql_plan and v$sqlarea instead of dba_hist_sql_plan and dba_hist_sqlstat, respectively. Since these are just the snapshots on the shared pool, you’d need to periodically run it to obtain the information over a larger time period.<\/p>\n","protected":false},"excerpt":{"rendered":"
Reporting optimizer mistakes where the cost of operations in the execution plan wasn’t propagated to their parents 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-2889","post","type-post","status-publish","format-standard","hentry","category-cost-based-optimizer","category-oracle"],"yoast_head":"\nCost Propagation Errors - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"Reporting optimizer mistakes where the cost of operations in the execution plan wasn't propagated to their parents\" \/>\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\/cost-propagation-errors\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Cost Propagation Errors - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"Reporting optimizer mistakes where the cost of operations in the execution plan wasn't propagated to their parents\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2019-09-01T16:43:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-01T16:43:18+00:00\" \/>\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=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/cost-propagation-errors\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/cost-propagation-errors\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Cost Propagation Errors\",\"datePublished\":\"2019-09-01T16:43:15+00:00\",\"dateModified\":\"2019-09-01T16:43:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/cost-propagation-errors\\\/\"},\"wordCount\":250,\"commentCount\":0,\"articleSection\":[\"cost based optimizer\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/cost-propagation-errors\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/cost-propagation-errors\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/cost-propagation-errors\\\/\",\"name\":\"Cost Propagation Errors - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2019-09-01T16:43:15+00:00\",\"dateModified\":\"2019-09-01T16:43:18+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"Reporting optimizer mistakes where the cost of operations in the execution plan wasn't propagated to their parents\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/cost-propagation-errors\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/cost-propagation-errors\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/cost-propagation-errors\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cost Propagation Errors\"}]},{\"@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":"Cost Propagation Errors - All-round Database Topics","description":"Reporting optimizer mistakes where the cost of operations in the execution plan wasn't propagated to their parents","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\/cost-propagation-errors\/","og_locale":"en_US","og_type":"article","og_title":"Cost Propagation Errors - All-round Database Topics","og_description":"Reporting optimizer mistakes where the cost of operations in the execution plan wasn't propagated to their parents","og_url":"https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/","og_site_name":"All-round Database Topics","article_published_time":"2019-09-01T16:43:15+00:00","article_modified_time":"2019-09-01T16:43:18+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Cost Propagation Errors","datePublished":"2019-09-01T16:43:15+00:00","dateModified":"2019-09-01T16:43:18+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/"},"wordCount":250,"commentCount":0,"articleSection":["cost based optimizer","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/","url":"https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/","name":"Cost Propagation Errors - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2019-09-01T16:43:15+00:00","dateModified":"2019-09-01T16:43:18+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"Reporting optimizer mistakes where the cost of operations in the execution plan wasn't propagated to their parents","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/cost-propagation-errors\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Cost Propagation Errors"}]},{"@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\/2889","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=2889"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2889\/revisions"}],"predecessor-version":[{"id":2906,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2889\/revisions\/2906"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=2889"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=2889"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=2889"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}