Skip to Main Content
  • Questions

  • PDML disabled because child referential constraints are present

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: November 16, 2022 - 5:18 pm UTC

Last updated: November 21, 2022 - 2:31 pm UTC

Version: Oracle 19c EE 19.17.0.0.0.

Viewed 1000+ times

You Asked

Table 8-1 ( https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/vldb-and-partitioning-guide.pdf#page=353&zoom=100,0,120 ) of the VLDB and Partition Guid (19c) lists cell ("Insert", "Issued on Parent") as "(Not applicable)". Why is it "not applicable" to PDML insert into a referenced (parent) table of a foreign key constraint? What's more the example below indicates to me that it actually ought to say "Not parallelized" and perhaps with a note to say that it is still eligible for direct path load. Furthermore, the optimizer hint report says the "parallel" hint was unused. Though PDML was not performed, it does appear the optimizer used the hint for direct path load. Reasoning: without the hint, one does not get the direct path load.

Clarification note: When attempting PDML insert on the referencing (child) table of a foreign key constraint the optimizer note references the "parent referential constraint" and this seems to align with Table 8-1's cell ("Insert", "Issued on Child"). My question is regarding PDML insert on the parent table, not the child table. In the example below, the variation where it inserts into tale c is for comparison purposes only.

Using Oracle 19c EE 19.17.0.0.0.
parallel_degree_policy = MANUAL.

This is the primary test case that shows one is not able to PDML a referenced (parent) table (which does not seem consistent with the documentation), but yet one is able to get direct path loads.

SQL> set linesize 107
SQL> set pagesize 0 embedded on
SQL> -- ========================
SQL> -- Referenced (Parent) PDML
SQL> -- ========================
SQL> create table p( x int constraint p_pk primary key );

 Table created.

SQL> create table c( x int constraint c_fk references p( x ) );

Table created.

SQL> insert /*+ enable_parallel_dml parallel(2) */ into p( x ) select 1 from dual;

1 row created.

SQL> select * from table(dbms_xplan.display_cursor(format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  0ktdd66391zgt, child number 0
-------------------------------------
insert /*+ enable_parallel_dml parallel(2) */ into p( x ) select 1 from
dual
 
Plan hash value: 2781518217
 
---------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |      |       |     2 (100)|          |
|   1 |  LOAD AS SELECT                  | P    |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |      |     1 |     2   (0)| 00:00:01 |
|   3 |    FAST DUAL                     |      |     1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   0 -  STATEMENT
         U -  parallel(2)                                                       <============= However, it was used to give us direct path load
 
Note
-----
   - PDML disabled because child referential constraints are present            <============= child, No PDML
 
 
27 rows selected.
 
SQL> select * from p;
select * from p
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel          <============= No PDML, but we did get direct path load

SQL> drop table c;
 
Table dropped.
 
SQL> drop table p;
 
Table dropped.


This second test case is only provided for comparison purposes. The primary test case above is attempting PDML into a referenced (parent) table (which does not seem consistent with the documentation). This second test case is attempting (and achieving) PDML into a referencing (child) table (which seems consistent with the documentation).

SQL> -- ========================
SQL> -- Referencing (Child) PDML
SQL> -- ========================
SQL> create table p( x int constraint p_pk primary key );
 
Table created.
 
SQL> create table c( x int constraint c_fk references p( x ) );
 
Table created.
 
SQL> insert into p( x ) select 1 from dual;
 
1 row created.
 
SQL> insert /*+ enable_parallel_dml parallel(2) */ into c( x ) select 1 from dual;
 
1 row created.
 
SQL> select * from table(dbms_xplan.display_cursor(format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  17bnvjhnq7sgn, child number 0
-------------------------------------
insert /*+ enable_parallel_dml parallel(2) */ into c( x ) select 1 from
dual
 
Plan hash value: 1388734953
 
-------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |       |     2 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL | C    |       |            |          |
|   2 |   FAST DUAL              |      |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   0 -  STATEMENT
         U -  parallel(2)
 
Note
-----
   - PDML disabled because parent referential constraints are present           <============= parent, No PDML
   - Direct Load disabled because parent referential constraints are present    <============= parent, No direct path load
 
 
27 rows selected.
 
SQL> select * from c;
         X
----------
         1                                                                      <============= No PDML and no direct path load

SQL> drop table c;
 
Table dropped.
 
SQL> drop table p;
 
Table dropped.


This third test case shows that the parallel hint in the first test case really was used even though the execution plan said it was unused.

SQL> -- ===============================
SQL> -- Referenced (Parent) No Parallel
SQL> -- ===============================
SQL> create table p( x int constraint p_pk primary key );
 
Table created.
 
SQL> create table c( x int constraint c_fk references p( x ) );
 
Table created.
 
SQL> insert /*+ enable_parallel_dml */ into p( x ) select 1 from dual;
 
1 row created.
 
SQL> select * from table(dbms_xplan.display_cursor(format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  4jz8y9jxvzgcx, child number 0
-------------------------------------
insert /*+ enable_parallel_dml */ into p( x ) select 1 from dual
 
Plan hash value: 1388734953
 
-------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |       |     2 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL | P    |       |            |          |
|   2 |   FAST DUAL              |      |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------
 
Note
-----
   - PDML disabled because object is not decorated with parallel clause
   - Direct Load disabled because no append hint given and not executing in parallel
 
 
19 rows selected.
 
SQL> select * from p;
         X
----------                                                                      <============= It was the parallel hint that caused direct path load
         1                                                                      <============= because without the parallel hint we do not get direct path load
 
SQL> drop table c;
 
Table dropped.
 
SQL> drop table p;
 
Table dropped.


with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/ocom2kbhgzyn24o8qpxrxwjyf

and Chris said...

Why is it "not applicable" to PDML insert into a referenced (parent) table of a foreign key constraint?

Because the foreign key has no impact when inserting into a parent table!

The constraint is on the child table. When you add a parent row, there can't be any child rows. So there's nothing to check.

=> this scenario doesn't apply

I don't understand why you're seeing a serial plan. I've been able to get a parallel plan on Live SQL, 19c, 21c, and Always Free Autonomous 21c:

https://livesql.oracle.com/apex/livesql/s/octyjv0yhhno5j9wzus7okjkv 

alter session set parallel_degree_policy = MANUAL;
create table p( x int constraint p_pk primary key );

create table c( x int constraint c_fk references p( x ) );

insert /*+ enable_parallel_dml parallel(2) */ into p( x ) select 1 from dual;

select * from table(dbms_xplan.display_cursor(format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'));

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID  0ktdd66391zgt, child number 0
-------------------------------------
insert /*+ enable_parallel_dml parallel(2) */ into p( x ) select 1 from 
dual
 
Plan hash value: 232068378
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                      |          |       |     2 (100)|          |        |      |            |
|   1 |  PX COORDINATOR                       |          |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10002 |     1 |     2   (0)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE                  | P        |       |            |          |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                        |          |     1 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND RANGE                    | :TQ10001 |     1 |     2   (0)| 00:00:01 |  Q1,01 | P->P | RANGE      |
|   6 |       LOAD AS SELECT (HYBRID TSM/HWMB)| P        |       |            |          |  Q1,01 | PCWP |            |
|   7 |        PX RECEIVE                     |          |     1 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |         PX SEND ROUND-ROBIN           | :TQ10000 |     1 |     2   (0)| 00:00:01 |  Q1,00 | S->P | RND-ROBIN  |
|   9 |          PX SELECTOR                  |          |       |            |          |  Q1,00 | SCWC |            |
|  10 |           FAST DUAL                   |          |     1 |     2   (0)| 00:00:01 |  Q1,00 | SCWP |            |
-----------------------------------------------------------------------------------------------------------------------
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
 
   0 -  STATEMENT
           -  parallel(2)
 
select * from p;

ORA-12838: cannot read/modify an object after modifying it in parallel


Possible reasons why you're seeing a serial plan:

- The plan is displayed incorrectly for some reason
- "Something else" is disabling parallel DML
- I've not tested on 19.17 specifically; perhaps there's an issue on this specific RU

Things to check:

- You're able to get parallel DML when there are no foreign key constraints
- (If you're licensed for Diag & Tuning) The SQL monitor automatically captures all parallel statements; check that to see if the insert is there

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

from 19.17

Rajeshwaran, Jeyabal, November 18, 2022 - 1:27 pm UTC

Have tested this in 19.17 still don't see parallelized - may be we are hitting up a bug here ?

demo@ATP19C> set serveroutput off
demo@ATP19C> show parameter parallel_degree_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      MANUAL
demo@ATP19C> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.1.0


demo@ATP19C> create table p( x int constraint p_pk primary key );

Table created.

demo@ATP19C> create table c( x int constraint c_fk references p( x ) );

Table created.

demo@ATP19C> alter session set statistics_level=all;

Session altered.

demo@ATP19C> insert /*+ enable_parallel_dml parallel(2) */ into p( x ) select 1 from dual;

1 row created.

demo@ATP19C> select * from table(dbms_xplan.display_cursor(format => 'allstats last +PEEKED_BINDS -PROJECTION -ALIAS'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0ktdd66391zgt, child number 0
-------------------------------------
insert /*+ enable_parallel_dml parallel(2) */ into p( x ) select 1 from
dual

Plan hash value: 2781518217

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |      |      1 |        |      0 |00:00:00.01 |     133 |      1 |      1 |       |       |          |
|   1 |  LOAD AS SELECT                  | P    |      1 |        |      0 |00:00:00.01 |     133 |      1 |      1 |  2072K|  2072K| 2072K (0)|
|   2 |   OPTIMIZER STATISTICS GATHERING |      |      1 |      1 |      1 |00:00:00.01 |     114 |      0 |      0 |   256K|   256K|          |
|   3 |    FAST DUAL                     |      |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - PDML disabled because child referential constraints are present


20 rows selected.

demo@ATP19C>

The SQL Monitor

A reader, November 19, 2022 - 7:36 pm UTC

Thank you for taking the time to take this question and for attempting to recreate it on various versions. Also, thank you for showing me the :LIVESQL_LAST_SQL_ID argument and for The SQL Monitor idea.
Thank you Jeyabal Rajeshwaran for confirming the result on 19.17.

It is understood that a foreign key constraint exists on the child table. This is why it was so surprising that we were being prevented from PDML insert on the parent table apparently due to the foreign key constraint.
I say apparently because the hint says "PDML disabled because *child* referential constraints are present" and because when the child table's constraint is disabled, we do get PDML insert into the parent table.
It is great to know that it is possible to achieve PDML on a referenced (parent) table of an enabled foreign key constraint and that the documentation is correct. However, we are still left trying to determine what it is about our setup that is preventing PDML insert on the parent table.
Yes, we are able to successfully get PDML on table p if we disable the foreign key constraint on table c that references table p.

Because this is not reproducible on Live SQL the script output is provided here as text (at the risk of leaving out something critical, it is quite verbose).

Note: If the session is altered to enable parallel DML and the table p is decorated with the object level PARALLEL clause (thus avoiding the hints), then the the same non-PDML insert result. (results not shown)
Note: hinting optimizer_features_enable('12.2.0.1') still produced the same non-PDML insert results. (results not shown)
Note: I am fairly sure this issue was presenting prior to 19.17; though, I cannot say when exactly we first noticed this.

This example shows that with the FK constraint disabled, then PDML insert into the referenced (parent) table is achieved as verified by v$sql_monitor.
However, if the constraint is enabled, then PDML insert into the referenced (parent) table is not achieved as verified by v$sql_monitor. Please note the 2 second sleep between test cases and sql_exec_start time. The rows from the v$sql_monitor are from the first test case, not the second.
SQL> column name format a33
SQL> column value format a30
SQL> select name, value
  2    from v$parameter
  3   where name in( 'control_management_pack_access'
  4                , 'compatible'
  5                , 'cpu_count'
  6                , 'optimizer_features_enable'
  7                , 'parallel_degree_limit'
  8                , 'parallel_degree_policy'
  9                , 'parallel_min_time_threshold'
 10                , 'parallel_threads_per_cpu'
 11                , 'statistics_level'
 12                )
 13   order by 1
 14  ;

NAME                              VALUE
--------------------------------- ------------------------------
compatible                        19.0.0
control_management_pack_access    DIAGNOSTIC+TUNING
cpu_count                         8
optimizer_features_enable         19.1.0
parallel_degree_limit             CPU
parallel_degree_policy            MANUAL
parallel_min_time_threshold       AUTO
parallel_threads_per_cpu          1
statistics_level                  TYPICAL

9 rows selected.



SQL> set linesize 171
SQL> set pagesize 0 embedded on
SQL> alter session SET nls_date_format = 'MM/DD/YYYY HH24:MI:SS';

Session altered.

SQL> create table p( x int constraint p_pk primary key );

Table created.

SQL> create table c( x int constraint c_fk references p( x ) );

Table created.

SQL> alter table c modify constraint c_fk disable;

Table altered.

SQL> set feedback on sql_id
SQL> insert /*+ enable_parallel_dml parallel(2) */ into p( x ) select 1 from dual;

1 row created.

SQL_ID: 0ktdd66391zgt
SQL> define my_sql_id = &_sql_id
SQL> set feedback on
SQL> with
  2    "M" as
  3    ( select sid, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_full_plan_hash_value, process_name, px_servers_requested, px_servers_allocated, px_qcsid
  4           , max(sql_exec_id) over() max_sql_exec_id
  5        from v$sql_monitor
  6       where sql_id = '&my_sql_id'
  7    -- order by sql_exec_start desc, sql_exec_id desc, process_name
  8    )
  9    select sid, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_full_plan_hash_value, process_name, px_servers_requested, px_servers_allocated, px_qcsid
 10      from "M"
 11     where sql_exec_id = max_sql_exec_id
 12     order by process_name
 13  ;
old   6:      where sql_id = '&my_sql_id'
new   6:      where sql_id = '0ktdd66391zgt'
       SID SQL_ID        SQL_EXEC_START      SQL_EXEC_ID SQL_PLAN_HASH_VALUE SQL_FULL_PLAN_HASH_VALUE PROCE PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED   PX_QCSID           
---------- ------------- ------------------- ----------- ------------------- ------------------------ ----- -------------------- -------------------- ----------           
      1385 0ktdd66391zgt 11/19/2022 12:49:57    16777217           232068378               3283043155 ora                      4                    4                      
       961 0ktdd66391zgt 11/19/2022 12:49:57    16777217           232068378               3283043155 p000                                                  1385           
      1161 0ktdd66391zgt 11/19/2022 12:49:57    16777217           232068378               3283043155 p001                                                  1385           
      1398 0ktdd66391zgt 11/19/2022 12:49:57    16777217           232068378               3283043155 p002                                                  1385           
      1618 0ktdd66391zgt 11/19/2022 12:49:57    16777217           232068378               3283043155 p003                                                  1385           

5 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '&my_sql_id',format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'));
old   1: select * from table(dbms_xplan.display_cursor(sql_id => '&my_sql_id',format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'))
new   1: select * from table(dbms_xplan.display_cursor(sql_id => '0ktdd66391zgt',format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'))
PLAN_TABLE_OUTPUT                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0ktdd66391zgt, child number 0                                                                                                                                      
-------------------------------------                                                                                                                                      
insert /*+ enable_parallel_dml parallel(2) */ into p( x ) select 1 from                                                                                                    
dual                                                                                                                                                                       
                                                                                                                                                                           
Plan hash value: 232068378                                                                                                                                                 
                                                                                                                                                                           
-----------------------------------------------------------------------------------------------------------------------                                                    
| Id  | Operation                             | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |                                                    
-----------------------------------------------------------------------------------------------------------------------                                                    
|   0 | INSERT STATEMENT                      |          |       |     2 (100)|          |        |      |            |                                                    
|   1 |  PX COORDINATOR                       |          |       |            |          |        |      |            |                                                    
|   2 |   PX SEND QC (RANDOM)                 | :TQ10002 |     1 |     2   (0)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |                                                    
|   3 |    INDEX MAINTENANCE                  | P        |       |            |          |  Q1,02 | PCWP |            |                                                    
|   4 |     PX RECEIVE                        |          |     1 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |                                                    
|   5 |      PX SEND RANGE                    | :TQ10001 |     1 |     2   (0)| 00:00:01 |  Q1,01 | P->P | RANGE      |                                                    
|   6 |       LOAD AS SELECT (HYBRID TSM/HWMB)| P        |       |            |          |  Q1,01 | PCWP |            |                                                    
|   7 |        OPTIMIZER STATISTICS GATHERING |          |     1 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |                                                    
|   8 |         PX RECEIVE                    |          |     1 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |                                                    
|   9 |          PX SEND ROUND-ROBIN          | :TQ10000 |     1 |     2   (0)| 00:00:01 |  Q1,00 | S->P | RND-ROBIN  |                                                    
|  10 |           PX SELECTOR                 |          |       |            |          |  Q1,00 | SCWC |            |                                                    
|  11 |            FAST DUAL                  |          |     1 |     2   (0)| 00:00:01 |  Q1,00 | SCWP |            |                                                    
-----------------------------------------------------------------------------------------------------------------------                                                    
                                                                                                                                                                           
Hint Report (identified by operation id / Query Block Name / Object Alias):                                                                                                
Total hints for statement: 1                                                                                                                                               
---------------------------------------------------------------------------                                                                                                
                                                                                                                                                                           
   0 -  STATEMENT                                                                                                                                                          
           -  parallel(2)                                                                                                                                                  
                                                                                                                                                                           
Note                                                                                                                                                                       
-----                                                                                                                                                                      
   - Degree of Parallelism is 2 because of hint                                                                                                                            
                                                                                                                                                                           

35 rows selected.

SQL> select * from p;
select * from p
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel 


SQL> drop table c;

Table dropped.

SQL> drop table p;

Table dropped.

SQL> undefine my_sql_id
SQL> 
SQL> exec dbms_session.sleep(2)

PL/SQL procedure successfully completed.

SQL> 
SQL> create table p( x int constraint p_pk primary key );

Table created.

SQL> create table c( x int constraint c_fk references p( x ) );

Table created.

SQL> set feedback on sql_id
SQL> insert /*+ enable_parallel_dml parallel(2) */ into p( x ) select 1 from dual;

1 row created.

SQL_ID: 0ktdd66391zgt
SQL> define my_sql_id = &_sql_id
SQL> set feedback on
SQL> 
SQL> with
  2    "M" as
  3    ( select sid, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_full_plan_hash_value, process_name, px_servers_requested, px_servers_allocated, px_qcsid
  4           , max(sql_exec_id) over() max_sql_exec_id
  5        from v$sql_monitor
  6       where sql_id = '&my_sql_id'
  7    -- order by sql_exec_start desc, sql_exec_id desc, process_name
  8    )
  9    select sid, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_full_plan_hash_value, process_name, px_servers_requested, px_servers_allocated, px_qcsid
 10      from "M"
 11     where sql_exec_id = max_sql_exec_id
 12     order by process_name
 13  ;
old   6:      where sql_id = '&my_sql_id'
new   6:      where sql_id = '0ktdd66391zgt'
       SID SQL_ID        SQL_EXEC_START      SQL_EXEC_ID SQL_PLAN_HASH_VALUE SQL_FULL_PLAN_HASH_VALUE PROCE PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED   PX_QCSID           
---------- ------------- ------------------- ----------- ------------------- ------------------------ ----- -------------------- -------------------- ----------           
      1385 0ktdd66391zgt 11/19/2022 12:49:57    16777217           232068378               3283043155 ora                      4                    4                      
       961 0ktdd66391zgt 11/19/2022 12:49:57    16777217           232068378               3283043155 p000                                                  1385           
      1161 0ktdd66391zgt 11/19/2022 12:49:57    16777217           232068378               3283043155 p001                                                  1385           
      1398 0ktdd66391zgt 11/19/2022 12:49:57    16777217           232068378               3283043155 p002                                                  1385           
      1618 0ktdd66391zgt 11/19/2022 12:49:57    16777217           232068378               3283043155 p003                                                  1385           

5 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '&my_sql_id',format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'));
old   1: select * from table(dbms_xplan.display_cursor(sql_id => '&my_sql_id',format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'))
new   1: select * from table(dbms_xplan.display_cursor(sql_id => '0ktdd66391zgt',format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'))
PLAN_TABLE_OUTPUT                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0ktdd66391zgt, child number 0                                                                                                                                      
-------------------------------------                                                                                                                                      
insert /*+ enable_parallel_dml parallel(2) */ into p( x ) select 1 from                                                                                                    
dual                                                                                                                                                                       
                                                                                                                                                                           
Plan hash value: 2781518217                                                                                                                                                
                                                                                                                                                                           
---------------------------------------------------------------------------------                                                                                          
| Id  | Operation                        | Name | Rows  | Cost (%CPU)| Time     |                                                                                          
---------------------------------------------------------------------------------                                                                                          
|   0 | INSERT STATEMENT                 |      |       |     2 (100)|          |                                                                                          
|   1 |  LOAD AS SELECT                  | P    |       |            |          |                                                                                          
|   2 |   OPTIMIZER STATISTICS GATHERING |      |     1 |     2   (0)| 00:00:01 |                                                                                          
|   3 |    FAST DUAL                     |      |     1 |     2   (0)| 00:00:01 |                                                                                          
---------------------------------------------------------------------------------                                                                                          
                                                                                                                                                                           
Hint Report (identified by operation id / Query Block Name / Object Alias):                                                                                                
Total hints for statement: 1 (U - Unused (1))                                                                                                                              
---------------------------------------------------------------------------                                                                                                
                                                                                                                                                                           
   0 -  STATEMENT                                                                                                                                                          
         U -  parallel(2)                                                                                                                                                  
                                                                                                                                                                           
Note                                                                                                                                                                       
-----                                                                                                                                                                      
   - PDML disabled because child referential constraints are present                                                                                                       
                                                                                                                                                                           

27 rows selected.

SQL> select * from p;
select * from p
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel 


SQL> drop table c;

Table dropped.

SQL> drop table p;

Table dropped.

SQL> undefine my_sql_id


Using parallel_degree_policy MANUAL or LIMITED did not seem to change the result. Using AUTO gave the following two optimizer notes even though the insert was modified to take over one minute and even though all objects involved (except system generated indices to support the primary keys) were decorated with a parallel clause and parallel clauses, if my understanding is correct, are ignored in AUTO mode.
"automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold"
"PDML disabled because object is not decorated with parallel clause"
This is the same result regardless if the constraint is disabled or enabled.

SQL> alter session set parallel_degree_policy = AUTO;

Session altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> create table p( x int constraint p_pk primary key, pad varchar2(2000) default 'x' ) parallel 2;

Table created.

SQL> create table c( x int constraint c_fk references p( x ) ) parallel 4;

Table created.

SQL> create table g( x int ) parallel 8;

Table created.

SQL> insert into g(x) with "D" as (select 1 x from dual connect by level <=1000) select 1 from D, D, D where rownum <= 10e7;

100000000 rows created.

SQL> set feedback on sql_id
SQL> --insert into p( x ) with "D" as (select 1 x from dual connect by level <=1000) select rownum from D, D;
SQL> set timing on
SQL> insert into p( x ) select rownum from g;

100000000 rows created.

SQL_ID: d26wthqszhs4j
Elapsed: 00:02:28.01
SQL> set timing off
SQL> define my_sql_id = &_sql_id
SQL> set feedback on
SQL>
SQL> with
  2    "M" as
  3    ( select sid, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_full_plan_hash_value, process_name, px_servers_requested, px_servers_allocated, px_qcsid
  4           , max(sql_exec_id) over() max_sql_exec_id
  5        from v$sql_monitor
  6       where sql_id = '&my_sql_id'
  7    -- order by sql_exec_start desc, sql_exec_id desc, process_name
  8    )
  9    select sid, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_full_plan_hash_value, process_name, px_servers_requested, px_servers_allocated, px_qcsid
 10      from "M"
 11     where sql_exec_id = max_sql_exec_id
 12     order by process_name
 13  ;
old   6:      where sql_id = '&my_sql_id'
new   6:      where sql_id = 'd26wthqszhs4j'
       SID SQL_ID        SQL_EXEC_START      SQL_EXEC_ID SQL_PLAN_HASH_VALUE SQL_FULL_PLAN_HASH_VALUE PROCE PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED   PX_QCSID
---------- ------------- ------------------- ----------- ------------------- ------------------------ ----- -------------------- -------------------- ----------
      1385 d26wthqszhs4j 11/19/2022 13:41:42    16777223          1023046412               3113533584 ora

1 row selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '&my_sql_id', format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'));
old   1: select * from table(dbms_xplan.display_cursor(sql_id => '&my_sql_id', format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'))
new   1: select * from table(dbms_xplan.display_cursor(sql_id => 'd26wthqszhs4j', format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'))
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d26wthqszhs4j, child number 0
-------------------------------------
insert into p( x ) select rownum from g

Plan hash value: 1023046412

-------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |       | 42484 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL | P    |       |            |          |
|   2 |   COUNT                  |      |       |            |          |
|   3 |    TABLE ACCESS FULL     | G    |    96M| 42484   (1)| 00:00:02 |
-------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - PDML disabled because object is not decorated with parallel clause
   - Direct Load disabled because no append hint given and not executing in parallel


22 rows selected.

SQL> alter session set parallel_degree_policy = AUTO;

Session altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> create table p( x int constraint p_pk primary key, pad varchar2(2000) default 'x' ) parallel 2;

Table created.

SQL> create table c( x int constraint c_fk references p( x ) ) parallel 4;

Table created.

SQL> alter table c modify constraint c_fk disable;

Table altered.

SQL> create table g( x int ) parallel 8;

Table created.

SQL> insert into g(x) with "D" as (select 1 x from dual connect by level <=1000) select 1 from D, D, D where rownum <= 10e7;

100000000 rows created.

SQL> set feedback on sql_id
SQL> --insert into p( x ) with "D" as (select 1 x from dual connect by level <=1000) select rownum from D, D;
SQL> set timing on
SQL> insert into p( x ) select rownum from g;

100000000 rows created.

SQL_ID: d26wthqszhs4j
Elapsed: 00:02:10.89
SQL> set timing off
SQL> define my_sql_id = &_sql_id
SQL> set feedback on
SQL>
SQL> with
  2    "M" as
  3    ( select sid, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_full_plan_hash_value, process_name, px_servers_requested, px_servers_allocated, px_qcsid
  4           , max(sql_exec_id) over() max_sql_exec_id
  5        from v$sql_monitor
  6       where sql_id = '&my_sql_id'
  7    -- order by sql_exec_start desc, sql_exec_id desc, process_name
  8    )
  9    select sid, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_full_plan_hash_value, process_name, px_servers_requested, px_servers_allocated, px_qcsid
 10      from "M"
 11     where sql_exec_id = max_sql_exec_id
 12     order by process_name
 13  ;
old   6:      where sql_id = '&my_sql_id'
new   6:      where sql_id = 'd26wthqszhs4j'
       SID SQL_ID        SQL_EXEC_START      SQL_EXEC_ID SQL_PLAN_HASH_VALUE SQL_FULL_PLAN_HASH_VALUE PROCE PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED   PX_QCSID
---------- ------------- ------------------- ----------- ------------------- ------------------------ ----- -------------------- -------------------- ----------
      1385 d26wthqszhs4j 11/19/2022 13:54:34    16777225          1023046412               3113533584 ora

1 row selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '&my_sql_id', format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'));
old   1: select * from table(dbms_xplan.display_cursor(sql_id => '&my_sql_id', format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'))
new   1: select * from table(dbms_xplan.display_cursor(sql_id => 'd26wthqszhs4j', format => 'ALL LAST +PEEKED_BINDS -PROJECTION -ALIAS'))
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d26wthqszhs4j, child number 0
-------------------------------------
insert into p( x ) select rownum from g

Plan hash value: 1023046412

-------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |       | 42484 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL | P    |       |            |          |
|   2 |   COUNT                  |      |       |            |          |
|   3 |    TABLE ACCESS FULL     | G    |    96M| 42484   (1)| 00:00:02 |
-------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - PDML disabled because object is not decorated with parallel clause
   - Direct Load disabled because no append hint given and not executing in parallel


22 rows selected.

Chris Saxon
November 21, 2022 - 2:31 pm UTC

Thanks for the detailed test case.

It looks like there was a recent change. Parallel inserts on the parent lock the table in exclusive mode. This is a problem if you want to insert into the child in another session - this will be blocked. To avoid this parallel DML on the parent changing the primary key was disabled.

If this is an issue for you please raise it with support.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.