Oracle enq: TX - allocate ITL entry 구현

필유아사 2026. 6. 12. 16:44

2026. 6. 12. 16:44

🔍 Step 1. 테스트용 테이블 생성

ITL 개수를 결정하는 INITRANS와 MAXTRANS를 가장 낮게 설정하고,

데이터가 여러 블록으로 쪼개지지 않도록 하나의 블록에 최대한 모아서 넣습니다.

1. 테스트 테이블 생성

-- 1. 테스트 테이블 생성 (INITRANS와 MAXTRANS를 최소값인 1과 2로 설정)
CREATE TABLE itl_test_tbl (
    id   NUMBER,
    val  VARCHAR2(100)
)
INITRANS 1
MAXTRANS 2 -- Oracle 10g 이후 버전부터 MAXTRANS 값은 내부적으로 무조건 256으로 고정
PCTFREE 0; -- 블록 내 여유 공간을 0으로 만들어 ITL이 동적으로 확장될 공간을 원천 차단

2. 동일한 데이터 블록에 행들이 들어가도록 연속으로 INSERT

INSERT INTO itl_test_tbl VALUES (1, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (2, 'BBBBBBBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (3, 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC');
INSERT INTO itl_test_tbl VALUES (4, 'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
INSERT INTO itl_test_tbl VALUES (5, 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');
INSERT INTO itl_test_tbl VALUES (6, 'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG');
INSERT INTO itl_test_tbl VALUES (7, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
INSERT INTO itl_test_tbl VALUES (8, 'IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII');
INSERT INTO itl_test_tbl VALUES (9, 'JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ');
INSERT INTO itl_test_tbl VALUES (10, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (11, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (12, 'BBBBBBBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (13, 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC');
INSERT INTO itl_test_tbl VALUES (14, 'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
INSERT INTO itl_test_tbl VALUES (15, 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');
INSERT INTO itl_test_tbl VALUES (16, 'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG');
INSERT INTO itl_test_tbl VALUES (17, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
INSERT INTO itl_test_tbl VALUES (18, 'IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII');
INSERT INTO itl_test_tbl VALUES (19, 'JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ');
INSERT INTO itl_test_tbl VALUES (20, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (21, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (22, 'BBBBBBBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (23, 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC');
INSERT INTO itl_test_tbl VALUES (24, 'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
INSERT INTO itl_test_tbl VALUES (25, 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');
INSERT INTO itl_test_tbl VALUES (26, 'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG');
INSERT INTO itl_test_tbl VALUES (27, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
INSERT INTO itl_test_tbl VALUES (28, 'IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII');
INSERT INTO itl_test_tbl VALUES (29, 'JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ');
INSERT INTO itl_test_tbl VALUES (30, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (31, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (32, 'BBBBBBBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (33, 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC');
INSERT INTO itl_test_tbl VALUES (34, 'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
INSERT INTO itl_test_tbl VALUES (35, 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');
INSERT INTO itl_test_tbl VALUES (36, 'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG');
INSERT INTO itl_test_tbl VALUES (37, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
INSERT INTO itl_test_tbl VALUES (38, 'IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII');
INSERT INTO itl_test_tbl VALUES (39, 'JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ');
INSERT INTO itl_test_tbl VALUES (40, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (41, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (42, 'BBBBBBBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (43, 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC');
INSERT INTO itl_test_tbl VALUES (44, 'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
INSERT INTO itl_test_tbl VALUES (45, 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');
INSERT INTO itl_test_tbl VALUES (46, 'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG');
INSERT INTO itl_test_tbl VALUES (47, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
INSERT INTO itl_test_tbl VALUES (48, 'IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII');
INSERT INTO itl_test_tbl VALUES (49, 'JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ');
INSERT INTO itl_test_tbl VALUES (50, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (51, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (52, 'BBBBBBBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (53, 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC');
INSERT INTO itl_test_tbl VALUES (54, 'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
INSERT INTO itl_test_tbl VALUES (55, 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');
INSERT INTO itl_test_tbl VALUES (56, 'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG');
INSERT INTO itl_test_tbl VALUES (57, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
INSERT INTO itl_test_tbl VALUES (58, 'IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII');
INSERT INTO itl_test_tbl VALUES (59, 'JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ');
INSERT INTO itl_test_tbl VALUES (60, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (61, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (62, 'BBBBBBBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (63, 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC');
INSERT INTO itl_test_tbl VALUES (64, 'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
INSERT INTO itl_test_tbl VALUES (65, 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');
INSERT INTO itl_test_tbl VALUES (66, 'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG');
INSERT INTO itl_test_tbl VALUES (67, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
INSERT INTO itl_test_tbl VALUES (68, 'IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII');
INSERT INTO itl_test_tbl VALUES (69, 'JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ');
INSERT INTO itl_test_tbl VALUES (70, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (71, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (72, 'BBBBBBBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (73, 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC');
INSERT INTO itl_test_tbl VALUES (74, 'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
INSERT INTO itl_test_tbl VALUES (75, 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');
INSERT INTO itl_test_tbl VALUES (76, 'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG');
INSERT INTO itl_test_tbl VALUES (77, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
INSERT INTO itl_test_tbl VALUES (78, 'IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII');
INSERT INTO itl_test_tbl VALUES (79, 'JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ');
INSERT INTO itl_test_tbl VALUES (80, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (81, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (82, 'BBBBBBBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (83, 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC');
INSERT INTO itl_test_tbl VALUES (84, 'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
INSERT INTO itl_test_tbl VALUES (85, 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');
INSERT INTO itl_test_tbl VALUES (86, 'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG');
INSERT INTO itl_test_tbl VALUES (87, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
INSERT INTO itl_test_tbl VALUES (88, 'IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII');
INSERT INTO itl_test_tbl VALUES (89, 'JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ');
INSERT INTO itl_test_tbl VALUES (90, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (91, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (92, 'BBBBBBBBBBBBBBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
INSERT INTO itl_test_tbl VALUES (93, 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC');
INSERT INTO itl_test_tbl VALUES (94, 'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
INSERT INTO itl_test_tbl VALUES (95, 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');
INSERT INTO itl_test_tbl VALUES (96, 'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG');
INSERT INTO itl_test_tbl VALUES (97, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
INSERT INTO itl_test_tbl VALUES (98, 'IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII');
INSERT INTO itl_test_tbl VALUES (99, 'JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ');

commit;

3. 데이터가 실제로 같은 블록에 있는지 확인
SELECT id, dbms_rowid.rowid_block_number(rowid) as block_no
FROM itl_test_tbl;
-- 같은 Block에 저장된 데이터 확인;

* 본 테스트에서는 아래와 같이 id=79 까지는 같은 block에 저장됨
        77     613313
        78     613313
        79     613313
        80     613314
        81     613314

🔍 Step 2. 동시 다발적 트랜잭션 실행 (여러개의 세션 필요)

MAXTRANS를 256로 설정되기 때문에, 이 블록을 동시에 수정할 수 있는 세션은 테스트 환경마다 상이할 수 있습니다.

⚠️ 주의: 각각 **다른 행(Row)**을 업데이트해야 합니다. 같은 행을 업데이트하면 ITL 부족이 아니라 일반적인 Row Lock(enq: TX - row lock contention)이 걸립니다.

세션 1 (Session A):

SQL

UPDATE itl_test_tbl SET val = 'WORK_A' WHERE id = 1;
-- COMMIT 하지 않고 대기 (ITL 슬롯 1개 점유)

세션 2 (Session B):

SQL

UPDATE itl_test_tbl SET val = 'WORK_B' WHERE id = 2;
-- -- COMMIT 하지 않고 대기 (ITL 슬롯 2개 점유)

세션 3 (Session C):

SQL

UPDATE itl_test_tbl SET val = 'WORK_C' WHERE id = 3;
-- -- COMMIT 하지 않고 대기 (ITL 슬롯 3개 점유)

세션 4 (Session D):
SQL

UPDATE itl_test_tbl SET val = 'WORK_C' WHERE id = 4;
-- -- COMMIT 하지 않고 대기 (ITL 슬롯 4개 점유)

본 테스트에서는 세션 5, 세션 6에서 UPDATE 수행 후 commit 하지 않고 대기

세션 7 (Session G):

UPDATE itl_test_tbl SET val = 'WORK_G' WHERE id = 7;
-- 실행하는 순간 락이 걸리며 세션이 멈춤 (대기 발생!)

==> UPDATE 가 락이 걸릴 때까지 새로운 session에서 id를 변경하면서 수행

UPDATE itl_test_tbl SET val = 'WORK_G' WHERE id = 7;

본 테스트에서는 7번째 Session 에서 Lock 이 발생함.

🔍 Step 3. 모니터링 세션에서 대기 이벤트 확인

세션이 멈춰있는 상태(본 테스트에서는 7번째 session)에서, 별도의 모니터링 창을 열어 아래 쿼리를 수행합니다.

SQL

SELECT 
    sid, 
    blocking_session, 
    event, 
    p1, p2, p3, 
    seconds_in_wait
FROM v$session 
WHERE event LIKE '%ITL%';

📊 테스트 결과 출력

SID	BLOCKING_SESSION	EVENT	P1	P2	P3	SECONDS_IN_WAIT
389	5,863	enq: TX - allocate ITL entry	1,415,053,316	62,586,881	24,382	30

여기에서 BLOCKING_SESSION은

특징: 일반적인 Row Lock 경합과 달리 blocking_session이 명확하게 지정되지 않을 수 있습니다. 특정 세션이 나를 막은 게 아니라 "블록 자체의 방(ITL)이 꽉 차서" 못 들어가는 것이기 때문입니다.

🔍 추가로 Row lock test

본 테스트에서는 추가로 row lock test를 진행했습니다.

세션 8 (Session H):

SQL

UPDATE itl_test_tbl SET val = 'WORK_H' WHERE id = 4;
-- row lock으로 대기

아래와 같이 ITL lock과 row lock을 확인할 수 있다.

SID	BLOCKING_SESSION	EVENT	SECONDS_IN_WAIT
---------------------------------------------------------
389	5,863	enq: TX - allocate ITL entry	1,937
3,044	5,863	enq: TX - row lock contention	1,689

저작자표시 비영리 변경금지 (새창열림)

'ORACLE' 카테고리의 다른 글

java로 oracle DB 접속 테스트 (0)	2025.12.04
Oracle HR ERD (0)	2022.02.03
Oracle hint (0)	2022.02.02
DBMS_XPLAN 패키지 실행 권한 (0)	2022.02.01
Oracle - 실행계획 보는 방법 (0)	2022.01.31

잡학문구