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

Last updated on JULY 05, 2017

Applies to:

Oracle Server - Enterprise Edition - Version 9.2.0.8 and later
Information in this document applies to any platform.
***Checked for relevance on 24-Sep-2012***


Symptoms

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;
begin
 for i in 1..10000 loop
 insert into test_par_dml values (i);
 end loop;
 end;
/
commit;
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;
 begin
  open mycur;
  loop
    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
mytab(r);
    commit;
  end loop;
 end;
/
select * from v$pq_sesstat;


Output:

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.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms