No, we are not using an EXCEPTIONS clause
Dave.Noble@xxxxxxxxxxxx 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Thomas Kellerer
Sent: 06 May 2015 10:05
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
