Re: MERGE statement with parallel DML enabled deadlocks itself

Dave.Noble_at_wolseley.com schrieb am 06.05.2015 um 11:10:
> Are you using an EXCEPTIONS clause. There is a known bug re Parallel MERGE into EXCEPTIONS clause
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Kellerer
> Sent: 06 May 2015 10:05
> To: oracle-l_at_freelists.org
> Subject: MERGE statement with parallel DML enabled deadlocks itself
>
> Hello list,
>
> we have a fairly large MERGE statement that does some aggregation on a reporting table with about 12 millions rows. The MERGE only has an UPDATE part, but no INSERT, no primary key or foreign key columns are changed by the UPDATE (only columns holding aggregates)
>
> The target table is list partitioned on one column with 33 partitions. The SELECT statement inside the merge joins two tables that are partitioned in the same way as the target table.
>
> On the customer site, this MERGE is executed with parallel DML and parallel Query enabled and this MERGE repeatedly errors out with a ORA-00060 (although not always). With similar (but not identical) data in our development environments (but the same base Oracle version) this does not happen.
>
> The deadlock also doesn't happen all the time (which isn't really surprising though)
>
> I found some articles on the Oracle support site reporting problems with parallel DML and deferred segment creation. And indeed for some partitions no segments where yet created. So we forced the creation of the segments using "modify partition ... ALLOCATE EXTENT" but unfortunately this did not change anything.
>
> When looking at the deadlock graphs from the trace file, the blocking sessions are all sessions that are initiated by the parallel DML/Query (the process names are all P000 through P003, the statement is running with parallel 4).
>
> The trace files contain a total of up to 65 deadlock graphs and all blocked sessions are waiting for "enq: TX - row lock contention" with one of the P00x sessions being the blocking session.
>
> I have searched the Oracle support site, but nothing pops up that seems to fit this situtation
>
> The Oracle version is 11.2.0.4 running on Windows Server (not sure which Windows version though)
>
> Any ideas?
>
> Regards
> Thomas Kellerer
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

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