OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.10.0.0
방법 : 오라클 19c 테이블 move 시 발생하는 lock 확인
테이블 용량을 줄이거나 특정 테이블스페이스의 여유공간이 부족 할때
move 명령을(reorg) 사용해 여유 용량을 확보할 수 있음
보통 move시 db lock이 발생할수 있어 dml이 없는 업무 외 시간에 진행을 하지만
업무 시간이거나 dml이 조금이라도 있는 경우 업무 지연을 막기 위해 online 옵션을 사용해서 move 명령을 수행할 수 있음
online 옵션을 사용하지 않았을때와 사용했을때 발생하는 lock 차이를 테스트해봄
online 옵션 미사용 테스트
online 옵션 사용 테스트
online 옵션 미사용 테스트
세션 정보 확인(2개 세션에서 확인)
|
1
2
3
4
5
|
SQL> select sid from v$mystat where rownum<=1;
SID
----------
1997
|
(세션 1 : 1997, 세션 2 : 3989)
샘플 테이블 생성
|
1
2
3
4
5
|
SQL>
drop table movetest purge;
create table movetest users as select * from dba_segments;
Table created.
|
샘플데이터 삽입(원하는 만큼 반복)
|
1
2
3
|
SQL>
insert /*+ append */ into movetest select * from movetest;
commit;
|
테이블 용량 확인
|
1
2
3
4
5
6
7
8
|
SQL>
select bytes/1024/1024 mb
from dba_segments
where segment_name = 'MOVETEST';
MB
----------
5475
|
테이블 move 시도
|
1
2
|
SQL> alter table movetest move tablespace imsitbs;
(move 중)
|
세션 3에서 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 sid in (1997, 3989)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2022433 0 6 0 0 30 56353 0
1997 TX 5439495 109150 6 0 0 83 7 109150
|
1997 세션에 TM lock ID1 2022433(MOVETEST) 에 대해 LMODE 6 (exclusive (X))가 걸려있고,
TX lock 에 대해 LMODE 6 (exclusive (X)) 이 걸려있음
TM lock 오브젝트 확인
|
1
2
3
4
5
6
7
8
|
SQL>
col object_name for a10
select object_id, object_name, object_type from dba_objects
where object_id in (2022433);
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------- -----------------------
2022433 MOVETEST TABLE
|
세션 2에서 insert, update, delete 각각 시도 후 lock 확인
|
1
2
3
4
5
6
|
SQL> insert into movetest select * from dba_segments where rownum<=1;
(대기 후 lock 확인 후 명령 취소)
SQL> update movetest set owner = 'IMSI';
(대기 후 lock 확인 후 명령 취소)
SQL> delete movetest where rownum<=1;
(대기 후 lock 확인 후 명령 취소)
|
lock 때문에 dml이 수행되지 않음
세션 3에서 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 sid in (1997, 3989)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2022433 0 6 0 1 30 56353 0
1997 TX 5439495 109150 6 0 0 83 7 109150
3989 TM 2022433 0 0 3 0 30 56353 0
|
1997 세션의 TM lock ID1 2022433(movetest) 에 대해 BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
1997 세션의 TX lock 에 대해 LMODE 6 (exclusive (X)) 이 걸려있음
3989 세션의 TM lock ID1 2022433(movetest) 에 대해 LMODE 값이 0 (none)이고 REQUEST가 3 으로 되어있음
(TM lock에 LMODE 3 (row-X (SX))을 걸기 위해 대기중이라는 뜻)
3989 세션의 정보가 insert, update, delete 시 모두 동일하게 표시됨
(인덱스 생성 완료 후 lock 해소됨)
그림에 의하면 LMODE 6 (exclusive (X)) 와 LMODE 3 (row-X (SX)) 가 호환이 안된다고 나와있음
http://wiki.gurubee.net/display/STUDY/1.Lock
online 옵션 사용 테스트
online 테이블 move 시도
|
1
2
|
SQL> alter table movetest move tablespace imsitbs online;
(move 중)
|
세션 3에서 lock 확인
|
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
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 in (1997, 3989)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2022433 0 3 0 0 30 56353 0
1997 TM 2080395 0 6 0 0 31 48786 0
1997 TM 2080397 0 6 0 0 31 48788 0
1997 TX 5963804 38096 6 0 0 91 28 38096
1997 TX 5963809 38085 6 0 0 91 33 38085
|
1997 세션의 TM lock ID1 2022433(movetest) 에 대해 LMODE 3 (row-X (SX))가 걸려있고,
(*online 옵션 미사용시에는 TM lock ID1 2022433(movetest) 에 대해 LMODE 6 (exclusive (X)) 가 있었음)
1997 세션의 TM lock ID1 2080395(SYS_JOURNAL_2022433) 에 대해 LMODE 6 (exclusive (X))가 걸려있고,
1997 세션의 TM lock ID1 2080397(SYS_RMTAB$$_H2022433) 에 대해 LMODE 6 (exclusive (X))가 걸려있고,
1997 세션의 TX lock 2개에 대해 LMODE 6 (exclusive (X))이 각각 걸려있음
TM lock 오브젝트 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col object_name for a20
select object_id, object_name, object_type from dba_objects
where object_id in (2022433, 2080395, 2080397);
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------- -----------------------
2022433 MOVETEST TABLE
2080395 SYS_JOURNAL_2022433 TABLE
2080397 SYS_RMTAB$$_H2022433 TABLE
|
movetest 테이블과 2개의 시스템 테이블이 나옴
검색해보니 SYS_RMTAB$$_H2022433 테이블은 INTERNAL RECURSIVE MAPPING TABLE 이라고함(2664069.1)
*시스템 테이블 관련해서는 본문 마지막에 작성함
online move 중 세션 2에서 insert, update, delete 각각 시도 후 lock 확인
|
1
2
3
4
5
6
|
SQL> insert into movetest select * from dba_segments where rownum<=1;
1 row created.
SQL> update movetest set owner = 'IMSI' where rownum<=1;
1 row updated.
SQL> delete movetest where rownum<=1;
1 row deleted.
|
모든 dml이 각각 성공적으로 수행됨
세션 3에서 lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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 in (1997, 3989)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2022433 0 3 0 0 30 56353 0
1997 TM 2080395 0 6 0 0 31 48786 0
1997 TM 2080397 0 6 0 0 31 48788 0
1997 TX 5963804 38096 6 0 0 91 28 38096
1997 TX 5963809 38085 6 0 0 91 33 38085
3989 TM 2022433 0 3 0 0 30 56353 0
3989 TX 2162697 1444061 6 0 0 33 9 1444061
|
3989 세션의 lock이 생겼지만 서로 blocking 하지 않음(lock 미발생)
그림에 의하면 LMODE 3 (row-X (SX)) 과 LMODE 3 (row-X (SX)) 이 호환이 된다고 나와있음
http://wiki.gurubee.net/display/STUDY/1.Lock
dml 트랜젝션 롤백
|
1
|
SQL> rollback;
|
*테스트 중 발견한 신기한 점은 move tablespace online 명령어 수행이 끝나가기 전
기존에 있었던 2022433, 2080395, 2080397 외에 TM lock 이 총 3개 더 생긴다는 점임
(sid 1997 TM lock ID1 2080405, 18, 28)
|
1
2
3
4
5
6
7
8
9
10
|
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 18 0 3 0 0 0 18 0
1997 TM 28 0 3 0 0 0 28 0
1997 TM 2022433 0 3 0 0 30 56353 0
1997 TM 2080395 0 6 0 0 31 48786 0
1997 TM 2080397 0 6 0 0 31 48788 0
1997 TM 2080405 0 4 0 0 31 48789 0
1997 TX 5963804 38096 6 0 0 91 28 38096
1997 TX 5963803 38107 6 0 0 91 27 38107
|
1997 세션에서 기존 TM Lock(2022433, 2080395, 2080397) 외에 신규 TM lock이 생김(18, 28, 2080405)
move 끝나가기 전 생긴 TM lock 오브젝트 확인(18, 28, 2080405)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
col object_name for a20
select object_id, object_name, object_type from dba_objects
where object_id in (2022433, 2080395, 2080397, 2080405, 18, 28);
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------- -----------------------
18 OBJ$ TABLE
28 CON$ TABLE
2022433 MOVETEST TABLE
2080395 SYS_JOURNAL_2022433 TABLE
2080397 SYS_RMTAB$$_H2022433 TABLE
|
오븐젝트 18은 OBJ$ 테이블이고, 오브젝트 28은 CON$ 테이블임
오브젝트 2080405는 조회해도 나오지 않음
lock 조회시 나온 시스템 테이블들 구조 확인
SYS_JOURNAL_2022433 테이블 구조 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> desc SYS_JOURNAL_2022433
Name Null? Type
---------- -------- ---------------
C0 NOT NULL VARCHAR2(128)
C1 NOT NULL VARCHAR2(128)
C2 NOT NULL VARCHAR2(18)
C3 NOT NULL VARCHAR2(30)
OPCODE CHAR(1)
PARTNO NUMBER
RID NOT NULL ROWID
|
7개 컬럼으로 구성되어 있음
SYS_JOURNAL_2022460 테이블 count 확인(이 테이블은 move 작업 끝까지 데이터가 안들어옴)
|
1
2
3
4
5
|
SQL> select count(*) from SYS_JOURNAL_2022433;
COUNT(*)
----------
0
|
SYS_RMTAB$$_H2022433 테이블 구조 및 용량 확인
|
1
2
3
4
5
6
|
SQL> desc SYS_RMTAB$$_H2022433
Name Null? Type
--------------------------------- -------- ------
SRC_ROWID NOT NULL ROWID
TGT_ROWID NOT NULL ROWID
|
2개 컬럼으로 구성되어 있음
해당 테이블들 용량 확인
|
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
|
SQL>
col owner for a10
col segment_name for a30
select owner, segment_name , bytes/1024/1024 mb
from dba_segments
where segment_name in ('MOVETEST', 'SYS_JOURNAL_2022433', 'SYS_RMTAB$$_H2022433')
OWNER SEGMENT_NAME MB
---------- ------------------------------ ----------
IMSI MOVETEST 5475
IMSI SYS_RMTAB$$_H2022433 37
SQL> /
OWNER SEGMENT_NAME MB
---------- ------------------------------ ----------
IMSI MOVETEST 5475
IMSI SYS_RMTAB$$_H2022433 61
.
.
.
OWNER SEGMENT_NAME MB
---------- ------------------------------ ----------
IMSI MOVETEST 5475
IMSI SYS_RMTAB$$_H2022433 880
|
SYS_JOURNAL_2022433 테이블은 용량이 나오지 않고(count 0)
SYS_RMTAB$$_H2022433 테이블만 online move 시 계속 조회할때마다 용량이 올라가며 880mb 까지 올라감
SYS_RMTAB$$_H2022433 테이블 데이터 조회시 초기에는 계속 0건으로 나오다가
|
1
2
3
4
5
|
SQL> select count(*) from SYS_RMTAB$$_H2022433;
COUNT(*)
----------
0
|
online move 작업이 끝나갈 때 쯤 조회하면 데이터가 있는걸로 나옴(TM Lock 3개 더 생길때)
|
1
2
3
4
5
|
SQL> select count(*) from SYS_RMTAB$$_H2022433;
COUNT(*)
----------
30151424
|
이 시점에 SYS_RMTAB$$_H2022433 테이블 조회시 ROWID들이 나옴
|
1
2
3
4
5
6
7
8
9
10
|
SQL> select * from imsi.SYS_RMTAB$$_H2022433;
SRC_ROWID TGT_ROWID
---------- ---------------
AAIDGEAAFAAAun7AAA AAIDGKAAEAACUl7AAA
AAIDGEAAFAAAun7AAB AAIDGKAAEAACUl7AAB
AAIDGEAAFAAAun7AAC AAIDGKAAEAACUl7AAC
AAIDGEAAFAAAun7AAD AAIDGKAAEAACUl7AAD
AAIDGEAAFAAAun7AAE AAIDGKAAEAACUl7AAE
AAIDGEAAFAAAun7AAF AAIDGKAAEAACUl7AAF
|
move 완료 시 시스템 테이블도 자동으로 사라지고 모든 lock도 해소됨
결론 :
테이블 move 시 online 옵션을 사용하면 dml 이 있는 환경에서도 online move가 가능함
그리고 move 시 online 옵션을 사용할 때와 사용안할 때 속도차이는 크게 없는듯함
여러번 테스트 비교시 당시 db 부하에 따라 속도가 달라지는것으로 보임
https://positivemh.tistory.com/855
오라클 19c 테이블 move 동일 테이블스페이스 테스트
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c 테이블 move 동일 테이블스페이스 테스트 테이블 용량을 줄이거나 특정 테이블스페이스의 여유공간이 부족 할때 move 명
positivemh.tistory.com
https://positivemh.tistory.com/903
오라클 19c Online index 생성시 lock 확인 테스트
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c online index 생성시 lock 확인 테스트 인덱스 생성 시 dml이 없는 업무 외 시간인 경우 그냥 인덱스를 생성할수도 있지만 업
positivemh.tistory.com
https://connor-mcdonald.com/2021/02/19/free-space-requirements-for-alter-table/
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 동시에 같은 테이블에 datapump 시 발생하는 lock 확인 (0) | 2023.07.06 |
|---|---|
| 오라클 19c expdp가 ADG Standby db에서 동작하는지 확인 (1) | 2023.07.06 |
| 오라클 19c 테이블스페이스 BIGFILE/SMALLFILE Default 값 변경 (0) | 2023.06.28 |
| 오라클 19c 인덱스 생성 및 rebuild 시 발생하는 lock 확인 (0) | 2023.06.28 |
| 오라클 19c 소문자 컬럼 테스트 (0) | 2023.06.06 |