프린트 하기

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.15.0.0

 

방법 : 오라클 19c 동시에 같은 테이블에 datapump 시 발생하는 lock 확인

오라클 환경에서 datapump를 이용해 동시에 같은 테이블에 expdp하거나 impdp할때 발생하는 lock을 확인해봄

 

 

동시 expdp lock 테스트
os 디렉토리 생성

1
$ mkdir -/home/oracle/datapump

 

 

디렉토리 생성

1
2
3
SQL> create directory locktest as '/home/oracle/datapump';
 
Directory created.

 

 

샘플 테이블 생성

1
2
3
SQL> create table locktest tablespace users as select * from dba_objects;
 
Table created.

 

 

샘플 데이터 추가 삽입

1
2
3
4
5
6
SQL>
alter session enable parallel dml;
alter table locktest nologging;
insert /*+ append parallel(a 4) */ into locktest a select /*+ parallel(a 4) */ * from locktest;
commit;
(원하는만큼 반복)

 

 

샘플 테이블 용량 확인

1
2
3
4
5
6
7
8
9
SQL>
set lines 200 pages 1000
col segment_name for a10
select segment_name, round(bytes/1024/1024) mb from dba_segments
where segment_name = 'LOCKTEST';
 
SEGMENT_NA         MB
---------- ----------
LOCKTEST         3560

 

 

세션 1 expdp 실행

1
2
$ expdp system/"We1come$" directory=locktest job_name=LOCKTEST_01 dumpfile=expdp_locktest1.dmp 
logfile=expdp_locktest1.log tables=imsi.locktest status=60 

 

 

lock 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 TYPE in ('TX','TM')
order by sid, type
;
 
no rows selected

datapump expdp시 1개 세션 실행시에는 lock 없음

 

 

동시에 세션 2에서 expdp 실행

1
2
$ expdp system/"We1come$" directory=locktest job_name=LOCKTEST_02 dumpfile=expdp_locktest2.dmp 
logfile=expdp_locktest2.log tables=imsi.locktest status=60 

 

 

lock 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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 TYPE in ('TX','TM')
order by sid, type
;
 
    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK USN                        SLOT        SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
    829 TM      21309          0          3          0          0 0                         21309          0
    829 TX     196608       4425          6          0          0 3                             0       4425
    893 TM      21309          0          3          0          0 0                         21309          0
    893 TX     655360      69348          6          0          0 10                            0      69348

초반에 1초 미만으로 TM lock과 TX lock이 동시에 관측됨
두 TM lock은 LMODE 3(row-X (SX))임
그렇기 때문에 2개 세션의 TM lock은 경합이 발생하지 않음(호환됨)

 

 

빠르게 lock 재조회

1
2
3
4
    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK USN                        SLOT        SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
     72 TM      20724          0          3          0          0 0                         20724          0
    198 TM      20724          0          3          0          0 0                         20724          0

조금 있다가 다른 TM Lock도 관측됨
두 TM lock은 LMODE 3(row-X (SX))임
그렇기 때문에 2개 세션의 TM lock은 경합이 발생하지 않음(호환됨)

 

 

첫번째 TM lock 오브젝트 확인(ID1 21309)

1
2
3
4
5
6
7
8
9
SQL>
col owner for a10
col object_name for a20
select owner, object_id, object_name, object_type from dba_objects
where object_id in (21309);
 
OWNER       OBJECT_ID OBJECT_NAME          OBJECT_TYPE
---------- ---------- -------------------- -----------------------
SYS             21309 SPD_SCRATCH_TAB      TABLE

SPD_SCRATCH_TAB 라는 처음보는 오브젝트에 대해 lock이 걸렸었음

 

 

해당 오브젝트 구조 확인

1
2
3
4
5
6
7
SQL> desc SPD_SCRATCH_TAB
 
 Name    Null?    Type
 ------ -------- --------
 C1              VARCHAR2(130)
 C2              VARCHAR2(130)
 N1              NUMBER

 

 

해당 오브젝트 데이터 확인

1
2
3
SQL> select * from SPD_SCRATCH_TAB;
 
no rows selected

데이터는 없음
*처음부터 다시 테스트 하여 TM Lock 발생시 데이터 있는지 확인해봤지만 데이터는 계속 없었음

 

 

두번쨰 TM lock 오브젝트 확인(ID1 20724)

1
2
3
4
5
6
7
8
9
SQL>
col owner for a10
col object_name for a20
select owner, object_id, object_name, object_type from dba_objects
where object_id in (20724);
 
