I had a situation where a parallel dml encountered deadlocks on itself, the
cause was insufficient space in the block to expand the ITL slots. Rebuilding
the table with sufficient ITL slots to support all the parallel threads solved
our issue. I'd increase it to the max number of parallel sessions any query
against the table uses.
Hope this helps,
Ken
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Thomas Kellerer
Sent: Wednesday, May 06, 2015 5:05 AM
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
--
//www.freelists.org/webpage/oracle-l
