Re: MERGE statement with parallel DML enabled deadlocks itself
- From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 06 May 2015 12:21:29 +0200
Sayan Sergeevich Malakshinov schrieb am 06.05.2015 um 12:08:
I suspect now that the problem is in the pq distribution and
groupby-placement.
Could you show real execution plan?
This is the plan as it was dumped in the trace file:
--------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id | Operation | Name |
Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib | Pstart| Pstop |
--------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0 | MERGE STATEMENT | |
| | 68K | | | | | | |
| 1 | PX COORDINATOR | |
| | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10005 |
3541K | 4084M | 68K | 00:08:17 |:Q1005| P->S |QC (RANDOM)| | |
| 3 | MERGE | TARGET_TABLE |
| | | |:Q1005| PCWP | | | |
| 4 | PX RECEIVE | |
3541K | 4084M | 68K | 00:08:17 |:Q1005| PCWP | | | |
| 5 | PX SEND HYBRID (ROWID PKEY) | :TQ10004 |
3541K | 4084M | 68K | 00:08:17 |:Q1004| P->P |HYBRID (ROWID PKEY)| |
| 6 | VIEW | |
| | | |:Q1004| PCWP | | | |
| 7 | HASH JOIN BUFFERED | |
3541K | 4084M | 68K | 00:08:17 |:Q1004| PCWP | | | |
| 8 | PX RECEIVE | |
4149K | 1301M | 8189 | 00:00:59 |:Q1004| PCWP | | | |
| 9 | PX SEND HASH | :TQ10002 |
4149K | 1301M | 8189 | 00:00:59 |:Q1002| P->P |HASH | | |
| 10 | PX BLOCK ITERATOR | |
4149K | 1301M | 8189 | 00:00:59 |:Q1002| PCWC | | 1 | 33 |
| 11 | TABLE ACCESS FULL | TARGET_TABLE |
4149K | 1301M | 8189 | 00:00:59 |:Q1002| PCWP | | 1 | 33 |
| 12 | PX RECEIVE | |
3541K | 2974M | 60K | 00:07:18 |:Q1004| PCWP | | | |
| 13 | PX SEND HASH | :TQ10003 |
3541K | 2974M | 60K | 00:07:18 |:Q1003| P->P |HASH | | |
| 14 | VIEW | |
3541K | 2974M | 60K | 00:07:18 |:Q1003| PCWP | | | |
| 15 | SORT GROUP BY | |
3541K | 1127M | 60K | 00:07:18 |:Q1003| PCWP | | | |
| 16 | PX RECEIVE | |
3541K | 1127M | 8057 | 00:00:58 |:Q1003| PCWP | | | |
| 17 | PX SEND HASH | :TQ10001 |
3541K | 1127M | 8057 | 00:00:58 |:Q1001| P->P |HASH | | |
| 18 | HASH JOIN RIGHT OUTER | |
3541K | 1127M | 8057 | 00:00:58 |:Q1001| PCWP | | | |
| 19 | PX RECEIVE | |
2989K | 96M | 690 | 00:00:05 |:Q1001| PCWP | | | |
| 20 | PX SEND BROADCAST | :TQ10000 |
2989K | 96M | 690 | 00:00:05 |:Q1000| P->P |BROADCAST | | |
| 21 | PX BLOCK ITERATOR | |
2989K | 96M | 690 | 00:00:05 |:Q1000| PCWC | | 1 | 33 |
| 22 | TABLE ACCESS FULL | DATA_TABLE |
2989K | 96M | 690 | 00:00:05 |:Q1000| PCWP | | 1 | 33 |
| 23 | PX BLOCK ITERATOR | |
3541K | 1013M | 7348 | 00:00:53 |:Q1001| PCWC | | 1 | 33 |
| 24 | TABLE ACCESS FULL | BASE_TABLE |
3541K | 1013M | 7348 | 00:00:53 |:Q1001| PCWP | | 1 | 33 |
--------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
In case the formatting is not retained, the full plan can be accessed here:
http://textuploader.com/g62h
Thomas
--
//www.freelists.org/webpage/oracle-l
Other related posts:
