RE: MERGE statement with parallel DML enabled deadlocks itself

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 6 May 2015 16:47:30 -0400
Message-ID: <008801d0883d$dff607d0$9fe21770$_at_rsiz.com>

+1 Sayan:  

for this case where the join sources and the merged to destination have identical partitioning this not only avoids the *possibly buggy* deadlock,

but also avoids the child worker re-assembly overhead of (automatic) parallelism.  

You can adjust the effective level of "parallelism" by doing an appropriate number of partitions at a time.

If there is skew in the sizes of partitions you can arrange serial sets to run such that the total number of statements running at a time

(your desired effective parallelism) is constant and the number of rows per
serial set is approximately the size of your largest partition join.  

IF the tablespaces holding your various partitions are on different disk groups (ASM) or otherwise can be positioned on non-interfering i/o stack to the permanent media you might also be able to beat the statmux i/o rate of all your storage by reducing competition. Depending on your storage, this antique idea may or may not be germane. It is explained at length on JL's site as "Go Faster Stripes" if memory serves.  

If one or a few of the partitions are huge, you might see whether you can reliably run them in parallel to some degree, but IF there is a secondary indexed exclusion key within the partition you can use to divide large partitions into total size conforming chunks that might run faster.  

This, though, has nothing whatever to do with resolving the *possible* bug that drives the deadlock that does not seem to be logically required.  

Good luck.  

Partition by name is probably the best way to do it, but you could also do it by generating each statement with the 33 literals that in fact should then prune to a single set of partitions. That would eliminate the possibility of some partition names not lining up in a useful way. You clearly want to generate this code to avoid manual editing hilarity.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sayan Sergeevich Malakshinov Sent: Wednesday, May 06, 2015 7:41 AM
To: Thomas Kellerer
Cc: oracle-l_at_freelists.org
Subject: Re: MERGE statement with parallel DML enabled deadlocks itself  

I think it would be better and more reliable to execute 33 merge statements in parallel sessions/jobs each by own partition, like this:

merge into target_table partition(p1) tg using
(

 select ...
 from base_table_1 partition(p1) bt

      left join data_table_1 partition(p1) dt 
       on ...

 group by ...
) t on (t.part_key = tg.part_key and t.id1 = tg.id1 and t.id2 = tg.id2 and t.id3 = tg.id3)
when matched then update
  set aggregated_col_1 = t.aggregate_col_1,

      aggregated_col_2 = t.aggregate_col_2, 
      ...

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 06 2015 - 22:47:30 CEST