Re: MERGE statement with parallel DML enabled deadlocks itself

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 8 May 2015 09:51:52 +0100
Message-ID: <38220C16BDBC49E4A701261AA708FF17_at_Primary>

Possibly repeated - sorry:

I'm not sure if the merge would work.
Is it legal to write "merge into (select * from target_table where p_key = 1) tg using ..." ? If not you MAY find that Oracle locks the target table in a way that stops you running merges concurrently because it doesn't know that the data to be changed will come from a single partition.

If it doesn't work, then could you run the code from a different account, creating a pair of synonyms (over views if necessary) in that account that say:
  schema_user.target_table (synonym) = target_owner.target_table partition (p1)
etc.

You'd need 33 accounts to do all 33 partitions at once, of course. It might work.
I think the problem must be lurking in the data and data distribution, though. If you can get pq_distribute(xxx, none, none) hints to work it may go away.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Thomas Kellerer" <thomas.kellerer_at_mgm-tp.com> To: <oracle-l_at_freelists.org> Sent: Thursday, May 07, 2015 8:03 AM Subject: Re: MERGE statement with parallel DML enabled deadlocks itself

| Sayan Sergeevich Malakshinov schrieb am 06.05.2015 um 13:41:
| > I think it would be better and more reliable to execute 33 merge
statements in parallel sessions/jobs each by own partition, like this:
| >
| > merge into target_table partition(p1) tg
| > using
| > (
| > select ...
| > from base_table_1 partition(p1) bt
| > left join data_table_1 partition(p1) dt
| > on ...
| > group by ...
| > ) t on (t.part_key = tg.part_key and t.id1 = tg.id1 and t.id2 = tg.id2
and t.id3 = tg.id3)
| > when matched then update
| > set aggregated_col_1 = t.aggregate_col_1,
| > aggregated_col_2 = t.aggregate_col_2,
| > ...

|
|

| Thanks for the suggestion.

|
| We can't really change the SQL statements to include the partition names
(that would require dynamic SQL and given the size of the statements this would not be feasible)
|
| What we _can_ do though, is to include the partition key in the where
clause (where part_key = 1).
| Due to partition pruning this should have the same effect, shouldn't it?

|

| Regards
| Thomas

|

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

|
|
|
|

| -----
| No virus found in this message.
| Checked by AVG - www.avg.com
| Version: 2015.0.5863 / Virus Database: 4342/9722 - Release Date: 05/08/15
|

---

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2015.0.5863 / Virus Database: 4342/9722 - Release Date: 05/08/15

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

Received on Fri May 08 2015 - 10:51:52 CEST