OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 파티션 테이블 인덱스 생성, rebuild 시 lock 및 online 옵션 테스트
오라클에서 파티션 테이블에 인덱스를 생성할때와 rebuild 할때 발생하는 lock에 대해 알아보고
online 옵션을 사용했을 때는 lock이 어떻게 발생하는지 테스트해봄
4가지 경우를 테스트 할 예정임
online 옵션 미사용 인덱스 생성시 lock 확인 테스트
online 옵션 사용 인덱스 생성시 lock 확인 테스트
online 옵션 미사용 인덱스 rebuild 시 lock 확인 테스트
online 옵션 사용 인덱스 rebuild 시 lock 확인 테스트
샘플 유저 생성
|
1
2
3
|
SQL>
create user imsi identified by imsi account unlock default tablespace users quota unlimited on users;
grant resource, connect to imsi;
|
파티션 테이블 생성
|
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>
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 ('20210200'),
partition p202102 values less than ('20210300'),
partition p202103 values less than ('20210400'),
partition p202104 values less than ('20210500'),
partition p202105 values less than ('20210600'),
partition p202106 values less than ('20210700'),
partition p202107 values less than ('20210800'),
partition p202108 values less than ('20210900'),
partition p202109 values less than ('20211000'),
partition p202110 values less than ('20211100'),
partition p202111 values less than ('20211200'),
partition p202112 values less than ('20220100'),
partition p_max values less than (maxvalue)
);
Table created.
|
파티션 테이블 샘플 데이터 삽입(원하는 만큼 실행)
|
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 := round(dbms_random.value(2021,2022))||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char( round(dbms_random.value(1,28)), '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
|
sqlplus 시간 측정용 timing on
|
1
|
SQL> set timing on
|
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
|
online 옵션 미사용 인덱스 생성시 lock 확인 테스트
인덱스 생성(로컬, 글로벌 인덱스에 발생하는 lock은 동일함)
|
1
2
3
4
5
6
7
|
SQL>
drop index pt_ix01;
create index pt_ix01 on ptable(col1, col2) local;
Index created.
Elapsed: 00:00:11.52
|
11.52초 소요됨
인덱스 생성 중 lock 확인
|
1
2
3
4
5
6
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
281 TM OBJ$ 18 0 3 0 0 0 18
281 TM PTABLE 25051 0 4 0 0 0 25051
281 TX 65549 1574 6 0 0 1 13
|
PTABLE에 대해 LMODE 4(share (S)) TM lock이 발생함
이때 dml 시도
|
1
2
3
4
5
6
|
SQL> insert into ptable values ('20210505', 1);
(대기중)
SQL> update ptable set col2 = 78291 where col1 = '20210606' and col2 = 78291;
(대기중)
SQL> delete ptable where col1 = '20210606' and col2 = 42963;
(대기중)
|
dml을 완료하지 못하고 대기하고 있음
인덱스 생성 중 dml 시 lock 확인
|
1
2
3
4
5
6
7
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
44 TM PTABLE 25051 0 0 3 0 0 25051
281 TM OBJ$ 18 0 3 0 0 0 18
281 TM PTABLE 25051 0 4 0 1 0 25051
281 TX 262150 2634 6 0 0 4 6
|
SID 281(인덱스 생성세션)의 LMODE 4(share (S)) TM lock에 의해
SID 44(dml 세션)이 LMODE 3(row-X)를 얻기 위해 대기하고 있음
그림에 의하면 LMODE 4 (share (S)) 와 LMODE 3 (row-X (SX)) 가 호환이 안된다고 나와있음
http://wiki.gurubee.net/display/STUDY/1.Lock
online 옵션 사용 인덱스 생성시 lock 확인 테스트
online 옵션을 사용해서 인덱스 생성
|
1
2
3
4
5
6
7
|
SQL>
drop index pt_ix01;
create index pt_ix01 on ptable(col1, col2) local online;
Index created.
Elapsed: 00:00:13.14
|
13.14초 소요됨
인덱스 생성 중 lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
281 TM PTABLE P_MAX 25064 0 2 0 0 0 25064
281 TM PTABLE P202112 25063 0 2 0 0 0 25063
281 TM SYS_JOURNAL_25235 25249 0 4 0 0 0 25249
281 TM PTABLE P202111 25062 0 2 0 0 0 25062
281 TM PTABLE P202110 25061 0 2 0 0 0 25061
281 TM PTABLE P202109 25060 0 2 0 0 0 25060
281 TM PTABLE P202108 25059 0 2 0 0 0 25059
281 TM PTABLE P202107 25058 0 2 0 0 0 25058
281 TM PTABLE P202106 25057 0 2 0 0 0 25057
281 TM PTABLE P202105 25056 0 2 0 0 0 25056
281 TM PTABLE P202104 25055 0 2 0 0 0 25055
281 TM PTABLE P202103 25054 0 2 0 0 0 25054
281 TM PTABLE P202102 25053 0 2 0 0 0 25053
281 TM PTABLE P202101 25052 0 2 0 0 0 25052
281 TM PTABLE 25051 0 2 0 0 0 25051
281 TX 131104 2930 6 0 0 2 32
281 TX 131073 2931 6 0 0 2 1
17 rows selected.
|
PTABLE에 대해 LMODE 2(row-S (SS)) TM lock이 발생함
그리고 SYS_JOURNAL_25235이라는 시스템 테이블에 LMODE 4(share (S)) TM lock가 발생함
이때 dml 시도
|
1
2
3
4
5
6
|
SQL> insert into ptable values ('20210505', 1);
1 row created.
SQL> update ptable set col2 = 78291 where col1 = '20210606' and col2 = 78291;
1 row updated.
SQL> delete ptable where col1 = '20210606' and col2 = 42963;
1 row deleted.
|
정상적으로 dml이 수행됨
인덱스 생성 중 dml 시 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> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
44 TM PTABLE 25051 0 3 0 0 0 25051
44 TM PTABLE P202106 25057 0 3 0 0 0 25057
44 TX 655385 1662 6 0 0 10 25
281 TM PTABLE P202109 25060 0 2 0 0 0 25060
281 TM PTABLE P202108 25059 0 2 0 0 0 25059
281 TM PTABLE P202107 25058 0 2 0 0 0 25058
281 TM PTABLE P202106 25057 0 2 0 0 0 25057
281 TM PTABLE P202105 25056 0 2 0 0 0 25056
281 TM PTABLE P202104 25055 0 2 0 0 0 25055
281 TM PTABLE P202103 25054 0 2 0 0 0 25054
281 TM PTABLE P202102 25053 0 2 0 0 0 25053
281 TM PTABLE P202101 25052 0 2 0 0 0 25052
281 TM PTABLE 25051 0 2 0 0 0 25051
281 TM SYS_JOURNAL_25251 25265 0 4 0 0 0 25265
281 TM PTABLE P202110 25061 0 2 0 0 0 25061
281 TM PTABLE P202111 25062 0 2 0 0 0 25062
281 TM PTABLE P_MAX 25064 0 2 0 0 0 25064
281 TM PTABLE P202112 25063 0 2 0 0 0 25063
281 TX 196632 2063 6 0 0 3 24
281 TX 196616 2062 6 0 0 3 8
20 rows selected.
|
SID 281(인덱스 생성세션)과 SID 44(dml 세션)이 문제 없이 각각 실행중임
하지만 인덱스 생성 중 dml은 잘 동작하지만 dml 트랜젝션을 종료(rollback, commit) 하지 않으면
인덱스 생성 세션은 계속 대기하고 있음
(테스트시 정상 생성 시간 13초가 지났는데도 계속 대기중이다가 rollback 시 바로 Index created. 메세지가 나옴)
online 옵션 미사용 인덱스 rebuild 시 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
|
SQL>
set lines 200 pages 1000
col index_owner for a10
col index_name for a20
col partition_name for a20
select dip.index_owner, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='PTABLE'
order by dip.index_name, dip.partition_name;
INDEX_OWNE INDEX_NAME PARTITION_NAME STATUS LOCALI ALIGNMENT
---------- -------------------- -------------------- -------- ------ ------------
IMSI PT_IX01 P202101 USABLE LOCAL PREFIXED
IMSI PT_IX01 P202102 USABLE LOCAL PREFIXED
IMSI PT_IX01 P202103 USABLE LOCAL PREFIXED
IMSI PT_IX01 P202104 USABLE LOCAL PREFIXED
IMSI PT_IX01 P202105 USABLE LOCAL PREFIXED
IMSI PT_IX01 P202106 USABLE LOCAL PREFIXED
IMSI PT_IX01 P202107 USABLE LOCAL PREFIXED
IMSI PT_IX01 P202108 USABLE LOCAL PREFIXED
IMSI PT_IX01 P202109 USABLE LOCAL PREFIXED
IMSI PT_IX01 P202110 USABLE LOCAL PREFIXED
IMSI PT_IX01 P202111 USABLE LOCAL PREFIXED
IMSI PT_IX01 P202112 USABLE LOCAL PREFIXED
IMSI PT_IX01 P_MAX USABLE LOCAL PREFIXED
13 rows selected.
|
인덱스 파티션별로 rebuild 수행
|
1
2
3
4
5
|
SQL> alter index pt_ix01 rebuild partition P_MAX;
Index altered.
Elapsed: 00:00:06.83
|
6.83초 소요됨
인덱스 생성 중 lock 확인
|
1
2
3
4
5
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
281 TM PTABLE 25051 0 2 0 0 0 25051
281 TM PTABLE P_MAX 25064 0 4 0 0 0 25064
|
PTABLE에 대해 LMODE 2(row-S (SS)) TM lock이 발생하고
PTABLE의 P_MAX 파티션에 대해 LMODE 4(share (S)) TM lock이 발생함
이때 rebuild 대상 파티션에 dml 시도
|
1
2
3
4
5
6
|
SQL> insert into ptable values ('20220101', 1);
(대기중)
SQL> update ptable set col2 = 99999 where col1 = '20220901' and col2 = 8193174;
(대기중)
SQL> delete ptable where col1 = '20220901' and col2 = 8193174;
(대기중)
|
dml을 완료하지 못하고 대기하고 있음
인덱스 rebuild 중 rebuild 대상 파티션에 dml 시 lock 확인
|
1
2
3
4
5
6
7
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
44 TM PTABLE P_MAX 25064 0 0 3 0 0 25064
44 TM PTABLE 25051 0 3 0 0 0 25051
281 TM PTABLE 25051 0 2 0 0 0 25051
281 TM PTABLE P_MAX 25064 0 4 0 1 0 25064
|
SID 281(인덱스 rebuild 세션) PTABLE 테이블 P_MAX 파티션의 LMODE 4(share (S)) TM lock에 의해
SID 44(dml 세션)이 LMODE 3(row-X)를 얻기 위해 대기하고 있음
그림에 의하면 LMODE 4 (share (S)) 와 LMODE 3 (row-X (SX)) 가 호환이 안된다고 나와있음
http://wiki.gurubee.net/display/STUDY/1.Lock
모든 트랜젝션 rollback 후 다음테스트 진행
이때 rebuild 비대상 파티션에 dml 시도
|
1
2
3
4
5
6
|
SQL> insert into ptable values ('20210505', 1);
1 row created.
SQL> update ptable set col2 = 99999 where col1 = '20210516' and col2 = 5959961;
1 row updated.
SQL> delete ptable where col1 = '20210516' and col2 = 5959961;
1 row deleted.
|
정상적으로 dml이 가능함
인덱스 rebuild 중 rebuild 비대상 파티션에 dml 시 lock 확인
|
1
2
3
4
5
6
7
8
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
44 TM PTABLE 25051 0 3 0 0 0 25051
44 TM PTABLE P202105 25056 0 3 0 0 0 25056
44 TX 589837 2086 6 0 0 9 13
281 TM PTABLE P_MAX 25064 0 4 0 0 0 25064
281 TM PTABLE 25051 0 2 0 0 0 25051
|
SID 281(인덱스 rebuild 세션) PTABLE 테이블 P_MAX 파티션의 LMODE 4(share (S)) TM lock가 있지만
SID 44(dml 세션)은 PTABLE 테이블 P202105 파티션의 데이터를 수정하기 때문에 해당 파티션에만 LMODE 3(row-X) TM lock을 가지고 있어
두 세션간 blocking 이 발생하지 않고 있음
online 옵션 사용 인덱스 rebuild 시 lock 확인 테스트
인덱스 파티션별로 rebuild 수행
|
1
2
3
4
5
|
SQL> alter index pt_ix01 rebuild partition P_MAX online;
Index altered.
Elapsed: 00:00:07.29
|
7.29초 소요됨
인덱스 생성 중 lock 확인
|
1
2
3
4
5
6
7
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
281 TM PTABLE P_MAX 25064 0 2 0 0 0 25064
281 TM PTABLE 25051 0 2 0 0 0 25051
281 TM SYS_JOURNAL_25296 25310 0 4 0 0 0 25310
281 TX 589845 2086 6 0 0 9 21
|
PTABLE과 PTABLE P_MAX 파티션에 대해 LMODE 2(row-S (SS)) TM lock이 발생함
그리고 SYS_JOURNAL_25296이라는 시스템 테이블에 LMODE 4(share (S)) TM lock이 발생함
이때 rebuild 대상 파티션에 dml 시도
|
1
2
3
4
5
6
|
SQL> insert into ptable values ('20220101', 1);
1 row created.
SQL> update ptable set col2 = 99999 where col1 = '20220901' and col2 = 8193174;
1 row updated.
SQL> delete ptable where col1 = '20220901' and col2 = 8193174;
1 row deleted.
|
정상적으로 dml이 가능함
인덱스 생성 중 dml 시 lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
44 TM PTABLE 25051 0 3 0 0 0 25051
44 TM PTABLE P_MAX 25064 0 3 0 0 0 25064
44 TX 196640 2094 6 0 0 3 32
281 TM PTABLE 25051 0 2 0 0 0 25051
281 TM SYS_JOURNAL_25296 25310 0 4 0 0 0 25310
281 TM PTABLE P_MAX 25064 0 2 0 0 0 25064
281 TX 589845 2086 6 0 0 9 21
7 rows selected.
|
SID 281(인덱스 생성세션)과 SID 44(dml 세션)이 문제 없이 각각 실행중임
하지만 인덱스 생성 중 dml은 잘 동작하지만 dml 트랜젝션을 종료(rollback, commit) 하지 않으면
인덱스 생성 세션은 계속 대기하고 있음
(테스트시 정상 생성 시간 13초가 지났는데도 계속 대기중이다가 rollback 시 바로 Index altered. 메세지가 나옴)
모든 트랜젝션 rollback 후 다음테스트 진행
이때 rebuild 비대상 파티션에 dml 시도
|
1
2
3
4
5
6
|
SQL> insert into ptable values ('20210505', 1);
1 row created.
SQL> update ptable set col2 = 99999 where col1 = '20210516' and col2 = 5959961;
1 row updated.
SQL> delete ptable where col1 = '20210516' and col2 = 5959961;
1 row deleted.
|
정상적으로 dml이 가능함
인덱스 rebuild 중 rebuild 비대상 파티션에 dml 시 lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> @lock
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
44 TM PTABLE P202105 25056 0 3 0 0 0 25056
44 TM PTABLE 25051 0 3 0 0 0 25051
44 TX 655365 1664 6 0 0 10 5
281 TM SYS_JOURNAL_25296 25322 0 4 0 0 0 25322
281 TM PTABLE P_MAX 25064 0 2 0 0 0 25064
281 TM PTABLE 25051 0 2 0 0 0 25051
281 TX 458766 1571 6 0 0 7 14
7 rows selected.
|
SID 281(인덱스 생성세션)과 SID 44(dml 세션)이 문제 없이 각각 실행중임
dml 트랜젝션을 종료(rollback, commit) 하지 않아도 인덱스 생성 세션이 정상적으로 완료됨
결론 :
online 옵션 미사용 인덱스 생성시 lock 확인 테스트 : lock 발생하여 정상 dml 불가
online 옵션 사용 인덱스 생성시 lock 확인 테스트 : lock 발생하지 않아 정상 dml 가능, 하지만 dml 트랜젝션 종료 이후 인덱스 생성이 완료됨
online 옵션 미사용 인덱스 rebuild 시 lock 확인 테스트 :
- rebuild 대상 파티션 lock : lock 발생하여 정상 dml 불가
- rebuild 비대상 파티션 lock : lock 발생하지 않아 정상 dml 가능
online 옵션 사용 인덱스 rebuild 시 lock 확인 테스트
- rebuild 대상 파티션 lock : lock 발생하지 않아 정상 dml 가능, 하지만 dml 트랜젝션 종료 이후 인덱스 rebuild가 완료됨
- rebuild 비대상 파티션 lock : lock 발생하지 않아 정상 dml 가능
시간 비교
online 옵션 미사용 인덱스 생성시 : 11.52초
online 옵션 사용 인덱스 생성시 : 13.14초
online 옵션 미사용 인덱스 rebuild 시 : 6.83초
online 옵션 사용 인덱스 rebuild 시 : 7.29초
online 옵션을 사용 했을때가 사용하지 않았을때보다 더 느림을 확인할 수 있음
참조 :
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 파티션 테이블 drop 작업 시 인덱스 영향도 확인 (0) | 2024.01.25 |
|---|---|
| 오라클 19c 파티션 테이블 add 작업 시 인덱스 영향도 확인 (0) | 2024.01.25 |
| 오라클 19c 잘못된 dns 주소로 인한 sqlplus tns 연결 지연 문제 (0) | 2024.01.16 |
| 오라클 19c 파티션 테이블 impdp 시 lock 확인 (0) | 2024.01.10 |
| 오라클 19c sqlplus 에서의 rollback 커맨드 신기한점 (0) | 2023.12.26 |