When I am running 2 merge on different partitions of same table in parallel, one is going in wait state.
Below is the scenario.
Table A
columns - Acct_skey, date_skey, prod_code, element_id,SYS_SRC_SOR_ID,DDA_SKEY, partition_name, ... measure columns around 300
Primary key - Acct_skey, date_skey, prod_code, element_id,SYS_SRC_SOR_ID,DDA_SKEY, partition_name
Partitioned on partition_name - values('Val1','Val2','Val3') -- list partitioned
Subpartitioned on - date_skey - range partitioned
Table B
columns - Acct_skey, date_skey, prod_code, element_id,SYS_SRC_SOR_ID,DDA_SKEY, partition_name, ... measure columns around 300
Primary key - Acct_skey, date_skey, prod_code, element_id,SYS_SRC_SOR_ID,DDA_SKEY, partition_name
Partitioned on partition_name - values('Val1','Val2','Val3') -- list partitioned
Subpartitioned on - date_skey - range partitioned
Table C
Columns - Acct_skey,date_skey,partition_name,metrics_category
Primary key - Acct_skey, date_skey, partition_name
Partitioned on partition_name - values('Val1','Val2','Val3') -- list partitioned
Table D
Column - LOOKUP_TYPE_DESC,LOOKUP_VALUE,LOOKUP_CODE,LOOKUP_TYPE
Primary key - LOOKUP_VALUE,LOOKUP_CODE,LOOKUP_TYPE
Below are the merge statements which i want to run in parallel:
--------------------------------Merge 1----------------------------------
MERGE INTO A TT
USING (SELECT *
FROM (SELECT A.ACCT_SKEY,
A.Date_skey,
A.PARTITION_NAME,
A.PROD_CODE,
A.ELEMENT_ID,
A.SYS_SRC_SOR_ID,
A.DDA_SKEY,
(A.N_TAX) AS T_1469608103325_0,
(CASE
WHEN ( ( ( (D.LOOKUP_TYPE_DESC =
'DE')))
OR ( ( (D.LOOKUP_TYPE_DESC =
'TM'))))
THEN
10
ELSE
11
END)
AS COND_1469608103325_10,
( COALESCE (
CASE
WHEN 1 = 1
THEN
A.n_nibt
ELSE
NULL
END,
0)
* COALESCE (
CASE
WHEN 1 = 1
THEN
A.n_tax_rate
ELSE
NULL
END,
0))
AS EXP_1469608103325_10,
(A.N_TAX)
AS EXP_1469608103325_11,
(A.N_EQUITY)
AS T_1469608103325_1,
(CASE
WHEN ( ( ( (D.LOOKUP_TYPE_DESC =
'TM'))))
THEN
20
ELSE
21
END)
AS COND_1469608103325_20,
(COALESCE (
CASE
WHEN 1 = 1
THEN
A.n_oprisk_capital
ELSE
NULL
END,
0))
AS EXP_1469608103325_20,
(A.N_EQUITY)
AS EXP_1469608103325_21
FROM C
INNER JOIN B
ON B.ACCT_SKEY =
C.ACCT_SKEY
AND B.PARTITION_NAME =
C.PARTITION_NAME
AND B.Date_skey =
C.DATE_SKEY
INNER JOIN A
ON B.ACCT_SKEY =
A.ACCT_SKEY
AND B.Date_skey =
A.Date_skey
AND B.ELEMENT_ID =
A.ELEMENT_ID
AND B.PROD_CODE =
A.PROD_CODE
AND B.DDA_SKEY =
A.DDA_SKEY
AND B.SYS_SRC_SOR_ID =
A.SYS_SRC_SOR_ID
AND B.PARTITION_NAME =
A.PARTITION_NAME
INNER JOIN D
ON D.LOOKUP_VALUE =
B.SYS_SRC_SOR_ID
WHERE ( D.LOOKUP_TYPE_DESC IN
('DE', 'TM')
AND D.LOOKUP_TYPE =
'B'
AND D.LOOKUP_CODE =
'SYS_SRC_SOR_ID')
AND ( ( ( (C.PARTITION_NAME =
'Val1')))))
WHERE ( (COND_1469608103325_10 <> 11)
OR (COND_1469608103325_20 <> 21))) SS
ON ( TT.ACCT_SKEY = SS.ACCT_SKEY
AND TT.Date_skey = SS.Date_skey
AND TT.PARTITION_NAME = SS.PARTITION_NAME
AND TT.PROD_CODE = SS.PROD_CODE
AND TT.ELEMENT_ID = SS.ELEMENT_ID
AND TT.SYS_SRC_SOR_ID = SS.SYS_SRC_SOR_ID
AND TT.DDA_SKEY = SS.DDA_SKEY)
WHEN MATCHED
THEN
UPDATE SET
TT.N_TAX =
CASE
WHEN COND_1469608103325_10 = 10 THEN EXP_1469608103325_10
ELSE EXP_1469608103325_11
END,
TT.N_EQUITY =
CASE
WHEN COND_1469608103325_20 = 20 THEN EXP_1469608103325_20
ELSE EXP_1469608103325_21
END
--------------------------------Merge 2-------------------------------------------------
MERGE INTO A TT
USING (SELECT *
FROM (SELECT A.ACCT_SKEY,
A.Date_skey,
A.PARTITION_NAME,
A.PROD_CODE,
A.ELEMENT_ID,
A.SYS_SRC_SOR_ID,
A.DDA_SKEY,
(A.N_TAX) AS T_1469608103325_0,
(CASE
WHEN ( ( ( (D.LOOKUP_TYPE_DESC =
'DE')))
OR ( ( (D.LOOKUP_TYPE_DESC =
'TM'))))
THEN
10
ELSE
11
END)
AS COND_1469608103325_10,
( COALESCE (
CASE
WHEN 1 = 1
THEN
A.n_nibt
ELSE
NULL
END,
0)
* COALESCE (
CASE
WHEN 1 = 1
THEN
A.n_tax_rate
ELSE
NULL
END,
0))
AS EXP_1469608103325_10,
(A.N_TAX)
AS EXP_1469608103325_11,
(A.N_EQUITY)
AS T_1469608103325_1,
(CASE
WHEN ( ( ( (D.LOOKUP_TYPE_DESC =
'TM'))))
THEN
20
ELSE
21
END)
AS COND_1469608103325_20,
(COALESCE (
CASE
WHEN 1 = 1
THEN
A.n_oprisk_capital
ELSE
NULL
END,
0))
AS EXP_1469608103325_20,
(A.N_EQUITY)
AS EXP_1469608103325_21
FROM C
INNER JOIN B
ON B.ACCT_SKEY =
C.ACCT_SKEY
AND B.PARTITION_NAME =
C.PARTITION_NAME
AND B.Date_skey =
C.DATE_SKEY
INNER JOIN A
ON B.ACCT_SKEY =
A.ACCT_SKEY
AND B.Date_skey =
A.Date_skey
AND B.ELEMENT_ID =
A.ELEMENT_ID
AND B.PROD_CODE =
A.PROD_CODE
AND B.DDA_SKEY =
A.DDA_SKEY
AND B.SYS_SRC_SOR_ID =
A.SYS_SRC_SOR_ID
AND B.PARTITION_NAME =
A.PARTITION_NAME
INNER JOIN D
ON D.LOOKUP_VALUE =
B.SYS_SRC_SOR_ID
WHERE ( D.LOOKUP_TYPE_DESC IN
('DE', 'TM')
AND D.LOOKUP_TYPE =
'B'
AND D.LOOKUP_CODE =
'SYS_SRC_SOR_ID')
AND ( ( ( (C.PARTITION_NAME =
'Val2')))))
WHERE ( (COND_1469608103325_10 <> 11)
OR (COND_1469608103325_20 <> 21))) SS
ON ( TT.ACCT_SKEY = SS.ACCT_SKEY
AND TT.Date_skey = SS.Date_skey
AND TT.PARTITION_NAME = SS.PARTITION_NAME
AND TT.PROD_CODE = SS.PROD_CODE
AND TT.ELEMENT_ID = SS.ELEMENT_ID
AND TT.SYS_SRC_SOR_ID = SS.SYS_SRC_SOR_ID
AND TT.DDA_SKEY = SS.DDA_SKEY)
WHEN MATCHED
THEN
UPDATE SET
TT.N_TAX =
CASE
WHEN COND_1469608103325_10 = 10 THEN EXP_1469608103325_10
ELSE EXP_1469608103325_11
END,
TT.N_EQUITY =
CASE
WHEN COND_1469608103325_20 = 20 THEN EXP_1469608103325_20
ELSE EXP_1469608103325_21
END
You need to give a reproducible test case, because I can't get two merges where the input datasets are mutually exclusive cause an unexpected locking issue.
Here's my example:
SQL> create table T (
2 id int not null,
3 par varchar2(10) not null,
4 data int )
5 partition by list ( par )
6 (
7 partition p1 values ('A'),
8 partition p2 values ('B')
9 );
Table created.
SQL>
SQL>
SQL> insert into t
2 select rownum,
3 chr(ascii('A') + mod(rownum,2)),
4 rownum
5 from dual
6 connect by level <= 1000;
1000 rows created.
SQL>
SQL>
SQL> create table T1 (
2 id int not null,
3 par varchar2(10) not null,
4 data int )
5 /
Table created.
SQL>
SQL> insert into t1
2 select rownum+900,
3 chr(ascii('A') + mod(rownum,2)),
4 -rownum
5 from dual
6 connect by level <= 200;
200 rows created.
SQL>
SQL> commit;
Commit complete.
--
-- Session 1
--
SQL> merge into t
2 using ( select * from t1 where par = 'A') t1
3 on ( t.id = t1.id )
4 when matched then
5 update set t.data = t1.data
6 when not matched then
7 insert (t.id,t.par,t.data)
8 values (t1.id,t1.par,t1.data);
100 rows merged.
--
-- Session 2
--
SQL> merge into t
2 using ( select * from t1 where par = 'B') t1
3 on ( t.id = t1.id )
4 when matched then
5 update set t.data = t1.data
6 when not matched then
7 insert (t.id,t.par,t.data)
8 values (t1.id,t1.par,t1.data);
100 rows merged.
SQL>
Check which lock(s) you are waiting on - it might be a foreign key issue, or something else.