I'm trying to delete a large number of rows from a table, but parallelism isn't fully working and the execution plan Note says "PDML disabled because array binds are used". How can I fully enable parallelism for these SQL statements?
--drop table test_table;
create table test_table(a number, b number);
insert into test_table select level, level from dual connect by level <= 100000;
begin
dbms_stats.gather_table_stats(user, 'TEST_TABLE');
end;
/
(This is a vastly simplified version of the real problem.)
declare
v_numbers sys.odcinumberlist := sys.odcinumberlist();
begin
for i in 1 .. 100 loop
v_numbers.extend;
v_numbers(v_numbers.count) := i;
end loop;
forall i in 1 .. v_numbers.count
delete /*+ enable_parallel_dml parallel(8) */ from test_table where a = v_numbers(i);
end;
/
Find the SQL_TEXT and SQL_ID for the SQL statement:
-- (The SQL_TEXT may look slightly difference since it's created from the forall command.)
select sql_text, sql_id
from gv$sql
where lower(sql_fulltext) like lower('%delete%from%test_table%where%a%')
and sql_fulltext not like '%quine%';
SQL_ID: a748dtrpfqsra
SQL_TEXT: DELETE /*+ enable_parallel_dml parallel(8) */ FROM TEST_TABLE WHERE A = :B1
Find the execution plan:
select *
from table(dbms_xplan.display_cursor(sql_id => 'a748dtrpfqsra'));
SQL_ID a748dtrpfqsra, child number 0
-------------------------------------
DELETE /*+ enable_parallel_dml parallel(8) */ FROM TEST_TABLE WHERE A =
:B1
Plan hash value: 880908202
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 10 (100)| | | | |
| 1 | DELETE | TEST_TABLE | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 1 | 5 | 10 (10)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 1 | 5 | 10 (10)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| TEST_TABLE | 1 | 5 | 10 (10)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(:Z>=:Z AND :Z<=:Z)
filter("A"=:B1)
Note
-----
- Degree of Parallelism is 8 because of hint
- PDML disabled because array binds are used
While the above code is using some parallelism, it's only performing the read part in parallel. There is no "PX" operation above the "DELETE" operation, so the writing is not happening in parallel.
How can I get fully parallel DML to work in the above code?
Oracle is saving you from yourself by not allowing parallel DML in FORALL statements. The purpose of the FORALL command is to reduce the amount of overhead for many tiny DML statement by running them in a tight, optimized loop. The purpose of parallelism is to divide-and-conquer a single SQL statement by concurrently applying a large amount of resources to the statement. The two approaches are inherently at odds with each other, and if parallel DML did fully work inside a FORALL statement, it would likely be catastrophically slow and wasteful.
If parallelism is really needed, it's better to either re-write the PL/SQL block into a parallel SQL statement, or concurrently execute the PL/SQL block through a mechanism like multiple scheduler jobs. Don't mix bulk binding and parallel DML.