Thomas
Just to rule out a possible deadlock situation I have observed recently
which is due to an update of the partition key which causes the partition
to move and locked the whole table as a consequence.
You are not updating the partition key? right?
Best regards
Mohamed Houri
2015-05-06 11:04 GMT+02:00 Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>:
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
