프린트 하기

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