Parallel DML – not

A fairly common question about parallel DML is: “Why isn’t it happening?” A fairly common (correct) answer to this question is: “Because you haven’t enabled it.” Unlike parallel query and parallel DDL which are enabled by default parallel DML could cause strange deadlocking side-effects so you have to enable it explicitly before you use it (and disable it afterwards), with the statements like:

alter session enable parallel dml;
alter session force parallel dml;
alter session force parallel dml parallel 3;
alter session disable parallel dml;

There are, however, many reasons why Oracle will disallow parallel DML (e.g. this example from 2013) and some of them are listed in the documentation (Link is for 19c). Bear in mind that some things that are listed are no longer true following updates, or are only true in particular circumstances; and some things that block parallel DML are not listed, or are described in such a way that they don’t appear to be listed.

To keep myself up to date I search the executable from time to time for “revealing phrases”, starting with a Linux execution of “strings -a oracle”. For parallel DML I piped the results through “grep -n -i PDML”. I won’t show you the entire (307 line) output from the call to grep, but I will pick out a set of consecutive lines which look very promising:

1827860:PDML disabled in session
1827861:no intrapart PDML and instead-of trigger on view
1827862:no intrapart PDML and only one partition
1827863:no intrapart PDML and two partitions or greater
1827865:PDML disabled for MERGE using conventional insert
1827866:no PDML for domain index during delete/merge/update 
1827867:no PDML on global temp table
1827868:default PDML not allowed
1827869:no PDML for domain indexes that is not parallel ready 
--      always PIDL on cursor temp table  
1827871:no PDML. IPD disabled on table with bitmap index
1827872:PDML disabled due to MTI with conventional load
1827873:PDML enabled in session
--      non-partitioned IOT
--      table locks disabled
1827876:DML violated a PDML restriction
1827877:kkfdtpdml
1827878:ctxflg:%x ctxxyfl:%x pdml %sallowed
1827879:object reference. PDML disallowed. kctflg:0x%x
1827880:arrays. PDML disallowed. kctflg:0x%x
1827881:nested tables. PDML disallowed. kctflg:0x%x
1827882:adts. PDML disallowed. kctflg:0x%x
1827883:delete with retry on row change hint. PDML disallowed
1827884:PDML disallowed for insert-values
1827885:PDML disallowed: Memoptimize for Write insert
--      MTI uniq constr violation                                   [ed: MTI = Multi-table insert ??]
1827887:PDML disallowed: conv insert into temp tab
1827888:PDML disallowed:conv insert into single frag && !IPD
1827889:ignore/change dupkey hint on INSERT. PDML disallowed
1827890:kkfdpdml_restrictions_10
1827891:PDML disallowed: MTI and HWM brokering not allowed
1827892:Complex ADT: PDML disallowed.
1827893:arrays. PDML disallowed. kctflg: 0x%x
--      arrays. PIDL disallowed. Not stored as securefile.
1827895:nested tables. PDML disallowed. kctflg: 0x%x
1827896:typed subquery. PDML disallowed. kctflg: 0x%x
1827897:object references. PDML disallowed. kctflg: 0x%x
1827898:kkfdpdml_restrictions: PDML allowed
1827899:PDML disallowed: Transaction duration Private Temp Table
1827900:Table with ADT columns and VPD Policy: PDML disallowed
1827901:update with retry on row change hint. PDML disallowed
1827902:serializable update. PDML disallowed
1827903: not in parallel long to lob. PDML disallowed
1827904:kkfdpdml_restrictions
--      Basicfile LOB column
1827906:remote table. PDML disallowed
1827907:fro is a view. PDML disallowed
1827908:PDML disallowed: query block can produce at most one row

You’ll notice that there are a number of occurrences of “PDML disallowed” in this list, which makes it look as if it may be the definitive list of notes that appear either in the 10053 trace file or the Notes section of the execution plan, or in the PX trace. There were, however, a few gaps as far as “consecutive” lines were concerned, so I went back to the strings output to fill them – these are the lines marked with “–“

I’m not going to expand on the content or relevance of the list, it’s just something to leave in place as a quick clue to check if you ever find that your DML isn’t executing parallel when you expect it to – do your circumstances seem to match any of the above conditions.

Addendum

You’ll note that a number of the strings reported above include the text: “kctflg: 0x%x”. Having noticed that I was getting some “PDML disabled” comments that weren’t in the list above I did a further text search in the executable and found a set of consecutive strings that might be the values that can go into the place-holders, and so may be further clues when you fail to see parallel DML (or, in some cases of the below, direct path loads):

delete cascade used
workspaces used
deferrable constraints are used
parent referential constraints are present
child constraint is deferrable
returning clause is present
array binds are used
triggers are defined
non partitioned or single fragment IOT used
remote table used
clustered table used
IOT with rowid mapping table used
partitioned IOT with MV dependecy used
table has one or more nested table columns
table has one or more array columns
table has one or more object reference columns
ADTs are used
temporary tables with indexes used
partitioned temporary tables used
LOBs are used with row movement possible
bitmap indexes are defined with row movement possible
bitmap indexes are defined
child ref-partitioned table may need cascaded migration
table has one or more lob columns
merge statement used on an IOT
table has one or more opaque column
cube organized table used
function is not pure and not declared parallel enabled
query block can produce at the most one row
transaction isolation level is serializable
retry on row change hint used
typed subquery used
ignore or change duplicate key hint was used
insert values was used
single fragment or non partitioned table used
MTI used on tables with unique constraints
parallel execution either disabled or not enabled in session
object is not decorated with parallel clause
policies with referential integrity constraints were added
domain indexes are defined
merge statement with conventional insert used
table locks have been disabled
index is not parallel ready
PDML is disabled in current session
table has one or more XMLType columns
instead of triggers defined
unique constraints are enforced by non-unique index
object has FGA policy
no append hint given and not executing in parallel
merge statement has only an update branch
function or domain index defined on the LOB column
check constraints are defined
noappend hint used
insert values with no append values hint used
_direct_path_insert_features parameter used to disable IDLV
row_locking parameter value is not INTENT