Re: MERGE statement with parallel DML enabled deadlocks itself

From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Wed, 06 May 2015 11:44:01 +0200
Message-ID: <5549E261.30608_at_mgm-tp.com>

> could you post destination table ddl and merge text?

Unfortunately I'm not allowed to do disclose the table structure or the actual merge statement.

But the basic structure is this:

create table target_table
(
  part_key number,

  id1   number, 
  id2   number, 
  id3   number,

  ... 120 more columns that store aggregated numbers for different intervals.   Most of them being number(38,6), some date columns )
partition by list (part_key) ...;

There is a unique local index on (part_key, id1, id2, id3)

create table base_table_1
(
  part_key number,

  id1   number, 
  id2   number, 
  id3   number,

  approx. 70 more columns. Most of them are date columns   defining the intervals on which the aggregation is done. )
partition by list (part_key) ...;

There is a unique local index on (part_key, id1, id2, id3)

create table data_table_1
(
  part_key number,

  id1   number, 
  id2   number, 
  id3   number,

  ....
  value_date date,
  value number
)
partition by list (part_key) ...;

The merge statement does something like this:

merge into target_table tg
using
(

  select bt.part_key, 
         bt.id1, 
         bt.id2, 
         bt.id3, 
         sum(case when dt.value_date >= bt.date_column_1 >  then dt.value end) as aggregate_col_1,
         sum(case when dt.value_date >= bt.date_column_2 >  then dt.value end) as aggregate_col_2,
         ... many more conditional aggregates ... 

  from base_table_1 bt
    join data_table_1 dt on dt.part_key = bt.part_key and dt.id1 = bt.id1 and dt.id2 = bt.id2 and dt.id3 = bt.id3   group bt.part_key, bt.id1, bt.id2, bt.id3  

) 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, 
       ...

The above is an shortened/obfuscated sketch on what the statement is doing. It is not meant to be syntactically correct, it's just there to illustrate the process.

target_table and base_table_1 contain approx 14 million rows. The data_table contains approx. 60 million rows.

Before running the statements (we have several of that type) we are doing:

alter session force parallel query parallel x; alter session force parallel dml parallel x;

Regards
Thomas  

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

Received on Wed May 06 2015 - 11:44:01 CEST