OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 파티션 테이블 impdp 시 테이블 및 파티션 lock 확인
오라클 19c 파티션 테이블 impdp 시 lock 확인
일반적으로 단일 테이블에 datapump impdp를 이용해 데이터를 넣게되면 해당 전체에 lock이 걸림
datapump는 direct path 방식으로 insert를 하기 때문에 lock이 발생하는 것인데
파티션 테이블의 특정 파티션에 impdp를 하면 lock이 어떻게 발생하는지 확인해봄
파티션 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL>
conn imsi/imsi
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)
);
|
파티션 테이블 샘플 데이터 삽입(원하는 만큼 실행)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF PTABLE%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
for j in 1..100 loop
FOR i IN 1..100000 LOOP
w_ins(i).col1 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09');
w_ins(i).col2 := ceil(dbms_random.value(1, 10000000));
END LOOP;
FORALL i in 1..100000 INSERT INTO PTABLE VALUES w_ins(i);
COMMIT;
end loop;
END;
/
PL/SQL procedure successfully completed.
|
파티션 별 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
SQL>
set lines 200 pages 1000
col owner for a10
col segment_name for a20
col tablespace_name for a20
col partition_name for a20
col segment_type for a30
select owner, tablespace_name, segment_name,partition_name,segment_type, round(bytes/1024/1024) mb
from dba_segments
where segment_name = 'PTABLE'
and segment_type in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4;
OWNER TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE MB
---------- -------------------- -------------------- -------------------- ------------------------------ ----------
IMSI USERS PTABLE P202101 TABLE PARTITION 80
IMSI USERS PTABLE P202102 TABLE PARTITION 152
IMSI USERS PTABLE P202103 TABLE PARTITION 152
IMSI USERS PTABLE P202104 TABLE PARTITION 152
IMSI USERS PTABLE P202105 TABLE PARTITION 152
IMSI USERS PTABLE P202106 TABLE PARTITION 152
IMSI USERS PTABLE P202107 TABLE PARTITION 152
IMSI USERS PTABLE P202108 TABLE PARTITION 152
IMSI USERS PTABLE P202109 TABLE PARTITION 152
IMSI USERS PTABLE P202110 TABLE PARTITION 152
IMSI USERS PTABLE P202111 TABLE PARTITION 152
IMSI USERS PTABLE P202112 TABLE PARTITION 80
12 rows selected.
|
datapump용 directory 생성
|
1
2
3
4
|
$ mkdir -p /home/oracle/partpump
SQL> create directory partpump as '/home/oracle/partpump';
Directory created.
|
lock 조회 쿼리 저장
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
col object_name for a20
col subobject_name for a20
select sid, type, o.object_name, o.subobject_name, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
from v$lock v, dba_objects o
where 1=1
and TYPE in ('TX','TM')
and v.id1 = o.object_id(+)
--and sid = 300
order by sid, type;
SQL> save lock.sql
Created file lock.sql
|
expdp로 P202105 파티션 export
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
$ expdp system/oracle directory=partpump dumpfile=part_dump.dmp logfile=part_dump.log tables=IMSI.PTABLE:P202105
Export: Release 19.0.0.0.0 - Production on Wed Jan 10 16:44:58 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=partpump dumpfile=part_dump.dmp logfile=part_dump.log tables=IMSI.PTABLE:P202105
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "IMSI"."PTABLE":"P202105" 117.3 MB 7283184 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/partpump/part_dump.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 10 16:45:10 2024 elapsed 0 00:00:11
|
impdp로 P202105 파티션에 import
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ impdp system/oracle directory=partpump dumpfile=part_dump.dmp logfile=part_dump_impdp.log tables=IMSI.PTABLE:P202105 table_exists_action=append
Import: Release 19.0.0.0.0 - Production on Wed Jan 10 16:58:16 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=partpump dumpfile=part_dump.dmp logfile=part_dump_impdp.log tables=IMSI.PTABLE:P202105 table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "IMSI"."PTABLE" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "IMSI"."PTABLE":"P202105" 117.3 MB 7283184 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 10 16:58:24 2024 elapsed 0 00:00:07
|
impdp 시 다른 세션에서 lock 확인
|
1
2
3
4
5
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
273 TM PTABLE 44446 0 6 0 0 0 44446
273 TX 393242 982 6 0 0 6 26
|
테이블에 LMODE 6 TM LOCK이 생김
impdp시 impdp 대상 파티션에 dml 테스트
*dml 테스트 시 여러번 테스트해서 impdp 세션의 sid 및 TX id1이 다 다름
insert
|
1
2
|
SQL> insert into ptable partition (p202105) values ('202105',1);
(대기중)
|
insert 시 lock 조회
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE 44446 0 0 3 0 0 44446
284 TM PTABLE 44446 0 6 0 1 0 44446
284 TX 65566 953 6 0 0 1 30
|
impdp 세션(284)에 의해 insert 세션(51)이 대기하고 있음
impdp 완료 후 lock 조회(insert가 수행되고 commit 전 상태)
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE P202105 44451 0 3 0 0 0 44451
51 TM PTABLE 44446 0 3 0 0 0 44446
51 TX 458784 937 6 0 0 7 32
|
PTABLE 테이블과 P202105 파티션 모두에 TM lock LMODE 3 가 잡혀있음
update 테스트전 rollback;
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
update
|
1
2
3
4
5
|
SQL>
update ptable partition (p202105)
set col1='202105', col2 = 9999
where col1='202105' and col2 = 582205;
(대기중)
|
update시 lock 조회
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE 44446 0 0 3 0 0 44446
274 TM PTABLE 44446 0 6 0 1 0 44446
274 TX 196609 945 6 0 0 3 1
|
impdp 세션(274)에 의해 update 세션(51)이 대기하고 있음
impdp 완료 후 lock 조회(update가 수행되고 commit 전 상태)
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE P202105 44451 0 3 0 0 0 44451
51 TM PTABLE 44446 0 3 0 0 0 44446
51 TX 524306 1039 6 0 0 8 18
|
PTABLE 테이블과 P202105 파티션 모두에 TM lock LMODE 3 가 잡혀있음
delete 테스트전 rollback;
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
delete
|
1
2
3
4
|
SQL>
delete ptable partition (p202105)
where col1='202105' and col2 = 582205;
(대기중)
|
delete시 lock 조회
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
31 TM PTABLE 44446 0 6 0 1 0 44446
31 TX 524300 1040 6 0 0 8 12
51 TM PTABLE 44446 0 0 3 0 0 44446
|
impdp 세션(31)에 의해 delete 세션(51)이 대기하고 있음
impdp 완료 후 lock 조회(delete가 수행되고 commit 전 상태)
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE P202105 44451 0 3 0 0 0 44451
51 TM PTABLE 44446 0 3 0 0 0 44446
51 TX 327701 1013 6 0 0 5 21
|
PTABLE 테이블과 P202105 파티션 모두에 TM lock LMODE 3 가 잡혀있음
impdp 시 impdp 대상 파티션에 insert, update, delete 결론 : lock으로 인해 insert 되지 못함
impdp시 impdp 비대상 파티션에 dml 테스트
*dml 테스트 시 여러번 테스트해서 impdp 세션의 sid 및 TX id1이 다 다름
insert
|
1
2
|
SQL> insert into ptable partition (p202110) values ('202110',1);
(대기중)
|
insert시 lock 조회
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
31 TM PTABLE 44446 0 6 0 1 0 44446
31 TX 196638 954 6 0 0 3 30
51 TM PTABLE 44446 0 0 3 0 0 44446
|
impdp 세션(31)에 의해 insert 세션(51)이 대기하고 있음
impdp 완료 후 lock 조회(insert가 수행되고 commit 전 상태)
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE P202110 44456 0 3 0 0 0 44456
51 TM PTABLE 44446 0 3 0 0 0 44446
51 TX 655391 959 6 0 0 10 31
|
PTABLE 테이블과 P202110 파티션 모두에 TM lock LMODE 3 가 잡혀있음
update 테스트전 rollback;
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
update
|
1
2
3
4
5
|
SQL>
update ptable partition (p202110)
set col1='202110', col2 = 9999
where col1='202110' and col2 = 119816;
(대기중)
|
update시 lock 조회
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE 44446 0 0 3 0 0 44446
277 TM PTABLE 44446 0 6 0 1 0 44446
277 TX 524303 1065 6 0 0 8 15
|
impdp 세션(277)에 의해 update 세션(51)이 대기하고 있음
impdp 완료 후 lock 조회(update가 수행되고 commit 전 상태)
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE P202110 44456 0 3 0 0 0 44456
51 TM PTABLE 44446 0 3 0 0 0 44446
51 TX 327684 1033 6 0 0 5 4
|
PTABLE 테이블과 P202110 파티션 모두에 TM lock LMODE 3 가 잡혀있음
delete 테스트전 rollback;
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
delete
|
1
2
3
4
|
SQL>
delete ptable partition (p202110)
where col1='202110' and col2 = 119816;
(대기중)
|
delete시 lock 조회
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE 44446 0 0 3 0 0 44446
240 TM PTABLE 44446 0 6 0 1 0 44446
240 TX 327707 1022 6 0 0 5 27
|
impdp 세션(240)에 의해 delete 세션(51)이 대기하고 있음
impdp 완료 후 lock 조회(delete가 수행되고 commit 전 상태)
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE P202110 44456 0 3 0 0 0 44456
51 TM PTABLE 44446 0 3 0 0 0 44446
51 TX 655380 963 6 0 0 10 20
|
PTABLE 테이블과 P202110 파티션 모두에 TM lock LMODE 3 가 잡혀있음
impdp 시 impdp 비대상 파티션에 insert, update, delete 결론 : lock으로 인해 insert 되지 못함
이렇게 특정 파티션을 지정해서 impdp구문을 수행해도 테이블 전체에 TM lock LMODE 6(Exclusive)이 걸리는 이유는
datapump impdp시 append 힌트를 사용해 direct path 방식으로 import하기 때문임
실제 impdp 시 data를 insert 하는 쿼리를 ash를 통해 보면 아래와 같이 APPEND 힌트와 함께 수행됨
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000 LONG 99999
SELECT ash.sql_id,
sql_fulltext
FROM (SELECT sql_id
FROM (SELECT sql_id, count(sample_time)
FROM v$active_session_history
WHERE 1 = 1
AND sql_opname = 'INSERT'
AND module = 'Data Pump Worker'
AND sample_time BETWEEN SYSDATE - interval '1' minute and SYSDATE
GROUP BY sql_id
ORDER BY Count(sample_time) DESC)
WHERE ROWNUM <= 5) ash, v$sql s
WHERE ash.sql_id = s.sql_id;
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
f7xx5a34jj2v0 INSERT /*+ APPEND ENABLE_PARALLEL_DML PARALLEL("PTABLE",1)+*/
INTO RELATIONAL("IMSI"."PTABLE" NOT XMLTYPE) ("COL1", "COL2")
SELECT "COL1", "COL2"
FROM "SYSTEM"."ET$0115D60F0001" KU$
|
impdp 시 append 를 사용한 direct path 방식의 import를 하기 싫은 경우
data_options=disable_append_hint 옵션을 사용해서 impdp를 사용하면 됨
|
1
|
$ impdp system/oracle directory=partpump dumpfile=part_dump2.dmp logfile=part_dump_impdp2.log tables=IMSI.PTABLE:P202105 table_exists_action=append data_options=disable_append_hint
|
lock 확인
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
31 TM PTABLE P202105 44451 0 3 0 0 0 44451
31 TM PTABLE 44446 0 3 0 0 0 44446
31 TX 655383 964 6 0 0 10 23
|
테이블과 해당 파티션에만 lmode 3 으로 tm lock이 발생함
이때는 insert, update, delete 모두 가능함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
insert
SQL> insert into ptable partition (p202105) values ('202105',1);
1 row created.
update
SQL>
update ptable partition (p202105)
set col1='202105', col2 = 9999
where col1='202105' and col2 = 582205;
120 rows updated.
delete
SQL>
delete ptable partition (p202105)
where col1='202105' and col2 = 582205;
160 rows deleted.
|
dml 시 lock 확인
|
1
2
3
4
5
6
7
8
9
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE P202105 44451 0 3 0 0 0 44451
51 TM PTABLE 44446 0 3 0 0 0 44446
51 TX 262177 1008 6 0 0 4 33
31 TM PTABLE P202105 44451 0 3 0 0 0 44451
31 TM PTABLE 44446 0 3 0 0 0 44446
31 TX 655383 964 6 0 0 10 23
|
impdp 세션(31)에 의해 dml 세션(51)이 대기하지 않음
(lmode 3 tm lock 과 lmode 3 tm lock 는 호환이 되기 떄문에 서로 blocking이 발생하지 않음)
ash로 impdp시 쿼리 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000 LONG 99999
SELECT ash.sql_id,
sql_fulltext
FROM (SELECT sql_id
FROM (SELECT sql_id, count(sample_time)
FROM v$active_session_history
WHERE 1 = 1
AND sql_opname = 'INSERT'
AND module = 'Data Pump Worker'
AND sample_time BETWEEN SYSDATE - interval '1' minute and SYSDATE
GROUP BY sql_id
ORDER BY Count(sample_time) DESC)
WHERE ROWNUM <= 5) ash, v$sql s
WHERE ash.sql_id = s.sql_id;
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
0crv0ga7gpqsb INSERT /*+ ENABLE_PARALLEL_DML PARALLEL("PTABLE",1)+*/
INTO RELATIONAL("IMSI"."PTABLE" NOT XMLTYPE) ("COL1", "COL2") SELECT "COL1", "COL2"
FROM "SYSTEM"."ET$001FB2380001" KU$
|
APPEND 힌트가 없음을 확인할 수 있음
하지만 이 옵션을 사용한 경우 direct path 방식으로 import를 하지 않기 때문에 느릴수 있음
이 경우 data_options=trust_existing_table_partitions 옵션을 사용하면 append를 사용하면서
테이블 전체에 LMODE 6(Exclusive) TM lock 을 걸지않고, 해당 파티션에만 LMODE 6 TM lock 을 걸게끔 할수 있음
impdp 실행(data_options=trust_existing_table_partitions 옵션 사용)
|
1
|
$ impdp system/oracle directory=partpump dumpfile=part_dump2.dmp logfile=part_dump_impdp2.log tables=IMSI.PTABLE:P202105 table_exists_action=append data_options=trust_existing_table_partitions
|
lock 확인
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
62 TM PTABLE P202105 44451 0 6 0 0 0 44451
62 TM PTABLE 44446 0 3 0 0 0 44446
62 TX 327707 1035 6 0 0 5 27
|
impdp 대상 테이블은 LMODE 3 TM LOCK이 걸리고, impdp 대상 파티션 P202105에만 LMODE 6 TM LOCK이 걸려있음
이때는 impdp 대상 파티션에는 insert, update, delete 모두 불가능함
dml 테스트
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
insert
SQL> insert into ptable partition (p202105) values ('202105',1);
(대기중)
update
SQL>
update ptable partition (p202105)
set col1='202105', col2 = 9999
where col1='202105' and col2 = 582205;
(대기중)
delete
SQL>
delete ptable partition (p202105)
where col1='202105' and col2 = 582205;
(대기중)
|
lock 확인
|
1
2
3
4
5
6
7
8
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
51 TM PTABLE P202105 44451 0 0 3 0 0 44451
51 TM PTABLE 44446 0 3 0 0 0 44446
62 TM PTABLE 44446 0 3 0 0 0 44446
62 TM PTABLE P202105 44451 0 6 0 1 0 44451
62 TX 655392 975 6 0 0 10 32
|
impdp 세션인 62번 세션의 TM lock의 block 부분에 1이 되어있음(lock을 얻기위해 대기중인 세션이 있다는 뜻)
그리고 dml 세션인 51번 세션의 TM lock의 request 부분에 3이라고 되어있음(이 세션이 lmode 3을 얻기위해 대기중이라는것을 알수있음)
하지만 impdp 비대상 파티션에는 insert, update, delete 모두 가능함
impdp 실행
|
1
|
$ impdp system/oracle directory=partpump dumpfile=part_dump2.dmp logfile=part_dump_impdp2.log tables=IMSI.PTABLE:P202105 table_exists_action=append data_options=trust_existing_table_partitions
|
lock 확인
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
275 TM PTABLE P202105 44451 0 6 0 0 0 44451
275 TM PTABLE 44446 0 3 0 0 0 44446
275 TX 655367 974 6 0 0 10 7
|
dml 테스트
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
insert
SQL> insert into ptable partition (p202110) values ('202110',1);
1 row created.
update
SQL>
update ptable partition (p202110)
set col1='202110', col2 = 9999
where col1='202110' and col2 = 119816;
8 rows updated.
delete
SQL>
delete ptable partition (p202110)
where col1='202110' and col2 = 119816;
8 rows deleted.
|
정상적으로 dml이 수행됨
lock 확인
|
1
2
3
4
5
6
7
8
9
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
37 TM PTABLE P202110 44456 0 3 0 0 0 44456
37 TM PTABLE 44446 0 3 0 0 0 44446
37 TX 393217 1021 6 0 0 6 1
275 TM PTABLE 44446 0 3 0 0 0 44446
275 TM PTABLE P202105 44451 0 6 0 0 0 44451
275 TX 655367 974 6 0 0 10 7
|
blocking 없이 impdp와 dml 모두 잘 수행됨
ash 쿼리 확인(data_options=trust_existing_table_partitions 옵션 사용시)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000 LONG 99999
SELECT ash.sql_id,
sql_fulltext
FROM (SELECT sql_id
FROM (SELECT sql_id, count(sample_time)
FROM v$active_session_history
WHERE 1 = 1
AND sql_opname = 'INSERT'
AND module = 'Data Pump Worker'
AND sample_time BETWEEN SYSDATE - interval '1' minute and SYSDATE
GROUP BY sql_id
ORDER BY Count(sample_time) DESC)
WHERE ROWNUM <= 5) ash, v$sql s
WHERE ash.sql_id = s.sql_id;
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
1u4j2bb2tyn6d INSERT /*+ APPEND ENABLE_PARALLEL_DML PARALLEL("PTABLE",1)+*/
INTO RELATIONAL("IMSI"."PTABLE" NOT XMLTYPE) PARTITION ( "P202105" ) ("COL1", "COL2") SELECT "COL1", "COL2"
FROM "SYSTEM"."ET$003030B90001" KU$
|
일반 impdp와 data_options=trust_existing_table_partitions 옵션 사용 impdp 시 insert 쿼리 비교
일반 impdp insert 에 비해 PARTITION 구문이 추가로 들어감
이유
import 시 export 시점과 비교해서 파티션 구조가 변경 되었을수도 있고, nls 케릭터셋의 변경이 있을수도 있기 때문에 오라클은 특정 파티션만 lock을 거는게 아니라 전체 테이블에 대해 lock을 걸어버림
하지만 import 하는 데이터와 import 될 테이블의 파티션 구조를 신뢰한다면 trust_existing_table_partitions 옵션을 사용하는것이 빠르게 impdp 할수 있는 방법임
참고로 이 옵션을 사용해야 병렬 impdp 시 개별 파티션에 대해 병렬로 import가 진행됨
그렇지 않으면 한 파티션에 import 중일때 해당 파티션만 병렬로 insert를 하고 다른 파티션은 tm lock으로 대기를 하고 있게됨
결론
파티션 테이블에 impdp 시 개별 파티션레벨이 아닌 테이블 전체에 LMODE 6(Exclusive) TM lock이 걸려 impdp 대상,비대상 모든 파티션에 대해 동시 dml이 불가능하고,
이 lock이 걸리는것을 회피하기 위해선 impdp 시 data_options 옵션의 disable_append_hint 나, trust_existing_table_partitions 를 사용해줘야함
다만 disable_append_hint 는 append 힌트를 사용하지 않게끔 하는 힌트이기 때문에 direct path 방식이 아닌 Conventional path (일반 insert) 방식이라 느릴수 있음
trust_existing_table_partitions 는 꼭 파티션 구조가 동일한지 확인이 된 상태에서 실행해줘야함
참조 :
https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:9532861700346267954
https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/datapump-import-utility.html#GUID-5FFA128D-B7F5-41D0-A72C-EB2CE384765D
2014960.1
https://positivemh.tistory.com/808
대량 샘플데이터 생성용 쿼리 벌크 인서트
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 대량 샘플데이터 생성용 쿼리 벌크인서트 벌크인서트를 이용해 대량 데이터를 만들 때 회원번호, 전화번호, 주소, 금액, 일자, 일
positivemh.tistory.com
https://positivemh.tistory.com/892
오라클 19c 파티션 테이블 append insert 시 lock 확인
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c 파티션 테이블 append insert 시 lock 확인 만약 파티션 테이블에 일반 테이블의 데이터를 통으로 밀어 넣어야할 때 일반 inser
positivemh.tistory.com
https://positivemh.tistory.com/908
오라클 19c 동시에 같은 테이블에 datapump 시 발생하는 lock 확인
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.15.0.0 방법 : 오라클 19c 동시에 같은 테이블에 datapump 시 발생하는 lock 확인 오라클 환경에서 datapump를 이용해 동시에 같은 테이블에 expdp하거나 i
positivemh.tistory.com
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 파티션 테이블 인덱스 생성, rebuild 시 lock 및 online 옵션 테스트 (0) | 2024.01.22 |
|---|---|
| 오라클 19c 잘못된 dns 주소로 인한 sqlplus tns 연결 지연 문제 (0) | 2024.01.16 |
| 오라클 19c sqlplus 에서의 rollback 커맨드 신기한점 (0) | 2023.12.26 |
| 오라클 19c OCP, OCM 자격증 취득 관련 내용 (13) | 2023.12.20 |
| 오라클 19c 인터벌 파티션 테이블 주기적으로 삭제 (0) | 2023.12.19 |