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 9.2.0.8 and laterOracle 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.
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 011 rows selected.Cause
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
| Symptoms |
| Cause |
| Solution |