OWNER       OBJECT_ID OBJECT_NAME          OBJECT_TYPE
---------- ---------- -------------------- -----------------------
SYS             20724 IMPDP_STATS          TABLE

expdp 만 수행했는데도 특이하게 IMPDP_STATS 오브젝트도 TM Lock이 잠깐 걸림

 

 

expdp시 status=60으로 해서 보면 첫번째 세션이 DW00 백그라운드 프로세스를 사용하고
두번째 세션이 DW01 백그라운드 프로세스를 사용하는것을 확인할 수 있음

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
세션1
Job: LOCKTEST_01
  Operation: EXPORT
  Mode: TABLE
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /home/oracle/datapump/expdp_locktest1.dmp
    bytes written: 4,096
 
Worker 1 Status:
  Instance ID: 1
  Instance name: TEST
  Host name: HLMAXGAUGE
  Object start time: Friday, 18 August, 2023 15:38:08
  Object status at: Friday, 18 August, 2023 15:38:09
  Process Name: DW00  <<<<<<<<<<
  State: EXECUTING
  Object Schema: IMSI
  Object Name: LOCKTEST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1
  Worker Parallelism: 1
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.
.
 
세션2
Job: LOCKTEST_02
  Operation: EXPORT
  Mode: TABLE
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Job heartbeat: 2
  Dump File: /home/oracle/datapump/expdp_locktest2.dmp
    bytes written: 4,096
 
Worker 1 Status:
  Instance ID: 1
  Instance name: TEST
  Host name: HLMAXGAUGE
  Object start time: Friday, 18 August, 2023 15:38:08
  Object status at: Friday, 18 August, 2023 15:38:09
  Process Name: DW01  <<<<<<<<<<
  State: EXECUTING
  Object Schema: IMSI
  Object Name: LOCKTEST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1
  Worker Parallelism: 1
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.
.

 

 

동시 expdp lock 결론 :
하나의 테이블에 대해 동시에 expdp 작업을 걸어도 해당 테이블에는 lock이 걸리지 않음

 

 

동시 impdp lock 테스트

이전 테스트에서 받은 dmp 파일 확인

1
2
3
4
5
6
$ ls -/home/oracle/datapump/
total 6300096
-rw-r----- 1 oracle oinstall 3225645056 Aug 18 16:16 expdp_locktest1.dmp
-rw-r--r-- 1 oracle oinstall       1078 Aug 18 16:16 expdp_locktest1.log
-rw-r----- 1 oracle oinstall 3225645056 Aug 18 16:16 expdp_locktest2.dmp
-rw-r--r-- 1 oracle oinstall       1078 Aug 18 16:16 expdp_locktest2.log

 

 

기존 테이블 삭제

1
2
3
SQL> drop table locktest purge;
 
Table dropped.

 

 

세션 1 impdp 실행

1
2
$ impdp system/"We1come$" directory=locktest job_name=LOCKTEST_imp_01 dumpfile=expdp_locktest1.dmp 
logfile=impdp_locktest1.log table_exists_action=append status=60 

 

 

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
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 TYPE in ('TX','TM')
order by sid, type
;
 
    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK USN                        SLOT        SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
    639 TM      75985          0          6          0          0 1                         10449          0
    639 TX     655384      69807          6          0          0 10                           24      69807

impdp 실행시 75985 오브젝트(LOCKTEST)에 대해 TM Lock이 발생하고 TX lock 도 발생함
TM Lock 의 LMODE가 6(exclusive (X)) 이기 때문에 어떤 세션도 해당 테이블을 변경할 수 없음(dml ddl 등)

 

 

TM lock 오브젝트 확인(ID1 75985)

1
2
3
4
5
6
7
8
9
SQL>
col owner for a10
col object_name for a20
select owner, object_id, object_name, object_type from dba_objects
where object_id in (75985);
 
OWNER       OBJECT_ID OBJECT_NAME          OBJECT_TYPE
---------- ---------- -------------------- -----------------------
IMSI            75985 LOCKTEST             TABLE

LOCKTEST 테이블임

 

 

동시에 세션 2에서 impdp 실행

1
2
$ impdp system/"We1come$" directory=locktest job_name=LOCKTEST_imp_02 dumpfile=expdp_locktest2.dmp 
logfile=impdp_locktest2.log table_exists_action=append status=60 

 

 

lock 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 TYPE in ('TX','TM')
order by sid, type
;
 
    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK USN                        SLOT        SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
    639 TM      75985          0          6          0          1 1                         10449          0
    639 TX     655384      69807          6          0          0 10                           24      69807
    828 TM      75985          0          0          6          0 1                         10449          0

