Re: MERGE statement with parallel DML enabled deadlocks itself

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_at_mgm-tp.com>:

> 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
>
>
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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