OS 환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 테이블 initrans 에 따른 lock 테스트
데이터베이스에서 동시성 제어는 중요한 이슈 중 하나임
오라클 db 환경에서 동일 블록에 대해 여러 트랜잭션이 동시에 접근하려는 경우, ITL(Interested Transaction List) 엔트리가 부족하여 트랜잭션이 대기 상태에 빠지는 상황이 발생할 수 있음
이때 발생하는 대기 이벤트는 enq: TX - allocate ITL entry 임
Oracle GoldenGate(OGG) 같은 데이터 동기화 솔루션을 사용하는 경우에도 동기화 대상이 많은 경우 이 문제가 발생할 수 있음
이 문제에 대한 해법은 테이블의 initrans 값을 올리는것임(initrans 는 동일 블록에 대해 트랜젝션을 제한하는 테이블 속성임)
하지만 initrans 를 변경하더라도 기존 데이터는 initrans 설정이 변경되지 않기 때문에
아래와 같이 데이터를 재생성하는 작업을 해줘야함
- exp/imp 또는 expdp/impdp 작업
- alter table move 작업
- dbms_redefenition 작업
참고로 변경 이후에 추가된 데이터는 변경된 initrans 값에 의해 제한받음
본문에서는 initrans를 초기에 2로 설정 한 후 lock 및 이벤트를 관측해보고, 이후 initrans를 3으로 변경했을때 어떻게 동작하는지를 확인해봄
테스트
1. initrans 2로 설정 후 기존 데이터에 대한 트랙잭션 확인
2. initrans 3로 변경 후 기존 데이터에 대한 트랙잭션 확인
3. initrans 3로 변경 후 추가 데이터에 대한 트랙잭션 확인
4. initrans 3로 변경 후 move 후 기존 데이터에 대한 트랙잭션 확인
5. initrans 10으로 변경 후 move 후 block dump 확인
테스트
1. initrans 2로 설정 후 기존 데이터에 대한 트랙잭션 확인
샘플 테이블 생성
1
2
3
4
5
|
SQL>
drop table itl_test purge;
create table itl_test (col1 number, col2 char(5)) pctfree 0 initrans 2;
Table created.
|
참고로 아래 링크 테스트에 의하면 initrans를 1로 설정 시 dba_tables에는 initrans가 1로 표시되지만 실제 최소 값은 2로 자동으로 재설정됨(19c에서 테스트해도 동일했음)
https://connor-mcdonald.com/2022/02/16/intrans-grabs-a-little-more-space/
*오라클 코어 54p에서 9i 부터 이렇게 변경되었나고 나와있음
본문 테스트에서는 테스트 이해도를 올리기 위해 처음부터 initrans를 2로 설정함
initrans 값 확인
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col owner for a15
col table_name for a10
select a.owner, a.table_name, a.ini_trans, a.max_trans
from dba_tables a
where a.table_name = 'ITL_TEST';
OWNER TABLE_NAME INI_TRANS MAX_TRANS
--------------- ---------- ---------- ----------
IMSI ITL_TEST 2 255
|
initrans가 2, maxtrans가 255임
샘플 데이터 삽입
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
insert into itl_test
select level, 'x'
from dual
connect by level <= 5000;
5000 rows created.
SQL> commit;
Commit complete.
|
업데이트할 row 의 block 확인
1
2
3
4
5
6
7
8
|
SQL>
select distinct dbms_rowid.rowid_block_number(rowid)
from itl_test
where col1 in (1,2,3);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
29364
|
col1이 1, 2, 3인 데이터 모두 29364번 블록에 들어있음
세션1에서 col1이 1인 row update 시도
1
2
3
4
5
6
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 1;
1 row updated.
|
update가 잘됨
세션2에서 col1이 2인 row update 시도
1
2
3
4
5
6
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 2;
1 row updated.
|
update가 잘됨
세션3에서 col1이 3인 row update 시도
1
2
3
4
5
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 3;
(대기중)
|
initrans를 2로 설정했기 때문에 동일 블록에 대해 3개 세션에서 동시에 수정이 불가능함
ash 확인
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 wait_class for a15
col event for a50
select * from (
select
WAIT_CLASS ,
EVENT,
count(sample_time) as EST_SECS_IN_WAIT
from v$active_session_history
where sample_time between sysdate - interval '30' second and sysdate
group by WAIT_CLASS,EVENT
order by count(sample_time) desc
)
where rownum <6;
WAIT_CLASS EVENT EST_SECS_IN_WAIT
--------------- -------------------------------------------------- ----------------
Configuration enq: TX - allocate ITL entry 29
3
|
initrans 제한으로 인해 대기시 enq: TX - allocate ITL entry 이벤트가 발생함
블록 덤프 확인을 위해 datafile number, block number 확인
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
select dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid_block_number(rowid) blk_number
from itl_test
where col1 = 1;
FNO BLK_NUMBER
---------- ----------
5 29364
SQL> alter system dump datafile 5 block 29364;
System altered.
|
덤프 수집됨
덤프파일 경로 확인
1
2
3
4
5
6
7
8
9
|
SQL>
set lines 200 pages 1000
col value for a100
select value from v$diag_info
where name = 'Default Trace File';
VALUE
----------------------------------------------------------------------------------------------------
/ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_43177.trc
|
덤프파일 확인
(Block header dump 검색하여 확인)
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
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_43177.trc
Block header dump: 0x014072b4
Object id on Block? Y
seg/obj: 0xe252 csc: 0x0000000003509454 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x14072b0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x00cc.002.000065b6 0x01c15d87.0db7.1c C--- 0 scn 0x0000000003509449
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x014072b4
data_block_dump,data header at 0xc4718064
===============
tsiz: 0x1f98
hsiz: 0x452
pbl: 0xc4718064
76543210
flag=--------
ntab=1
nrow=544
frre=-1
fsbo=0x452
fseo=0x460
avsp=0xe
tosp=0xe
0xe:pti[0] nrow=544 offs=0
0x12:pri[0] offs=0x130a
0x14:pri[1] offs=0x1316
0x16:pri[2] offs=0x1322
0x18:pri[3] offs=0x132e
|
itl 슬롯이 2개인것을 확인할 수 있음
update 모두 rollback
1
2
3
4
5
6
|
#세션1
SQL> rollback;
#세션2
SQL> rollback;
#세션3
SQL> rollback;
|
2. initrans 3로 변경 후 기존 데이터에 대한 트랙잭션 확인
테이블의 initrans 값 상향(2 -> 3)
1
2
3
|
SQL> alter table itl_test initrans 3;
Table altered.
|
변경된 initrans 값 확인
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col owner for a15
col table_name for a10
select a.owner, a.table_name, a.ini_trans, a.max_trans
from dba_tables a
where a.table_name = 'ITL_TEST';
OWNER TABLE_NAME INI_TRANS MAX_TRANS
--------------- ---------- ---------- ----------
IMSI ITL_TEST 3 255
|
initrans가 3으로 변경됨
트랜젝션 테스트 재수행
세션1에서 col1이 1인 row update 시도
1
2
3
4
5
6
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 1;
1 row updated.
|
update가 잘됨
세션2에서 col1이 2인 row update 시도
1
2
3
4
5
6
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 2;
1 row updated.
|
update가 잘됨
세션3에서 col1이 3인 row update 시도
1
2
3
4
5
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 3;
(대기중)
|
initrans를 3으로 변경했지만 3번째 변경 시도시 lock이 걸려 대기함
블록 덤프 재수행 후 확인
덤프파일 확인
(Block header dump 검색하여 확인)
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
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_27365.trc
Block header dump: 0x014072b4
Object id on Block? Y
seg/obj: 0xe252 csc: 0x0000000003509454 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x14072b0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x00cc.002.000065b6 0x01c15d87.0db7.1c C--- 0 scn 0x0000000003509449
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x014072b4
data_block_dump,data header at 0xc4718064
===============
tsiz: 0x1f98
hsiz: 0x452
pbl: 0xc4718064
76543210
flag=--------
ntab=1
nrow=544
frre=-1
fsbo=0x452
fseo=0x460
avsp=0xe
tosp=0xe
0xe:pti[0] nrow=544 offs=0
0x12:pri[0] offs=0x130a
0x14:pri[1] offs=0x1316
0x16:pri[2] offs=0x1322
0x18:pri[3] offs=0x132e
|
itl 슬롯이 여전히 3개가 아닌 2개임
initrans를 변경하더라도 기존 데이터는 initrans 설정이 변경되지 않기 때문에
아래와 같이 데이터를 재생성하는 작업을 해줘야함
- exp/imp 또는 expdp/impdp 작업
- alter table move 작업
- dbms_redefenition 작업
참고로 변경 이후에 추가된 데이터는 변경된 initrans 값에 의해 제한받음
update 모두 rollback
1
2
3
4
5
6
|
#세션1
SQL> rollback;
#세션2
SQL> rollback;
#세션3
SQL> rollback;
|
3. initrans 3로 변경 후 추가 데이터에 대한 트랙잭션 확인
추가 데이터 삽입
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
insert into itl_test
select level+5000, 'x'
from dual
connect by level <= 5000;
5000 rows created.
SQL> commit;
Commit complete.
|
업데이트할 row 의 block 확인
1
2
3
4
5
6
7
8
|
SQL>
select distinct dbms_rowid.rowid_block_number(rowid)
from itl_test
where col1 in (9001,9002,9003,9004);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
29381
|
col1이 9001, 9002, 9003인 데이터 모두 29381번 블록에 들어있음
세션1에서 col1이 9001인 row update 시도
1
2
3
4
5
6
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 9001;
1 row updated.
|
update가 잘됨
세션2에서 col1이 9002인 row update 시도
1
2
3
4
5
6
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 9002;
1 row updated.
|
update가 잘됨
세션3에서 col1이 9003인 row update 시도
1
2
3
4
5
6
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 9003;
1 row updated.
|
update가 잘됨
initrans를 3으로 설정했기 때문에 동일 블록에 대해 3개 세션에서 동시에 수정이 가능함
세션4에서 col1이 9004인 row update 시도
1
2
3
4
5
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 9004;
(대기중)
|
initrans를 3으로 설정했기 때문에 동일 블록에 대해 4개 세션에서 동시에 수정이 불가능함
update 모두 rollback
1
2
3
4
5
6
7
8
|
#세션1
SQL> rollback;
#세션2
SQL> rollback;
#세션3
SQL> rollback;
#세션4
SQL> rollback;
|
4. initrans 3로 변경 후 move 후 기존 데이터에 대한 트랙잭션 확인
테이블 move 진행
1
2
3
|
SQL> alter table itl_test move tablespace users;
Table altered.
|
업데이트할 row 의 block 확인
1
2
3
4
5
6
7
8
|
SQL>
select distinct dbms_rowid.rowid_block_number(rowid)
from itl_test
where col1 in (1,2,3);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
164
|
col1이 1, 2, 3인 데이터 모두 164번 블록에 들어있음
세션1에서 col1이 1인 row update 시도
1
2
3
4
5
6
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 1;
1 row updated.
|
update가 잘됨
세션2에서 col1이 2인 row update 시도
1
2
3
4
5
6
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 2;
1 row updated.
|
update가 잘됨
세션3에서 col1이 3인 row update 시도
1
2
3
4
5
6
|
SQL>
update itl_test
set col2 = 'y'
where col1 = 3;
1 row updated.
|
update가 잘됨
initrans를 3로 설정했기 때문에 동일 블록에 대해 3개 세션에서 동시에 수정이 가능함
테스트3과 마찬가지로 동일 블록을 4번째 세션이 update 시도할 때에는 lock이 발생함
블록 덤프 확인을 위해 datafile number, block number 확인
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
select dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid_block_number(rowid) blk_number
from itl_test
where col1 = 1;
FNO BLK_NUMBER
---------- ----------
6 164
SQL> alter system dump datafile 6 block 164;
System altered.
|
덤프 수집됨
덤프파일 경로 확인
1
2
3
4
5
6
7
8
9
|
SQL>
set lines 200 pages 1000
col value for a100
select value from v$diag_info
where name = 'Default Trace File';
VALUE
----------------------------------------------------------------------------------------------------
/ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_9701.trc
|
덤프파일 확인
(Block header dump 검색하여 확인)
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
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_9701.trc
Block header dump: 0x018000a4
Object id on Block? Y
seg/obj: 0xe253 csc: 0x000000000350a1a7 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x18000a0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x00cd.020.000065a8 0x00000000.0000.00 C--- 0 scn 0x000000000350a17a
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x018000a4
data_block_dump,data header at 0x8aae807c
===============
tsiz: 0x1f80
hsiz: 0x44e
pbl: 0x8aae807c
76543210
flag=--------
ntab=1
nrow=542
frre=-1
fsbo=0x44e
fseo=0x462
avsp=0x14
tosp=0x14
0xe:pti[0] nrow=542 offs=0
0x12:pri[0] offs=0x1f73
0x14:pri[1] offs=0x1f66
0x16:pri[2] offs=0x1f5a
0x18:pri[3] offs=0x1f4e
|
itl 슬롯이 3개인것을 확인할 수 있음
5. initrans 10으로 변경 후 move 후 block dump 확인
테이블의 initrans 값 상향(3 -> 10)
1
2
3
|
SQL> alter table itl_test initrans 10;
Table altered.
|
변경된 initrans 값 확인
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col owner for a15
col table_name for a10
select a.owner, a.table_name, a.ini_trans, a.max_trans
from dba_tables a
where a.table_name = 'ITL_TEST';
OWNER TABLE_NAME INI_TRANS MAX_TRANS
--------------- ---------- ---------- ----------
IMSI ITL_TEST 10 255
|
initrans가 10으로 변경됨
테이블 move 진행
1
2
3
|
SQL> alter table itl_test move tablespace users;
Table altered.
|
블록 덤프 확인을 위해 datafile number, block number 확인
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
select dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid_block_number(rowid) blk_number
from itl_test
where col1 = 1;
FNO BLK_NUMBER
---------- ----------
6 188
SQL> alter system dump datafile 6 block 188;
System altered.
|
덤프 수집됨
덤프파일 경로 확인
1
2
3
4
5
6
7
8
9
|
SQL>
set lines 200 pages 1000
col value for a100
select value from v$diag_info
where name = 'Default Trace File';
VALUE
----------------------------------------------------------------------------------------------------
/ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_63277.trc
|
덤프파일 확인
(Block header dump 검색하여 확인)
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
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_63277.trc
Block header dump: 0x018000bc
Object id on Block? Y
seg/obj: 0xe3d9 csc: 0x0000000003534586 itc: 10 flg: E typ: 1 - DATA
brn: 0 bdba: 0x18000b8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x00c6.005.00006678 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x06 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x07 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x08 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x09 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x018000bc
data_block_dump,data header at 0x89b0a124
===============
tsiz: 0x1ed8
hsiz: 0x438
pbl: 0x89b0a124
76543210
flag=--------
ntab=1
nrow=531
frre=-1
fsbo=0x438
fseo=0x449
avsp=0x11
tosp=0x11
0xe:pti[0] nrow=531 offs=0
0x12:pri[0] offs=0x1ecb
0x14:pri[1] offs=0x1ebe
0x16:pri[2] offs=0x1eb1
0x18:pri[3] offs=0x1ea4
|
itl 슬롯이 10개인것을 확인할 수 있음
현재 이 블록에 어떤 트랜잭션도 없기 때문에 0x01번 xid를 제외하고 모두 0으로 채워져있음
결론 :
본문 테스트를 통해 initrans 값이 트랜잭션 동시성에 미치는 영향을 확인할 수 있었음
initrans 값을 2에서 3으로 변경했을 때 신규 데이터는 변경된 initrans 값을 적용받아 동일 블록에 대해 3개의 트랜잭션이 동시에 처리될 수 있었음
하지만 기존 데이터는 initrans 값이 변경되지 않아 동일한 블록에서 여전히 2개의 트랜잭션만 처리될 수 있었음
그렇기 때문에 기존 데이터에도 변경된 initrans 값을 적용하려면 exp/imp, expdp/impdp, table move, dbms_redefinition 등의 작업을 통해 데이터를 재생성 해줘야함
운영 환경에서 initrans 값을 조정할 때는 다음 사항을 고려해야함
- 테이블에 저장된 데이터의 양과 트랜잭션 패턴을 분석하여 적절한 initrans 값을 설정
- 데이터 재생성 작업에 따른 다운타임과 리소스 소모를 고려
- 변경 후 충분한 테스트를 통해 성능 향상 여부를 검증
참고용
블록별 저장되어 있는 값 모두 확인
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
select distinct dbms_rowid.rowid_block_number(rowid) as block_number,
listagg(col1, '|') within group (order by col1) as data
from itl_test
group by dbms_rowid.rowid_block_number(rowid)
order by 1;
(데이터 출력결과 예시)
BLOCK_NUMBER DATA
------------ ------------------
29364 1|2|3|4|5|6|7|8|9|10...
29365 ...
|
참조 :
1472175.1, 549074.1, 756121.1
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/physical_attributes_clause.html#GUID-A15063A9-3237-43D3-B0AE-D01F6E80B393
https://connor-mcdonald.com/2022/02/16/intrans-grabs-a-little-more-space/
https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?mod=document&uid=237742
오라클 코어 서적 53p
'ORACLE > Admin' 카테고리의 다른 글
오라클 19c 인덱스 리빌드 전후 용량 차이 확인 테스트 (0) | 2024.06.17 |
---|---|
오라클 23ai 신기능 UMM(Unified Memory Management) 통합메모리 관리 (0) | 2024.06.14 |
오라클 23ai 신기능 일반 에러 메세지 개선 (0) | 2024.06.02 |
오라클 19c commit 기본 옵션 정리 및 테스트 (0) | 2024.05.27 |
오라클 19c sqlplus 의 history 명령어 (0) | 2024.05.15 |