{"id":1082,"date":"2019-01-28T00:27:07","date_gmt":"2019-01-27T22:27:07","guid":{"rendered":"http:\/\/blog.sqlora.com\/en\/?p=1082"},"modified":"2020-10-07T21:44:10","modified_gmt":"2020-10-07T19:44:10","slug":"issue-with-the-hint-enable_parallel_dml","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/issue-with-the-hint-enable_parallel_dml\/","title":{"rendered":"Issue with the Hint ENABLE_PARALLEL_DML"},"content":{"rendered":"\n<p>Performing an ETL with large data sets, it is often a good idea to run DML in parallel. But, in contrast to parallel query or DDL, parallel DML has to be explicitly enabled. You had to issue <strong>ALTER SESSION ENABLE PARALLEL DML<\/strong> in the past. Starting with 12c you can enable parallel DML specifically for each query using the hint <strong>ENABLE_PARALLEL_DML<\/strong>. For a few years now, I&#8217;ve been using the hint now and then and was quite happy. An observation I made a few days ago can lead to a rethinking. What I could observe is that for the SQL with embedded hint a new child cursor was created each time. Let&#8217;s test it!<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>Let&#8217;s run two identical INSERT statements after enabling parallel DML in the session via ALTER SESSION:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER SYSTEM FLUSH SHARED_POOL;\n\nSystem FLUSH altered.\n\nSQL&gt; DROP TABLE t_sales;\n\nTable T_SALES dropped.\n\nSQL&gt; CREATE TABLE t_sales AS SELECT * FROM sales WHERE 1=0;\n\nTable T_SALES created.\n\nSQL&gt; ALTER SESSION ENABLE PARALLEL DML;\n\nSession altered.\n\nSQL&gt; INSERT \/*+ parallel*\/ INTO t_sales\n  2  SELECT * \/* TEST_PDML_1 *\/ FROM sales;\n\n918,843 rows inserted.\n\nSQL&gt; ROLLBACK;\n\nRollback complete.\n\nSQL&gt; INSERT \/*+ parallel*\/ INTO t_sales\n  2  SELECT * \/* TEST_PDML_1 *\/ FROM sales;\n\n918,843 rows inserted.\n\nSQL&gt; ROLLBACK;\n\nRollback complete.\n\n\nSQL&gt; column sql_text format a50\nSQL&gt;\nSQL&gt; SELECT sql_id, sql_text, child_number, optimizer_env_hash_value\n  2  FROM   v$sql\n  3  WHERE sql_text LIKE &#039;%TEST_PDML_1%&#039;\n  4  AND   sql_text NOT LIKE &#039;%v$sql%&#039;;\n\nSQL_ID        SQL_TEXT                                           CHILD_NUMBER OPTIMIZER_ENV_HASH_VALUE\n------------- -------------------------------------------------- ------------ ------------------------\n650ahmv9yck9z INSERT \/*+ parallel*\/ INTO t_sales SELECT * \/* TES            0               3910552229\n              T_PDML_1 *\/ FROM sales   \n\n<\/pre><\/div>\n\n\n<p>As you can see, there is only one child cursor for the INSERT. No surprise here.<\/p>\n\n\n\n<p>Now we&#8217;ll disable parallel DML in the session and start two identical INSERT statement with the hint ENABLE_PARALLEL_DML:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER SESSION DISABLE PARALLEL DML;\n\nSession altered.\n\nSQL&gt; INSERT \/*+ enable_parallel_dml parallel*\/ INTO t_sales\n  2  SELECT * \/* TEST_PDML_2 *\/ FROM sales;\n\n918,843 rows inserted.\n\nSQL&gt; ROLLBACK;\n\nRollback complete.\n\nSQL&gt; INSERT \/*+ enable_parallel_dml parallel*\/ INTO t_sales\n  2  SELECT * \/* TEST_PDML_2 *\/ FROM sales;\n\n918,843 rows inserted.\n\nSQL&gt; ROLLBACK;\n\nRollback complete.\n\nSQL&gt; SELECT sql_id, sql_text, child_number, optimizer_env_hash_value\n  2  FROM   v$sql\n  3  WHERE sql_text LIKE &#039;%TEST_PDML_2%&#039;\n  4  AND   sql_text NOT LIKE &#039;%v$sql%&#039;;\n\nSQL_ID        SQL_TEXT                                           CHILD_NUMBER OPTIMIZER_ENV_HASH_VALUE\n------------- -------------------------------------------------- ------------ ------------------------\nduh23r209pqhb INSERT \/*+ enable_parallel_dml parallel*\/ INTO t_s            0               3056622317\n              ales SELECT * \/* TEST_PDML_2 *\/ FROM sales\n\nduh23r209pqhb INSERT \/*+ enable_parallel_dml parallel*\/ INTO t_s            1               3056622317\n              ales SELECT * \/* TEST_PDML_2 *\/ FROM sales\n\n\nSQL&gt; column pdml_env_mismatch format a22\nSQL&gt;\nSQL&gt; SELECT sql_id, child_number, pdml_env_mismatch\n  2  FROM   v$sql_shared_cursor\n  3  WHERE  sql_id = &#039;duh23r209pqhb&#039;;\n\nSQL_ID        CHILD_NUMBER PDML_ENV_MISMATCH\n------------- ------------ ----------------------\nduh23r209pqhb            0 N\nduh23r209pqhb            1 Y                     \n\nSQL&gt;\nSQL&gt; SELECT id, name, value\n  2  FROM   v$sql_optimizer_env\n  3  WHERE  sql_id = &#039;duh23r209pqhb&#039;\n  4  AND    child_number = 0\n  5  MINUS\n  6  SELECT id, name, value\n  7  FROM   v$sql_optimizer_env\n  8  WHERE  sql_id = &#039;duh23r209pqhb&#039;\n  9  AND    child_number = 1 ;\nno rows selected\n\n\n<\/pre><\/div>\n\n\n<p>What happens then? We get two child cursors. Note that OPTIMIZER_ENV_HASH_VALUE is the same. Checking v$sql_shared_cursor for the reason for the new child cursor tells us &#8220;PDML_ENV_MISMATCH&#8221;. But comparing the settings in v$sql_optimizer_env, we can see they are exactly the same for both child cursors.<\/p>\n\n\n\n<p>I believe, this can be a bug. The optimizer environment is saved before PDML is enabled at a query level or something like this. I checked this behavior with 12.1.0.2 and 18c.<\/p>\n\n\n\n<p>I noticed that because in our system some different (bad) execution plan have been generated for newly created child cursors. Well, that problem can be solved with SQL Plan Baselines, but it&#8217;s another story. Why should a statement with good shareable cursor in place be hard parsed every time is my question?<\/p>\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-2925154690547867\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-client=\"ca-pub-2925154690547867\" \ndata-ad-slot=\"2727403138\" \ndata-ad-layout=\"in-article\"\ndata-ad-format=\"fluid\"><\/ins>\n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Performing an ETL with large data sets, it is often a good idea to run DML in parallel. But, in contrast to parallel query or DDL, parallel DML has to be explicitly enabled. You had to issue ALTER SESSION ENABLE PARALLEL DML in the past. Starting with 12c you can enable parallel DML specifically for [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10,50,71,1,2,3],"tags":[98,97],"class_list":["post-1082","post","type-post","status-publish","format-standard","hentry","category-cbo","category-data-warehouse","category-etl","category-general","category-oracle","category-sql","tag-etl","tag-parallel-dml"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1082","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/comments?post=1082"}],"version-history":[{"count":11,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1082\/revisions"}],"predecessor-version":[{"id":1576,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1082\/revisions\/1576"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media?parent=1082"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/categories?post=1082"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/tags?post=1082"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}