639 세션의 TM Lock에 BLOCK에 1이 생기고 828 세션이 REQUEST에 6(exclusive (X))이 표시됨
75985 오브젝트에 대해 TM Lock LMODE 6(exclusive (X))을 얻기 위해 대기중이라는 뜻

 

 

잠시뒤 639 세션 impdp 작업이 끝난 뒤 lock 확인

1
2
3
4
    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK USN                        SLOT        SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
    828 TM      75985          0          6          0          0 1                         10449          0
    828 TX     655377      69816          6          0          0 10                           17      69816

828세션에 대해 TM Lock이 LMODE 6(exclusive (X)) 을 가지게 되고 TX lock 도 발생함
TM Lock 의 LMODE가 6(exclusive (X)) 이기 때문에 어떤 세션도 해당 테이블을 변경할 수 없음(dml ddl 등)

 

 

동시 impdp lock 결론 : 
하나의 테이블에 대해 동시 impdp 시 table_exists_action=append 옵션을 사용하지 않으면
ORA-39151: Table "IMSI"."LOCKTEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip 
에러 메세지가 발생하고 table_exists_action=append 옵션을 사용하면
먼저 시작한 세션이 TM Lock LMODE 6을 잡고 뒤 세션이 대기하게 됨
이후 앞 세션 impdp가 끝나면 뒤 세션도 동일하게 TM Lock LMODE 6을 잡고 impdp를 진행함

 

동시 expdp lock 결론 :
하나의 테이블에 대해 동시에 expdp 작업을 걸어도 해당 테이블에는 lock이 걸리지 않음

 

 

* 기타
동시 impdp lock 모니터링시 발견한 내용1(테이블 삭제 후 여러번 테스트한거라 ID1(object_id)가 다를수 있음 76151이 LOCKTEST 오브젝트)
빠르게 lock 를 조회하다 보면 중간에 세션 1(639)의 impdp가 끝나갈때 639 세션의 76151에 대한 TM Lock이 없어졌다가 
잠시뒤 639 세션에서 대량의 TM Lock 오브젝트가 관찰됨
OWNER가 SYS 인 TM Lock LMODE 3 가 49개 생기고, LMODE 6 도 1개 생김(76158)

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
@lock 조회 쿼리
    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK USN                        SLOT        SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
    639 TM      76151          0          6          0          1 1                         10615          0
    639 TX     196630       4453          6          0          0 3                            22       4453
    828 TM      76151          0          0          6          0 1                         10615          0
.
.
    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK USN                        SLOT        SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
    828 TM      76151          0          6          0          0 1                         10615          0
    828 TX     327705       4285          6          0          0 5                            25       4285
.
.
    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK USN                        SLOT        SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
    639 TM        405          0          3          0          0 0                           405          0
    639 TM         20          0          3          0          0 0                            20          0
    639 TM        152          0          3          0          0 0                           152          0
    639 TM         21          0          3          0          0 0                            21          0
    639 TM       1388          0          3          0          0 0                          1388          0
    639 TM        370          0          3          0          0 0                           370          0
    639 TM        121          0          3          0          0 0                           121          0
    639 TM        109          0          3          0          0 0                           109          0
    639 TM        112          0          3          0          0 0                           112          0
    639 TM        128          0          3          0          0 0                           128          0
    639 TM        124          0          3          0          0 0                           124          0
    639 TM        317          0          3          0          0 0                           317          0
    639 TM      76158          0          6          0          0 1                         10622          0
    639 TM         68          0          3          0          0 0                            68          0
    639 TM        261          0          3          0          0 0                           261          0
    639 TM         61          0          3          0          0 0                            61          0
    639 TM        117          0          3          0          0 0                           117          0
    639 TM        279          0          3          0          0 0                           279          0
    639 TM        411          0          3          0          0 0                           411          0
    639 TM        375          0          3          0          0 0                           375          0
    639 TM         18          0          3          0          0 0                            18          0
    639 TM         19          0          3          0          0 0                            19          0
    639 TM         31          0          3          0          0 0                            31          0
    639 TM         32          0          3          0          0 0                            32          0
    639 TM        810          0          3          0          0 0                           810          0
    639 TM        141          0          3          0          0 0                           141          0
    639 TM        307          0          3          0          0 0                           307          0
    639 TM        309          0          3          0          0 0                           309          0
    639 TM          4          0          3          0          0 0                             4          0
    639 TM        315          0          3          0          0 0                           315          0
    639 TM        319          0          3          0          0 0                           319          0
    639 TM        401          0          3          0          0 0                           401          0
    639 TM        321          0          3          0          0 0                           321          0
    639 TM        323          0          3          0          0 0                           323          0
    639 TM        325          0          3          0          0 0                           325          0
    639 TM        865          0          3          0          0 0                           865          0
    639 TM        868          0          3          0          0 0                           868          0
    639 TM        872          0          3          0          0 0                           872          0
    639 TM       1382          0          3          0          0 0                          1382          0
    639 TM         77          0          3          0          0 0                            77          0
    639 TM        377          0          3          0          0 0                           377          0
    639 TM        275          0          3          0          0 0                           275          0
    639 TM        734          0          3          0          0 0                           734          0
    639 TM        742          0          3          0          0 0                           742          0
    639 TM        402          0          3          0          0 0                           402          0
    639 TM        374          0          3          0          0 0                           374          0
    639 TM        376          0          3          0          0 0                           376          0
    639 TM        391          0          3          0          0 0                           391          0
    639 TM        407          0          3          0          0 0                           407          0
    639 TM        115          0          3          0          0 0                           115          0
    639 TX     655373      68423          6          0          0 10                           13      68423
    828 TM      76151          0          6          0          0 1                         10615          0
    828 TX     327705       4285          6          0          0 5                            25       4285
 
