The main reason I decided to have a blog was to share all the small things I learn every day while experimenting but I’ve to admit I fell short on my initial intention, hopefully I’ll do better in the coming months, starting with this super-quick post.
I always knew that concurrent (aka multiple sessions at the same time) INSERT /*+ APPEND */ into the same target table was not possible even if the target table was partitioned. Here is an example of a SQL
create table test_target (n1 number, n2 number) partition by range(n1) (partition p1 values less than (5), partition p2 values less than (10)); insert /*+ append */ into test_target select mod(rownum,5), rownum from dual connect by rownum <= 10;
Reason for the concurrency being not feasible is the first session acquires an enqueue TM in X mode on the whole table (so every other session will have to wait) even if the inserted data only goes into a specific partition.
In the following extract SID 306 (my session) is holding the enq TM in X mode on object id 138542, which is the table even though only partition P1 has been populated.
SQL> select * from v$lock where sid = 306;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
306 TX 131102 11102 6 0 29 0 0
306 TM 138542 0 6 0 29 0 3
306 AE 133 0 4 0 86 0 3
SQL> select object_id, object_name, object_type from user_objects where object_id = 138542;
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- --------------- -----------------------
138542 TEST_TARGET TABLE
If in another session (SID 186) I try to run an INSERT /*+ APPEND */ to populated only P2 then the execution will sit and wait for SID 306 to release the enqueue on object id 138542, as shown in the first row from V$LOCK where 186 wants to grab an enq TM in X mode for object id 138542
SQL> insert /*+ append */ into test_target select mod(rownum,5)+5, rownum from dual connect by rownum select * from v$lock where sid in (306,186) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
186 TM 138542 0 0 6 94 0 3
186 AE 133 0 4 0 104 0 3
306 AE 133 0 4 0 640 0 3
306 TM 138542 0 6 0 583 1 3
306 TX 131102 11102 6 0 583 0 0
Nothing new so far, it confirms what I expected.
What I didn’t know is that if “extended partition syntax” is used to specify the target partition (and if you specify the wrong one you get a correct ORA-14401) then the X lock is held on the partition and not the table (it’s held in SX on the table).
Here is the example
SID 306
insert /*+ append */ into test_target partition (p1) select mod(rownum,5), rownum from dual connect by rownum <= 10;
SID 186
insert /*+ append */ into test_target partition (p2) select mod(rownum,5)+5, rownum from dual connect by rownum select * from v$lock where sid in (306,186) and type = 'TM' order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
186 TM 138542 0 3 0 11 0 3
186 TM 138544 0 6 0 11 0 3
306 TM 138543 0 6 0 35 0 3
306 TM 138542 0 3 0 35 0 3
SQL> select object_id, object_name, object_type from user_objects where object_id in (138542, 138543, 138544);
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------- -----------------------
138544 TEST_TARGET TABLE PARTITION
138543 TEST_TARGET TABLE PARTITION
138542 TEST_TARGET TABLE
So in case you know in advance where the data is going (which is generally true specially for range/interval partition) then you can have multiple concurrent loads into the same target table.
I’m not recommending it, just saying it’s possible 😀
Mauro Pagano's Blog 
June 5, 2019 at 11:49 pm
Very good article! I have tried your solution but it seems that I continue to have locks.
If the insert into append with partition start from a select on the same table is the lock related to the table of the select?
Thanks
LikeLike
June 7, 2019 at 5:57 am
Does the select part access a single partition too? It would be easier if you can just provide a few lines long testcase (you can use the one in the blog post as starting point, just use a physical table instead of dual)
LikeLike
June 8, 2019 at 1:59 am
Hi Mauro, below an example.
I have a target table T_TGT created with NOLOGGING option and with a partition by LIST on field PARTITION_ID.
I have in all queries the same source table T_SRC in join with a table that contains a list of condition to be applied (T_COND_1, T_COND_2, T_COND_3).
So I insert in multithread in tgt table always starting from T_SRC (that is not partitioned because if a temp table that contain only data in scope) in join with a different table with different conditions (T_COND_1, T_COND_2, T_COND_3).
First query:
INSERT INTO /*+ APPEND */ T_TGT PARTITION (P1)
(
PARTITION_ID,
COL1,
COL2,
COL3
)
SELECT /*+ LEADING(T1 T2) USE_NL(T1) */
‘1’
,T1.COL1
,T1.COL2
,T1.COL3
FROM
T_SRC T1
, T_COND_1 T2
WHERE
( (1=0) OR T2.PK = 1 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FA’))
OR T2.PK = 2 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FB’)))
Second query:
INSERT INTO /*+ APPEND */ T_TGT PARTITION (P2)
(
PARTITION_ID,
COL1,
COL2,
COL3
)
SELECT /*+ LEADING(T1 T2) USE_NL(T1) */
‘2’
,T1.COL1
,T1.COL2
,T1.COL3
FROM
T_SRC T1
, T_COND_2 T2
WHERE
( (1=0) OR T2.PK = 1 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FA’))
OR T2.PK = 2 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FB’)))
Third query:
INSERT INTO /*+ APPEND */ T_TGT PARTITION (P3)
(
PARTITION_ID,
COL1,
COL2,
COL3
)
SELECT /*+ LEADING(T1 T2) USE_NL(T1) */
‘3’
,T1.COL1
,T1.COL2
,T1.COL3
FROM
T_SRC T1
, T_COND_3 T2
WHERE
( (1=0) OR T2.PK = 1 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FA’))
OR T2.PK = 2 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FB’)))
From my analysis (unfortunately i have not DBA grants so i not see $V_LOCK table) it seems that one query wait the other one before executed.
I have also tried to add PARALLEL hint near append in the INSERT INTO CLAUSE and also in SELECT CLAUSE and enabled DML PARALLEL. In this case performance are a bit better, but i want to understand your suggestion and why in this case your suggestion seems to not work correctly.
Many thanks
LikeLike
June 12, 2019 at 10:29 pm
Ciao Andrea, sorry for the delay.
I cannot reproduce your problem, below is my TC (a simplified version of yours)
DDL:
create table t_tgt (partition_id varchar2(1), col1 number, col2 number, col3 number) partition by list (partition_id) (partition p1 values (‘1’), partition p2 values (‘2’), partition p3 values (‘3’));
I’ll then lock exclusively a single partition (p1)
MPAGANO@orcl> lock table t_tgt partition (p1) in exclusive mode;
MPAGANO@orcl> select * from v$lock where sid = 153;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
—————- —————- ———- — ———- ———- ———- ———- ———- ———- ———-
0000000074BE9B58 0000000074BE9BD8 153 AE 134 0 4 0 12751 0 0
00007F914289ABE8 00007F914289AC58 153 TM 112013 0 6 0 9 0 0 @show_obj 112012,112013
OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
—————————— ———- ————– —————————— —————————— ——————————
MPAGANO 112012 T_TGT TABLE
MPAGANO 112013 112013 T_TGT P1 TABLE PARTITION
From another session (20) the insert into P2 works just fine (P1 is locked)
MPAGANO@orcl> INSERT INTO /*+ APPEND */ T_TGT PARTITION (P2) (PARTITION_ID,COL1,COL2,COL3) SELECT ‘2’ ,1 COL1 ,2 COL2 ,3 COL3 FROM dual;
1 row created.
Elapsed: 00:00:01.65
while the insert into P1 waits forever waiting on the enq TM on the partition
MPAGANO@orcl> INSERT INTO /*+ APPEND */ T_TGT PARTITION (P1) (PARTITION_ID,COL1,COL2,COL3) SELECT ‘1’ ,1 COL1 ,2 COL2 ,3 COL3 FROM dual;
… waiting …
MPAGANO@orcl> select * from v$lock where sid in (153,20) order by sid, type;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
—————- —————- ———- — ———- ———- ———- ———- ———- ———- ———-
0000000074BE9D88 0000000074BE9E08 20 AE 134 0 4 0 273 0 0
00007F9142752250 00007F91427522C0 20 TM 112013 0 0 3 234 0 0 @show_sess mpagano
INST_ID SID SERIAL# STATE SEQ# EVENT P1 P2 P3 SECONDS_IN_WAIT SQL_ID SQL_EXEC_ID
———- ———- ———- ——————- ———- —————————————- ———- ———- ———- ————— ————- ———–
1 20 31225 WAITING 857 enq: TM – contention 1414332419 112013 0 255 54y6094ksq801 16777216 <—–
1 153 15094 WAITED SHORT TIME 487 SQL*Net message to client 1413697536 1 0 0 2j461wqch7tb8 16777216
So if you can't get this to work it sounds like there might be something else in the real-world case that is preventing it, unrelated from what just shown above
Cheers,
Mauro
LikeLike
June 12, 2019 at 10:31 pm
Just realized my previous update is completely unreadable, copy&paste into a text editor with fixed width chars and it should be readable
LikeLike