My Oracle Support Banner

Bulk Insert With Parallel DmML Fails to Use Parallel Engine (Doc ID 357841.1)

Last updated on JANUARY 30, 2022

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


Using Bulk Binds with Parallel DML, the Parallel engine is not used.

This behavior can be reproduced with the following:

drop table test_par_dml;
drop table test_par_dml2;
create table test_par_dml (a number) parallel 4;
create table test_par_dml2(a number)parallel 4;
 for i in 1..10000 loop
 insert into test_par_dml values (i);
 end loop;
alter session enable parallel dml;declare
type tabtyp is table of test_par_dml%rowtype;
mytab tabtyp;
cursor mycur is select * from test_par_dml;
  open mycur;
    fetch mycur bulk collect into mytab limit 10;
    exit when mycur%notfound;
    forall r in mytab.first .. mytab.last
        insert /*+parallel(test_par_dml2,4) */ into test_par_dml2 values
  end loop;
select * from v$pq_sesstat;


SQL> select * from v$pq_sesstat;STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             1
DML Parallelized                        0             0  <----- this means Parallel engine was not used .
DDL Parallelized                        0             0
DFO Trees                               1             1
Server Threads                          4             0
Allocation Height                       4             0
Allocation Width                        1             0
Local Msgs Sent                        76            76
Distr Msgs Sent                         0             0
Local Msgs Recv'd                      80            80
Distr Msgs Recv'd                       0             0
11 rows selected.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.