53 rows selected.

 

 

해당 TM 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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
SQL> 
col owner for a10
col object_name for a30
select owner, object_id, object_name, object_type from dba_objects
where object_id 
in (4052015221138837012110911212812431776158
682616111727941137518193132810141307309
4315319401321323325865868872138277377275
734742402374376391407115);
 
OWNER       OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ---------- ------------------------------ -----------------------
SYS                 4 TAB$                           TABLE
SYS                18 OBJ$                           TABLE
SYS                19 IND$                           TABLE
SYS                20 ICOL$                          TABLE
SYS                21 COL$                           TABLE
SYS                31 CDEF$                          TABLE
SYS                32 CCOL$                          TABLE
SYS                61 OBJAUTH$                       TABLE
SYS                68 HIST_HEAD$                     TABLE
SYS                77 OBJECT_USAGE                   TABLE
SYS               109 LOB$                           TABLE
SYS               112 COLTYPE$                       TABLE
SYS               115 SUBCOLTYPE$                    TABLE
SYS               117 NTAB$                          TABLE
SYS               121 REFCON$                        TABLE
SYS               124 OPQTYPE$                       TABLE
SYS               128 EDITION$                       TABLE
SYS               141 ICOLDEP$                       TABLE
SYS               152 ECOL$                          TABLE
SYS               261 COM$                           TABLE
SYS               275 JIJOIN$                        TABLE
SYS               279 JIREFRESHSQL$                  TABLE
SYS               307 SQLTXL$                        TABLE
SYS               309 SQLTXL_SQL$                    TABLE
SYS               315 SQLTXL_ERR$                    TABLE
SYS               317 CLST$                          TABLE
SYS               319 CLSTKEY$                       TABLE
SYS               321 CLSTDIMENSION$                 TABLE
SYS               323 CLSTJOIN$                      TABLE
SYS               325 INDEX_ORPHANED_ENTRY$          TABLE
SYS               370 PROCEDURE$                     TABLE
SYS               374 IDL_UB1$                       TABLE
SYS               375 IDL_CHAR$                      TABLE
SYS               376 IDL_UB2$                       TABLE
SYS               377 IDL_SB4$                       TABLE
SYS               391 NCOMP_DLL$                     TABLE
SYS               401 LIBRARY$                       TABLE
SYS               402 ASSEMBLY$                      TABLE
SYS               405 DIANA_VERSION$                 TABLE
SYS               407 PLSCOPE_IDENTIFIER$            TABLE
SYS               411 PLSCOPE_ACTION$                TABLE
SYS               734 DIR$                           TABLE
SYS               742 TYPE_MISC$                     TABLE
SYS               810 JAVAOBJ$                       TABLE
SYS               865 SUMPARTLOG$                    TABLE
SYS               868 SUMDELTA$                      TABLE
SYS               872 SNAP_LOADERTIME$               TABLE
SYS              1382 EXTERNAL_TAB$                  TABLE
SYS              1388 EXTERNAL_LOCATION$             TABLE
 
49 rows selected.

impdp 작업이 끝난뒤라 그런지 LMODE 6(76158) object는 dba_object에서 찾을수 없었음
아마 임시로 생성되는 오브젝트가 아닐까 싶음

 

 

참조 : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-LOCK.html#GUID-87D76889-832C-4BFC-B8B0-154A22721781
12c DataPump Export (EXPDP) Is Slow When Exporting Statistics (Doc ID 2120162.1)