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 have 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 each query using the hint ENABLE_PARALLEL_DML. For a few years now, I’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’s test it!

Let’s run two identical INSERT statements after enabling parallel DML in the session via ALTER SESSION:

SQL> set echo on
SQL> set lines 200
SQL> 
SQL> 
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System FLUSH altered.

SQL> 
SQL> 
SQL> DROP TABLE t_sales;

Table T_SALES dropped.

SQL> 
SQL> CREATE TABLE t_sales AS SELECT * FROM sales WHERE 1=0;

Table T_SALES created.

SQL> 
SQL> ALTER SESSION ENABLE PARALLEL DML;

Session altered.

SQL> 
SQL> INSERT /*+ parallel*/ INTO t_sales
  2  SELECT * /* TEST_PDML_1 */ FROM sales;

918,843 rows inserted.

SQL> 
SQL> ROLLBACK;

Rollback complete.

SQL> 
SQL> INSERT /*+ parallel*/ INTO t_sales
  2  SELECT * /* TEST_PDML_1 */ FROM sales;

918,843 rows inserted.

SQL> 
SQL> ROLLBACK;

Rollback complete.

SQL> 
SQL> column sql_text format a50
SQL> 
SQL> SELECT sql_id, sql_text, child_number, optimizer_env_hash_value
  2  FROM   v$sql
  3  WHERE sql_text LIKE '%TEST_PDML_1%'
  4  AND   sql_text NOT LIKE '%v$sql%';

SQL_ID        SQL_TEXT                                           CHILD_NUMBER OPTIMIZER_ENV_HASH_VALUE
------------- -------------------------------------------------- ------------ ------------------------
650ahmv9yck9z INSERT /*+ parallel*/ INTO t_sales SELECT * /* TES            0               3910552229
              T_PDML_1 */ FROM sales                                                                  

As you can see, there is only one child cursor for the INSERT. No surprise here.

Now we’ll disable parallel DML in the session and start two identical INSERT statement with the hint ENABLE_PARALLEL_DML:

SQL> 
SQL> 
SQL> ALTER SESSION DISABLE PARALLEL DML;

Session altered.

SQL> 
SQL> INSERT /*+ enable_parallel_dml parallel*/ INTO t_sales
  2  SELECT * /* TEST_PDML_2 */ FROM sales;

918,843 rows inserted.

SQL> 
SQL> ROLLBACK;

Rollback complete.

SQL> 
SQL> INSERT /*+ enable_parallel_dml parallel*/ INTO t_sales
  2  SELECT * /* TEST_PDML_2 */ FROM sales;

918,843 rows inserted.

SQL> 
SQL> ROLLBACK;

Rollback complete.

SQL> 
SQL> 
SQL> SELECT sql_id, sql_text, child_number, optimizer_env_hash_value
  2  FROM   v$sql
  3  WHERE sql_text LIKE '%TEST_PDML_2%'
  4  AND   sql_text NOT LIKE '%v$sql%';

SQL_ID        SQL_TEXT                                           CHILD_NUMBER OPTIMIZER_ENV_HASH_VALUE
------------- -------------------------------------------------- ------------ ------------------------
duh23r209pqhb INSERT /*+ enable_parallel_dml parallel*/ INTO t_s            0               3056622317
              ales SELECT * /* TEST_PDML_2 */ FROM sales                                              

duh23r209pqhb INSERT /*+ enable_parallel_dml parallel*/ INTO t_s            1               3056622317
              ales SELECT * /* TEST_PDML_2 */ FROM sales                                              


SQL> 
SQL> column pdml_env_mismatch format a22
SQL> 
SQL> SELECT sql_id, child_number, pdml_env_mismatch
  2  FROM   v$sql_shared_cursor
  3  WHERE  sql_id = 'duh23r209pqhb';

SQL_ID        CHILD_NUMBER PDML_ENV_MISMATCH     
------------- ------------ ----------------------
duh23r209pqhb            0 N                     
duh23r209pqhb            1 Y                     

SQL> 
SQL> SELECT id, name, value
  2  FROM   v$sql_optimizer_env
  3  WHERE  sql_id = 'duh23r209pqhb'
  4  AND    child_number = 0
  5  MINUS  
  6  SELECT id, name, value
  7  FROM   v$sql_optimizer_env
  8  WHERE  sql_id = 'duh23r209pqhb'
  9  AND    child_number = 1;
no rows selected

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 „PDML_ENV_MISMATCH“. But comparing the settings in v$sql_optimizer_env, we can see they are exactly the same for both child cursors.

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.

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’s another story. Why should a statement with good shareable cursor in place be hard parsed every time is my question?