{"id":73192,"date":"2014-07-23T14:06:32","date_gmt":"2014-07-23T14:06:32","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/parallel-execution-skew-skew-caused-by-outer-joins\/"},"modified":"2021-07-14T13:07:31","modified_gmt":"2021-07-14T13:07:31","slug":"parallel-execution-skew-skew-caused-by-outer-joins","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/parallel-execution-skew-skew-caused-by-outer-joins\/","title":{"rendered":"Parallel Execution Skew – Skew Caused By Outer Joins"},"content":{"rendered":"

In the final part of this series I want to cover one last source of Parallel Execution Skew: Skew caused by Outer Joins.<\/p>\n

If you have Outer Joins where the majority of foreign key values is NULL<\/strong>, this can lead to the following symptoms:\u00a0From a processing point of view for a parallel outer join, the NULL value in the foreign key is a popular value<\/strong> like a non-NULL popular value. All rows with NULLs in the join column will be sent to the same<\/strong> Parallel Execution Server, and the whole Parallel Execution will suffer again from a bad distribution problem.<\/p>\n

Consider the following sample query:<\/p>\n

select count(t_2_filler) from (\r\nselect  \/*+ monitor\r\n            leading(t_2 t_1)\r\n            use_hash(t_1)\r\n            swap_join_inputs(t_1)\r\n            pq_distribute(t_1 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      , regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') || regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'i') as t_2_filler\r\nfrom    t_1\r\n      , t_2\r\nwhere\r\n        t_2.fk_id_sparse = t_1.id (+)\r\n);<\/pre>\n
This query is quite similar to my previous sample query<\/a>\u00a0for demonstrating skew but it uses now the FK_ID_SPARSE<\/strong> column of T_2<\/strong> to join. Due to the outer join I had to move the CPU intensive expression to the projection part instead of using it as part of the join.<\/p>\n
If I run that query on my test system I get a run time of 45 seconds<\/strong>, which is not much faster than the serial variant, that in this case just runs a couple of seconds longer (52 seconds<\/strong> on my test system).<\/p>\n
As mentioned in the previous installments of this series, this outer join skew problem isn’t covered by the new Oracle database 12c automatic skew detection \/ handling code, so we need to address this problem manually in any version of Oracle currently available.<\/p>\n
Similar to the initial approach used for the popular values in the previous part of the series we could come up with a solution like the following:<\/p>\n
select count(t_2_filler) from (\r\n-- The first part of the query inner joins the non-null FK values\r\nselect  \/*+ monitor\r\n            leading(t_2 t_1)\r\n            use_hash(t_1)\r\n            swap_join_inputs(t_1)\r\n            pq_distribute(t_1 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      , regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') || regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'i') as t_2_filler\r\nfrom    t_1\r\n      , t_2\r\nwhere\r\n        t_2.fk_id_sparse = t_1.id\r\nand     t_2.fk_id_sparse is not null\r\n---------\r\n---------\r\nunion all\r\n---------\r\n---------\r\n-- The second part processes the null FK values - since they don't find a match no join is required\r\nselect\r\n        NULL as t_1_id\r\n      , NULL as t_1_filler\r\n      , t_2.id as t_2_id\r\n      , regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') || regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'i') as t_2_filler\r\nfrom    t_2\r\nwhere\r\n        t_2.fk_id_sparse is null\r\n);<\/pre>\n
Since the NULL foreign keys won’t find a match in the other row source anyway, the first part<\/strong> of the UNION ALL<\/strong> processes only those rows with a non-null<\/strong> foreign key in T_2<\/strong>, whereas the second part doesn’t join at all and simply substitutes the T_1<\/strong> values with NULLs<\/strong>.<\/p>\n
Running that query I’m back to my desired 15 seconds<\/strong> run time, but the solution has a familiar disadvantage: We need to access T_2 twice<\/strong>, and the whole thing is a bit clumsy.<\/p>\n
So, can we come up with an improved solution? Yes, we can: Since we don’t need to find a match for the NULL foreign keys, an obvious approach could be to remap the NULLs to a range of values that are guaranteed to be non-matching, similar to the remapping we did in the previous post:<\/p>\n
define distrib_size = 1e4\r\n\r\nselect count(t_2_filler) from (\r\nselect  \/*+ monitor\r\n            leading(t_2 t_1)\r\n            use_hash(t_1)\r\n            swap_join_inputs(t_1)\r\n            pq_distribute(t_1 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      , regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') || regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'i') as t_2_filler\r\nfrom    t_1\r\n      , t_2\r\nwhere\r\n        -- this case expression maps FK_ID_SPARSE NULLs to a range of negative integers based on the primary key of T_2 that are guaranteed to be non-matching to T_1.ID\r\n        case when t_2.fk_id_sparse is null then -mod(t_2.id, &distrib_size) - 1 else t_2.fk_id_sparse end = t_1.id (+)\r\n);<\/pre>\n
Remapping the NULLs to a range of values that are guaranteed to be non-matching doesn’t change the result of the query, so semantically this query is equal to the original one. We don’t need an additional table for remapping here since we just want to spread<\/strong> the NULLs<\/strong> across some other non-matching values.<\/p>\n
Running this query I’m back to my optimal 15 seconds<\/strong>, and I don’t need to access T_2 twice, so we have already addressed one of the disadvantages of above approach.<\/p>\n
And again, from 11g on we could make use of virtual columns<\/strong> so we don’t have to write the complex join expression:<\/p>\n
alter table t_2 add new_fk_id_sparse as (case when t_2.fk_id_sparse is null then -mod(t_2.id, &distrib_size) - 1 else t_2.fk_id_sparse end);\r\n\r\nexec dbms_stats.gather_table_stats(null, 't_2', method_opt=>'for all columns size 1 for columns fk_id_skew size 254, new_fk_id_skew size 254, new_fk_id_sparse size 254', no_invalidate=>false)<\/pre>\n
And our query now looks like this:<\/p>\n
select count(t_2_filler) from (\r\nselect  \/*+ monitor\r\n            leading(t_2 t_1)\r\n            use_hash(t_1)\r\n            swap_join_inputs(t_1)\r\n            pq_distribute(t_1 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      , regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'c') || regexp_replace(t_2.filler, '^\\s+([[:alnum:]]+)\\s+$', lpad('\\1', 10), 1, 1, 'i') as t_2_filler\r\nfrom    t_1\r\n      , t_2\r\nwhere\r\n        t_2.new_fk_id_sparse = t_1.id (+)\r\n);<\/pre>\n
This looks pretty similar to our original query but performs much, much better.<\/p>\n
It’s a wrap<\/h3>\n
I hope this series has given you some ideas how you might be able to speed up<\/strong> Parallel Execution in case you encounter Parallel Execution Skew, but keep in mind that I’ve focused here on joins<\/strong> only and there are other operations that might be affected by skew too, like sorts<\/strong> and aggregates<\/strong>.<\/p>\n
For such operations things look a bit different as we don’t have two row sources to process, so what I’ve outlined here cannot be simply be applied one to one. However some of the ideas presented here will hopefully serve as a starting point.<\/p>\n","protected":false},"excerpt":{"rendered":"
In the final part of this series I want to cover one last source of Parallel Execution Skew: Skew caused by Outer Joins. If you have Outer Joins where the majority of foreign key values is NULL, this can lead to the following symptoms:\u00a0From a processing point of view for a parallel outer join, the NULL value in the foreign…<\/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":[48464,48467,48468,48500],"coauthors":[],"class_list":["post-73192","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-outer-join","tag-parallel","tag-parallel-execution","tag-skew"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73192","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=73192"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73192\/revisions"}],"predecessor-version":[{"id":91679,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73192\/revisions\/91679"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73192"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73192"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73192"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73192"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}