{"id":73200,"date":"2014-05-19T14:45:42","date_gmt":"2014-05-19T14:45:42","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/parallel-execution-skew-12c-hybrid-hash-distribution-with-skew-detection\/"},"modified":"2021-07-14T13:07:32","modified_gmt":"2021-07-14T13:07:32","slug":"parallel-execution-skew-12c-hybrid-hash-distribution-with-skew-detection","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/parallel-execution-skew-12c-hybrid-hash-distribution-with-skew-detection\/","title":{"rendered":"Parallel Execution Skew – 12c Hybrid Hash Distribution With Skew Detection"},"content":{"rendered":"

Since Parallel Execution Skew is a common problem that can severely limit the scalability of Parallel Execution (see my previous article<\/a> for a demonstration), Oracle has introduced a new feature in 12c that can address the problem automatically<\/strong>. That’s great, but unfortunately – at least in the initial 12c release – it only applies to a limited number of scenarios, so we still need to look at ways how to address the problem manually. Nevertheless it’s a great feature and a good starting point, maybe in future releases Parallel Execution Skew will be addressed automatically by the database in all the most common scenarios. Until that happens, we’ll have to resort to workarounds.<\/p>\n

For the feature to be triggered automatically Oracle needs a histogram<\/strong> on the join expression. Oracle will use the histogram information to check for popular values<\/strong> – if the histogram shows that such popular values exist a special code path<\/strong> in the optimizer will be triggered that runs a recursive query<\/strong> on the table during optimization to generate a list of such popular values. These values will actually be hard-coded<\/strong> into the cursor information, and the HYBRID HASH<\/strong> distribution of “the other” row source at run time will check the data to distribute against this list of popular values in the cursor. If there is a match<\/strong> the value will be selectively distributed via BROADCAST<\/strong> to all receiving Parallel Execution Servers (all other values will still use the HASH distribution), and for the table with the skewed, popular values data that matches won’t be distributed via HASH, but simply in a random \/ round-robin<\/strong> fashion.<\/p>\n

This way, any data having the popular values will be evenly distributed among the receiving Parallel Execution Servers, and due to the BROADCAST<\/strong> of the popular values in the other row source the join is still guaranteed to produce correct results<\/strong>, as the join will find a match no matter which worker process gets the values assigned.<\/p>\n

\n

Note:<\/strong><\/em> If you’re looking for more details how this feature works, see this accompanying post<\/a> on my blog.<\/p>\n<\/blockquote>\n

Let’s see that using our previous example. For the feature to be triggered we need to add a histogram on the corresponding column:<\/p>\n

exec dbms_stats.gather_table_stats(null, 't_2', method_opt=>'for all columns size 1 for columns fk_id_skew size 254', no_invalidate=>false)<\/pre>\n
and re-execute the parallel variant of our query:<\/p>\n
select count(t_2_filler) from (\r\nselect  \/*+ monitor\r\n            leading(t_1 t_2)\r\n            use_hash(t_2)\r\n            no_swap_join_inputs(t_2)\r\n            pq_distribute(t_2 hash hash) *\/\r\n        t_1.id as t_1_id\r\n      , t_1.filler as t_1_filler\r\n      , t_2.id as t_2_id\r\n      , t_2.filler as t_2_filler\r\nfrom    t_1\r\n      , t_2\r\nwhere\r\n        t_2.fk_id_skew = t_1.id\r\nand     regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') >= regexp_replace(t_1.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c')\r\n);<\/pre>\n
On my test system the query is back to 15 seconds<\/strong>, so that’s a great improvement compared to the 45 seconds<\/strong> it took without the new feature.<\/p>\n
We can see the good distribution confirmed by looking at the output of V$PQ_TQSTAT<\/strong> again. By looking at the computed sub-totals we can also see confirmed that Oracle has actually selectively distributed the popular value for the “other” row source T_1, as indicated by 2000003 rows<\/strong> being produced \/ consumed rather than the actual 2000000 rows that the row source consists of – so the popular value “1” was broadcast to all four worker processes:<\/p>\n
break on dfo_number nodup on tq_id nodup on server_type skip 1 nodup on instance nodup\r\n\r\ncompute sum label Total of num_rows on server_type\r\n\r\nselect\r\n        \/*dfo_number\r\n      , *\/tq_id\r\n      , cast(server_type as varchar2(10)) as server_type\r\n      , instance\r\n      , cast(process as varchar2(8)) as process\r\n      , num_rows\r\n      , round(ratio_to_report(num_rows) over (partition by dfo_number, tq_id, server_type) * 100) as \"%\"\r\n      , cast(rpad('#', round(num_rows * 10 \/ nullif(max(num_rows) over (partition by dfo_number, tq_id, server_type), 0)), '#') as varchar2(10)) as graph\r\n      , round(bytes \/ 1024 \/ 1024) as mb\r\n      , round(bytes \/ nullif(num_rows, 0)) as \"bytes\/row\"\r\nfrom\r\n        v$pq_tqstat\r\norder by\r\n        dfo_number\r\n      , tq_id\r\n      , server_type desc\r\n      , instance\r\n      , process\r\n;\r\n\r\n     TQ_ID SERVER_TYP   INSTANCE PROCESS    NUM_ROWS          % GRAPH              MB  bytes\/row\r\n---------- ---------- ---------- -------- ---------- ---------- ---------- ---------- ----------\r\n         0 Producer            1 P004         493866         25 ##########         51        109\r\n                                 P005         489961         24 ##########         51        109\r\n                                 P006         508088         25 ##########         53        109\r\n                                 P007         508088         25 ##########         53        109\r\n           ********** **********          ----------\r\n           Total                             2000003<\/span><\/strong>\r\n\r\n           Consumer            1 P000         499615         25 ##########         52        109\r\n                                 P001         500735         25 ##########         52        109\r\n                                 P002         499522         25 ##########         52        109\r\n                                 P003         500131         25 ##########         52        109\r\n           ********** **********          ----------\r\n           Total                             2000003<\/span><\/strong>\r\n\r\n         1 Producer            1 P004         482877         24 #########          49        107\r\n                                 P005         497862         25 ##########         51        106\r\n                                 P006         509934         25 ##########         52        107\r\n                                 P007         509327         25 ##########         52        106\r\n           ********** **********          ----------\r\n           Total                             2000000\r\n\r\n           Consumer            1 P000         500094         25 ##########         51        107\r\n                                 P001         499886         25 ##########         51        107\r\n                                 P002         499484         25 ##########         51        107\r\n                                 P003         500536         25 ##########         51        107\r\n           ********** **********          ----------\r\n           Total                             2000000\r\n\r\n         2 Producer            1 P000              1         25 ##########          0         36\r\n                                 P001              1         25 ##########          0         36\r\n                                 P002              1         25 ##########          0         36\r\n                                 P003              1         25 ##########          0         36\r\n           ********** **********          ----------\r\n           Total                                   4\r\n\r\n           Consumer            1 QC                4        100 ##########          0         36\r\n           ********** **********          ----------\r\n           Total                                   4<\/pre>\n
Here is the corresponding execution plan from 12c:<\/p>\n
----------------------------------------------------------------------------------\r\n| Id  | Operation                        | Name     |    TQ  |IN-OUT| PQ Distrib |\r\n----------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT                 |          |        |      |            |\r\n|   1 |  SORT AGGREGATE                  |          |        |      |            |\r\n|   2 |   PX COORDINATOR                 |          |        |      |            |\r\n|   3 |    PX SEND QC (RANDOM)           | :TQ10002 |  Q1,02 | P->S | QC (RAND)  |\r\n|   4 |     SORT AGGREGATE               |          |  Q1,02 | PCWP |            |\r\n|*  5 |      HASH JOIN                   |          |  Q1,02 | PCWP |            |\r\n|   6 |       PX RECEIVE                 |          |  Q1,02 | PCWP |            |\r\n|   7 |        PX SEND HYBRID HASH       | :TQ10000 |  Q1,00 | P->P | HYBRID HASH|\r\n|   8 |         STATISTICS COLLECTOR     |          |  Q1,00 | PCWC |            |\r\n|   9 |          PX BLOCK ITERATOR       |          |  Q1,00 | PCWC |            |\r\n|* 10 |           TABLE ACCESS FULL      | T_1      |  Q1,00 | PCWP |            |\r\n|  11 |       PX RECEIVE                 |          |  Q1,02 | PCWP |            |\r\n|  12 |        PX SEND HYBRID HASH (SKEW)<\/span><\/strong>| :TQ10001 |  Q1,01 | P->P | HYBRID HASH|\r\n|  13 |         PX BLOCK ITERATOR        |          |  Q1,01 | PCWC |            |\r\n|* 14 |          TABLE ACCESS FULL       | T_2      |  Q1,01 | PCWP |            |\r\n----------------------------------------------------------------------------------<\/pre>\n
The new feature is indicated in operation ID = 12<\/strong> by the keyword “(SKEW)”<\/strong> in the “PX SEND HYBRID HASH (SKEW)” operation. By the way, I believe that the “PQ Distrib” column is supposed to show the same “HYBRID HASH (SKEW)” distribution but is simply truncated in the output. According to my description above in my opinion the other PX SEND HYBRID HASH operation (the distribution of T_1) should also be flagged with “(SKEW)” because it doesn’t do a simple hash distribution, but does a mixed BROADCAST\/HASH<\/strong> distribution.<\/p>\n
But as pointed out before, the SKEW aware distribution feature at present does only work in certain scenarios. For example, if you make the join an outer join<\/strong>, or if you replace the full table scan on T_2 with a simple unmerged view<\/strong>, or make T_2 the build row source<\/strong> rather than then probe row source, then the feature isn’t triggered:<\/p>\n
Outer Join example:<\/p>\n
select count(t_1_filler) from (\r\nselect  \/*+ monitor \r\n            leading(t_1 t_2)\r\n            use_hash(t_2)\r\n            no_swap_join_inputs(t_2)\r\n            pq_distribute(t_2 hash hash) *\/\r\n        t_1.id as t_1_id\r\n      , t_1.filler as t_1_filler\r\n      , t_2.id as t_2_id\r\n      , t_2.filler as t_2_filler\r\nfrom    t_1\r\n      , t_2\r\nwhere\r\n        t_2.fk_id_skew (+) = t_1.id<\/span><\/strong>\r\nand     regexp_replace(t_2.filler (+), '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') >= regexp_replace(t_1.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c')\r\n);<\/pre>\n
T_2 as build row source:<\/p>\n
select count(t_2_filler) from (\r\nselect  \/*+ monitor \r\n            leading(t_1 t_2)\r\n            use_hash(t_2)\r\n            swap_join_inputs(t_2)<\/strong><\/span>\r\n            pq_distribute(t_2 hash hash) *\/\r\n        t_1.id as t_1_id\r\n      , t_1.filler as t_1_filler\r\n      , t_2.id as t_2_id\r\n      , t_2.filler as t_2_filler\r\nfrom    t_1\r\n      , t_2\r\nwhere\r\n        t_2.fk_id_skew = t_1.id\r\nand     regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') >= regexp_replace(t_1.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c')\r\n);<\/pre>\n
T_2 as simple view row source:<\/p>\n
select count(t_2_filler) from (\r\nselect  \/*+ monitor \r\n            leading(t_1 t_2)\r\n            use_hash(t_2)\r\n            no_swap_join_inputs(t_2)\r\n            pq_distribute(t_2 hash hash) *\/\r\n        t_1.id as t_1_id\r\n      , t_1.filler as t_1_filler\r\n      , t_2.id as t_2_id\r\n      , t_2.filler as t_2_filler\r\nfrom    t_1\r\n      , (select \/*+ no_merge *\/ * from t_2)<\/strong><\/span> t_2\r\nwhere\r\n        t_2.fk_id_skew = t_1.id\r\nand     regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') >= regexp_replace(t_1.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c')\r\n);<\/pre>\n
All these queries are back to 45 seconds<\/strong> on my test system and the execution plan doesn’t show any sight of the SKEW detection feature.<\/p>\n
It’s also worth pointing out that the special SKEW aware distribution is only supported with hash joins<\/strong>, so, for example, using a parallel MERGE JOIN<\/strong> instead in the previous example won’t trigger the SKEW aware distribution either.<\/p>\n
Furthermore if you happen to have joins to more than two tables then the join order dictates whether the feature might get used or not.<\/p>\n
For example this three table join makes use of the feature:<\/p>\n
select count(t_2_filler) from (\r\nselect  \/*+ monitor\r\n            leading(a b c)<\/strong><\/span>\r\n            use_hash(b c)\r\n            no_swap_join_inputs(b)\r\n            no_swap_join_inputs(c)\r\n            pq_distribute(b hash hash)\r\n            pq_distribute(c hash hash)\r\n            *\/\r\n        a.id as t_1_id\r\n      , a.filler as t_1_filler\r\n      , c.id as t_2_id\r\n      , c.filler as t_2_filler\r\nfrom    t_1 a\r\n      , t_1 b\r\n      , t_2 c\r\nwhere\r\n        c.fk_id_skew = b.id\r\nand     regexp_replace(c.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') >= regexp_replace(b.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c')\r\nand     a.id = b.id<\/strong><\/span>\r\nand     regexp_replace(a.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') >= regexp_replace(b.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c')\r\n);<\/pre>\n
But this one here uses slightly different joins and a different join order that joins T_2 first to another instance of T_1, so it doesn’t make use of the feature and therefore suffers again from skew:<\/p>\n
select count(t_2_filler) from (\r\nselect  \/*+ monitor\r\n            leading(a c b)<\/strong><\/span>\r\n            use_hash(b c)\r\n            swap_join_inputs(b)\r\n            no_swap_join_inputs(c)\r\n            pq_distribute(b hash hash)\r\n            pq_distribute(c hash hash)\r\n            *\/\r\n        a.id as t_1_id\r\n      , a.filler as t_1_filler\r\n      , c.id as t_2_id\r\n      , c.filler as t_2_filler\r\nfrom    t_1 a\r\n      , t_1 b\r\n      , t_2 c\r\nwhere\r\n        c.fk_id_skew = b.id\r\nand     regexp_replace(c.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') >= regexp_replace(b.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c')\r\nand     a.id = c.id<\/strong><\/span>\r\nand     regexp_replace(a.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') >= regexp_replace(c.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c')\r\n);<\/pre>\n
So what can we do to address Parallel Execution Skew manually<\/strong>, in cases where in 12c the new skew aware distribution doesn’t work yet or in versions prior 12c? We’ll have a look at that in the next part of the series.<\/p>\n","protected":false},"excerpt":{"rendered":"
Since Parallel Execution Skew is a common problem that can severely limit the scalability of Parallel Execution (see my previous article for a demonstration), Oracle has introduced a new feature in 12c that can address the problem automatically. That’s great, but unfortunately – at least in the initial 12c release – it only applies to a limited number of scenarios,…<\/p>\n","protected":false},"author":316197,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48420,48500,48501],"coauthors":[],"class_list":["post-73200","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-hybrid-hash","tag-skew","tag-skew-detection"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73200","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316197"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73200"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73200\/revisions"}],"predecessor-version":[{"id":91686,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73200\/revisions\/91686"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73200"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73200"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73200"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73200"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}