내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
2024
10.12
14:00
10.12
14:00
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 -p /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 -l /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 (405, 20, 152, 21, 1388, 370, 121, 109, 112, 128, 124, 317, 76158
, 68, 261, 61, 117, 279, 411, 375, 18, 19, 31, 32, 810, 141, 307, 309
, 4, 315, 319, 401, 321, 323, 325, 865, 868, 872, 1382, 77, 377, 275
, 734, 742, 402, 374, 376, 391, 407, 115);
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)
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c exachk 수집 가이드 (0) | 2023.07.26 |
|---|---|
| 오라클 19c sysaux 테이블스페이스 안에 들어있는 정보 확인 (0) | 2023.07.23 |
| 오라클 19c expdp가 ADG Standby db에서 동작하는지 확인 (1) | 2023.07.06 |
| 오라클 19c 테이블 move 시 발생하는 lock 확인 (0) | 2023.07.06 |
| 오라클 19c 테이블스페이스 BIGFILE/SMALLFILE Default 값 변경 (0) | 2023.06.28 |