오라클 19c 파티션 테이블 append insert 시 lock 확인

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c 파티션 테이블 append insert 시 lock 확인

만약 파티션 테이블에 일반 테이블의 데이터를 통으로 밀어 넣어야할 때

일반 insert 로 데이터를 넣게 되면 속도가 느림

이때 append 힌트를 사용해 insert 를 하면 빠른데 일반테이블에 append 힌트를 사용하여 insert를 하게되면

해당 테이블에 lock이 걸려 다른 트랜젝션에서 dml을 할수 없음

파티션 테이블의 경우 해당 파티션에만 lock이 걸려 다른 파티션의 dml에 영향을 주지 않고

해당 파티션만 사용하지 못하는 상태에서 append 힌트를 이용해 빠른 insert가 가능함

테스트를 통해 확인해봄 

 

 

세션 정보 확인

SQL> select sid from v$mystat where rownum<=1;
 
       SID
----------
       300

 

 

파티션 테이블 생성

SQL>
drop table ptable purge;
create table ptable
(
col1 varchar2(8),
col2 number
)
tablespace users
partition by range (col1)
(
partition p202101 values less than ('202102'),
partition p202102 values less than ('202103'),
partition p202103 values less than ('202104'),
partition p202104 values less than ('202105'),
partition p202105 values less than ('202106'),
partition p202106 values less than ('202107'),
partition p202107 values less than ('202108'),
partition p202108 values less than ('202109'),
partition p202109 values less than ('202110'),
partition p202110 values less than ('202111'),
partition p202111 values less than ('202112'),
partition p202112 values less than ('202113'),
partition p_max values less than (maxvalue)
);

 

 

파티션 테이블 샘플 데이터 삽입

SQL>
insert into PTABLE values ('202101',1);
insert into PTABLE values ('202102',1);
insert into PTABLE values ('202102',1);
insert into PTABLE values ('202103',1);
insert into PTABLE values ('202104',1);
insert into PTABLE values ('202105',1);
commit;

 

 

동일구조 일반 샘플 테이블 생성

SQL>
drop table ntable purge;
create table ntable
(
col1 varchar2(8),
col2 number
)
tablespace users;

 

 

샘플 데이터 삽입(NTABLE 에는 모두 202101 데이터만 들어있음)

SQL>
insert into NTABLE values ('202101',1);
insert into NTABLE values ('202101',2);
insert into NTABLE values ('202101',3);
insert into NTABLE values ('202101',4);
insert into NTABLE values ('202101',5);
insert into NTABLE values ('202101',6);
insert into NTABLE values ('202101',7);
insert into NTABLE values ('202101',8);
insert into NTABLE values ('202101',9);
insert into NTABLE values ('202101',10);
commit;

 

 

세션레벨 파라미터 변경

SQL> 
alter session enable parallel dml;
 
--선택사항--
alter session set workarea_size_policy = manual;
alter session set sort_area_size = 2000000000;
show parameter workarea_size_policy 
show parameter sort_area_size
--선택사항--

 

 

append parallel 힌트 사용하여 특정 파티션으로 insert

SQL>
insert /*+ append parallel(b 2) */ into ptable partition (p202101) b
select /*+ full(a) parallel(a 2) */ * from ntable a;

 

 

lock 확인

SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1 
and sid = 300
--and TYPE in ('TX','TM')
order by sid 
;
 
    SID TY      ID1         ID2      LMODE    REQUEST        BLOCK USN                 SLOT     SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
    300 AE      134           0      4         0        0 0                  134       0
    300 TM    23784           0      3         0        0 0                23784       0
    300 TM    23785           0      6         0        0 0                23785       0
    300 TX     393229         530      6         0        0 6                   13     530
 

 

 

id1 object id 확인

SQL>
col object_name for a15
col subobject_name for a15
select object_id, object_name, subobject_name
from dba_objects
where object_id in (23784, 23785, 393229);
 
 OBJECT_ID OBJECT_NAME       SUBOBJECT_NAME
---------- --------------- ---------------
     23784 PTABLE
     23785 PTABLE       P202101

23784 => PTABLE : TM Lock mode 3(row-X SX)

23785 => PTABLE P202101 파티션 : TM Lock mode 6(exclusive X)

해당 파티션에만 lock 이 걸림

 

 

다른 세션 dml확인

먼저 다른 파티션에 insert

SQL>
insert into PTABLE values ('202105',11);
 
1 row created.

 

 

다른 파티션에 update

SQL>
update PTABLE set col2 = 12
where col1 = '202105' and col2 = 11;
 
1 row updated.

 

 

다른 파티션에 delete

SQL>
delete PTABLE 
where col1 = '202105' and col2 = 12;
 
1 row deleted.

다른 파티션에 대해서 dml들이 모두 동작함

 

 

append 중인 파티션에 insert

SQL>
insert into PTABLE values ('202101',11);
(lock 대기)

append 중인 파티션에 insert 시 lock로 대기함

append 세션에서 commit 이후 이 세션에서 insert 성공함

 

 

DDL 테스트

DDL 테스트
drop table => ORA-00054 발생
SQL> drop table ptable;
 
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
 
truncate partition => ORA-00054 발생
SQL> alter table ptable truncate partition p202101;
 
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
 
split partition => 가능
SQL> alter table ptable split partition p_max at ('202305') into (partition p202206, partition p_max);
 
Table altered.

 

 

결론 : 

append 힌트를 이용해 특정 파티션에 데이터 insert 시

해당 파티션에만 lock이 걸려 다른 파티션의 dml에 영향을 주지 않고

(해당 파티션만 사용하지 못하는 상태에서) 빠른 insert가 가능함

 

 

참조 :

https://positivemh.tistory.com/842

오라클 11gR2 파티션 변경(split, drop, add) 시 커서 상태 변화 확인

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-LOCK.html#GUID-87D76889-832C-4BFC-B8B0-154A22721781