내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
10.12
14:00
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.10.0.0
방법 : 오라클 19c index 생성 rebuild 시 발생하는 lock 확인
인덱스 생성 시 dml이 없는 업무 외 시간인 경우 그냥 인덱스를 생성할수도 있지만
업무 시간이거나 dml이 조금이라도 있는 경우 업무 지연을 막기 위해 online 옵션을 사용해서 인덱스를 생성할 수 있음
online 옵션을 사용하지 않았을때와 사용했을때 발생하는 lock 차이를 테스트해봄
인덱스 생성 테스트
인덱스 rebuild 테스트
인덱스 생성 테스트
세션 정보 확인(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 idxtest purge;
create table idxtest as select * from dba_segments;
Table created.
|
샘플데이터 삽입(원하는 만큼 반복)
|
1
2
3
|
SQL>
insert /*+ append */ into idxtest select * from idxtest;
commit;
|
테이블 용량 확인
|
1
2
3
4
5
6
7
8
|
SQL>
select bytes/1024/1024 mb
from dba_segments
where segment_name = 'IDXTEST';
MB
----------
5475
|
online 옵션 미사용 테스트
인덱스 생성 시도
|
1
2
|
SQL> create index idxtest_ix1 on idxtest(owner, segment_name, segment_type, tablespace_name);
(생성중)
|
세션 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 18 0 3 0 0 0 18 0
1997 TM 2022433 0 4 0 0 30 56353 0
1997 TX 3080208 1199574 6 0 0 47 16 1199574
|
1997 세션에 TM lock ID1 18(OBJ$) 대해 LMODE 3 (row-X (SX))이 걸려있고,
TM lock ID1 2022433(IDXTEST) 에 대해 LMODE 4 (share (S))가 걸려있고,
TX lock 에 대해 LMODE 6 (exclusive (X)) 이 걸려있음
TM lock 오브젝트 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
col object_name for a10
select object_id, object_name, object_type from dba_objects
where object_id in (18, 2022433);
OBJECT_ID OBJECT_NAM OBJECT_TYPE
---------- ---------- -----------------------
18 OBJ$ TABLE
2022433 IDXTEST TABLE
|
OBJ$ 오브젝트와 IDXTEST 테이블임
OBJ$ 오브젝트는 왜 TM lock이 걸리는지 정확히 알수없음(인덱스를 만들기 위해서 걸리는건지?)
OBJ$ 테이블은 확인결과 dba_objects 보다 몇개 더 많은 object 정보를 가지고 있는 뷰로 보임
세션 2에서 insert, update, delete 각각 시도 후 lock 확인
|
1
2
3
4
5
6
|
SQL> insert into idxtest select * from dba_segments where rownum<=1;
(대기)
SQL> update idxtest set owner = 'IMSI';
(대기)
SQL> delete idxtest where rownum<=1;
(대기)
|
lock 때문에 dml이 수행되지 않음
세션 3에서 lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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 18 0 3 0 0 0 18 0
1997 TM 2022433 0 4 0 1 30 56353 0
1997 TX 3080208 1199574 6 0 0 47 16 1199574
3989 TM 2022433 0 0 3 0 30 56353 0
|
3989 세션의 TM lock ID1 2022433(IDXTEST) 에 대해 LMODE 값이 0 (none)이고 REQUEST가 3 으로 되어있음
(TM lock에 LMODE 3 (row-X (SX))을 걸기 위해 대기중이라는 뜻)
1997 세션의 TM lock ID1 2022433(IDXTEST) 에 대해 BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
3989 세션의 정보가 insert, update, delete 시 모두 동일하게 표시됨
그림에 의하면 LMODE 4 (share (S)) 와 LMODE 3 (row-X (SX)) 가 호환이 안된다고 나와있음
http://wiki.gurubee.net/display/STUDY/1.Lock
(인덱스 생성 완료 후 lock 해소됨)
인덱스 생성시 01분 06초 소요됨
생성 완료된 인덱스 제거
|
1
2
3
|
SQL> drop index idxtest_ix1;
Index dropped.
|
online 옵션 사용 테스트
online 옵션으로 인덱스 재생성 시도
|
1
2
|
SQL> create index idxtest_ix1 on idxtest(owner, segment_name, segment_type, tablespace_name) online;
(생성중)
|
세션 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 2 0 0 30 56353 0
1997 TM 2022460 0 4 0 0 30 56380 0
1997 TX 3014669 1662613 6 0 0 46 13 1662613
|
1997 세션에 TM lock ID1 2022433(IDXTEST) 에 대해 LMODE 2 (row-S (SS))가 걸려있고,
(*online 옵션 미사용시에는 TM lock ID1 2022433(IDXTEST) 에 대해 LMODE 4 (share (S)) 가 있었음)
TM lock ID1 2022460(SYS_JOURNAL_2022460) 에 대해 LMODE 4 (share (S))가 걸려있고,
TX lock 에 대해 LMODE 6 (exclusive (X))이 걸려있음
*테스트 중 발견한 신기한 점은 create index online 명령어 수행 후 약 35초 후
TX lock가 하나 더 생긴다는 점임(sid 1997 TX lock ID1 3014671)
|
1
2
3
4
5
6
|
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2022433 0 2 0 0 30 56353 0
1997 TM 2022460 0 4 0 0 30 56380 0
1997 TX 3014671 1662514 6 0 0 46 15 1662514
1997 TX 3014669 1662613 6 0 0 46 13 1662613
|
TM lock 오브젝트 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
col object_name for a20
select object_id, object_name, object_type from dba_objects
where object_id in (2022433, 2022460);
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------- -----------------------
2022433 IDXTEST TABLE
2022460 SYS_JOURNAL_2022460 TABLE
|
SYS_JOURNAL_2022460 오브젝트와 IDXTEST 테이블임
SYS_JOURNAL_2022460 오브젝트는 왜 TM lock이 걸리는지 정확히 알수없음(online 으로 인덱스를 만들기 위해서 사용하는 오브젝트로 보임)
해당 테이블 정보 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> desc SYS_JOURNAL_2022460
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
select count(*) from SYS_JOURNAL_2022460;
COUNT(*)
----------
0
|
데이터가 없는 컬럼 7개 짜리 테이블임
세션 2에서 insert, update, delete 각각 시도 후 lock 확인
|
1
2
3
4
5
6
|
SQL> insert into idxtest select * from dba_segments where rownum<=1;
1 row created.
SQL> update idxtest set owner = 'IMSI' where rownum<=1;
1 row updated.
SQL> delete idxtest 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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
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
;
insert 시 lock 확인
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2022433 0 2 0 0 30 56353 0
1997 TM 2022460 0 4 0 0 30 56380 0
1997 TX 3014669 1662613 6 0 0 46 13 1662613
3989 TM 2022433 0 3 0 0 30 56353 0
3989 TX 5439517 76522 6 0 0 83 29 76522
update 시 lock 확인
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2022433 0 2 0 0 30 56353 0
1997 TM 2022460 0 4 0 0 30 56380 0
1997 TX 3014669 1662613 6 0 0 46 13 1662613
3989 TM 2022433 0 3 0 0 30 56353 0
3989 TX 3866641 1251648 6 0 0 59 17 1251648
delete 시 lock 확인
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2022433 0 2 0 0 30 56353 0
1997 TM 2022460 0 4 0 0 30 56380 0
1997 TX 3014669 1662613 6 0 0 46 13 1662613
3989 TM 2022433 0 3 0 0 30 56353 0
3989 TX 4063251 1142696 6 0 0 62 19 1142696
|
1997 세션의 TM lock 2022433(IDXTEST) 에 대해 LMODE 2 (row-S (SS))가 걸려있고,
어떠한 BLOCK도 존재하지 않음(*online 옵션 미사용시에는 sid 1997 의 TM lock에 BLOCK 값이 1로 되어있었음)
3989 세션의 TM lock 2022433(IDXTEST) 에 대해 LMODE 3 (row-X (SX))이고 REQUEST가 0 (none) 으로 되어있음
(*online 옵션 미사용시에는 sid 3389 의 TM lock ID1 2022433(IDXTEST) 에 대해 LMODE 0 (none) 과 REQUEST가 3 (row-X (SX))으로 되어있었음)
3989 세션의 TX lock LMODE 값이 6 (exclusive (X)) 로 되어 있음
3989 세션의 정보가 insert, update, delete 시 모두 동일하게 표시됨
그림에 의하면 LMODE 2 (row-S (SS)) 와 LMODE 3 (row-X (SX)) 가 호환이 된다고 나와있음
http://wiki.gurubee.net/display/STUDY/1.Lock
(인덱스 생성 완료 후 lock 해소됨)
인덱스 생성시 01분 30초 소요됨(online 옵션 미사용시에는 01분 06초 소요)
인덱스 rebuild 테스트
세션 1에서 인덱스 리빌드 시도
|
1
2
|
SQL> alter index idxtest_ix1 rebuild;
(rebuild 중)
|
세션 3에서 lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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 4 0 0 30 56353 0
|
인덱스 rebuild 를 실행한 1997 세션의 TM lock ID1(2022433 IDXTEST)에 대해 LMODE 4 (share (S))가 있음
세션 2에서 insert, update, delete 각각 시도 후 lock 확인
|
1
2
3
4
5
6
7
|
SQL>
insert into idxtest select * from dba_segments where rownum<=1;
(대기)
update idxtest set owner = 'IMSI' where rownum<=1;
(대기)
delete idxtest where rownum<=1;
(대기)
|
lock 때문에 dml이 수행되지 않음
세션 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 4 0 1 30 56353 0
3989 TM 2022433 0 0 3 0 30 56353 0
|
인덱스 rebuild 를 실행한 1997 세션의 TM lock ID1(2022433 IDXTEST)에 대해 LMODE 4 (share (S))가 있고 BLOCK 값이 1로 표시됨
dml 을 실행한 3989 세션은 모두 동일하게 TM lock ID1(2022433 IDXTEST)에 대해 LMODE 0 (None)으로 대기하면서 REQUEST 가 3 (row-X (SX))으로 표시됨
인덱스 생성시와 동일하게 인덱스 rebuild 가 끝나갈 때 쯤 TX lock LMODE 6 (exclusive (X))이 새로 생김
|
1
2
3
4
5
|
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2022433 0 4 0 1 30 56353 0
1997 TX 2228247 1460008 6 0 0 34 23 1460008
3989 TM 2022433 0 0 3 0 30 56353 0
|
index rebuild online 시도
|
1
2
|
SQL> alter index idxtest_ix1 rebuild online;
(rebuild 중)
|
세션 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 2073789 0 4 0 0 31 42173 0
1997 TM 2022433 0 2 0 0 30 56353 0
1997 TX 3014665 1693667 6 0 0 46 9 1693667
|
인덱스 rebuild 를 실행한 1997 세션의 TM lock ID1(2022433 IDXTEST)에 대해 LMODE 2 (row-S (SS))가 있고
1997 세션의 TM lock ID1(2073789 SYS_JOURNAL_2029223)에 대해 LMODE 4 (share (S))가 있고
1997 세션의 TX lock 에 대해 LMODE 6 (exclusive (X))이 있음
TM lock 오브젝트 확인
|
1
2
3
4
5
6
7
8
|
SQL>
col object_name for a20
select object_id, object_name, object_type from dba_objects
where object_id in (2022433, 2073789);
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------- -----------------------
2022433 IDXTEST TABLE
2073789 SYS_JOURNAL_2029223 TABLE
|
세션 2(SID 3989)에서 insert, update, delete 각각 시도 후 lock 확인
|
1
2
3
4
5
6
7
|
SQL>
insert into idxtest select * from dba_segments where rownum<=1;
1 row created.
update idxtest set owner = 'IMSI' where rownum<=1;
1 row updated.
delete idxtest 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
|
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 2 0 0 30 56353 0
1997 TM 2073789 0 4 0 0 31 42176 0
1997 TX 5046290 370581 6 0 0 77 18 370581
3989 TM 2022433 0 3 0 0 30 56353 0
3989 TX 2228234 1465983 6 0 0 34 10 1465983
|
인덱스 rebuild 를 실행한 1997 세션의 TM lock ID1(2022433 IDXTEST)에 대해 LMODE 2 (row-S (SS))가 있고
1997 세션의 TM lock ID1(2073789 SYS_JOURNAL_2029223)에 대해 LMODE 4 (share (S))가 있고
1997 세션의 TX lock 에 대해 LMODE 6 (exclusive (X))이 있음
3989 세션의 TM lock ID1(2022433 IDXTEST)에 대해 LMODE 3 (row-X (SX))가 있고
(*online 옵션 미사용시에는 sid 3389 의 TM lock ID1 2022433(IDXTEST) 에 대해 LMODE 0 (none) 과 REQUEST가 3 (row-X (SX))으로 되어있었음)
3989 세션의 TX lock 에 대해 LMODE 값이 6 (exclusive (X)) 로 되어 있음
3989 세션의 정보가 insert, update, delete 시 모두 동일하게 표시됨
그림에 의하면 LMODE 3 (row-X (SX)) 과 LMODE 3 (row-X (SX)) 이 호환이 된다고 나와있음
http://wiki.gurubee.net/display/STUDY/1.Lock
결론 :
인덱스 생성 및 rebuild 시 online 옵션을 사용하면 dml 이 있는 환경에서도 인덱스 생성 및 rebuild가 가능함
인덱스 생성 시 발생하는 영향을 최소화 하기 위해선 invisible 옵션까지 같이 써주면
숨김 모드(invisible)로 인덱스가 만들어져서 실행계획 변경등에 영향을 미치지 않음
그리고 online 인덱스 생성시 시간이 조금 더 소요됨
참조 : https://positivemh.tistory.com/895
오라클 19c 일반 dml 시 lock 확인 테스트
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.11.0.0 방법 : 오라클 19c 일반 dml 시 lock 확인 테스트 오라클을 사용하면서 dml을 하게되면 lock 이 발생함 일반적인 insert, update, delete 수행 시 각각
positivemh.tistory.com
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-LOCK.html#GUID-87D76889-832C-4BFC-B8B0-154A22721781
https://www.dba-oracle.com/t_callan_data_dictionary.htm
http://wiki.gurubee.net/display/STUDY/1.Lock
https://positivemh.tistory.com/906
오라클 19c 테이블 move 시 발생하는 lock 확인
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c 테이블 move 시 발생하는 lock 확인 테이블 용량을 줄이거나 특정 테이블스페이스의 여유공간이 부족 할때 move 명령을(reorg
positivemh.tistory.com
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 테이블 move 시 발생하는 lock 확인 (0) | 2023.07.06 |
|---|---|
| 오라클 19c 테이블스페이스 BIGFILE/SMALLFILE Default 값 변경 (0) | 2023.06.28 |
| 오라클 19c 소문자 컬럼 테스트 (0) | 2023.06.06 |
| 오라클 19c insert 쿼리 logical read 확인 (0) | 2023.06.02 |
| 오라클 19c 일반 dml 시 발생하는 lock 확인 (0) | 2023.06.02 |