[아이티윌 오라클 DBA 과정 91기] 260129 TIL
2026. 1. 30. 09:45ㆍCourses/아이티윌 오라클 DBA 과정
Lock(Cont.)
인덱스를 online으로 생성 중에 delete 수행하는 경우
enq: TX - row lock contention
HR1 : 인덱스를 온라인으로 생성
HR@ora19c> create index hr.emp_name_idx on hr.emp(last_name, first_name) online tablespace users;
# 대기 중 HR2 : 데이터 삭제
HR@ora19c> delete from hr.emp where emp_id = 100;
1 row deleted.Lock 조회
SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 2 3 4 5
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
139 TM 75338 0 2 0 1 0 1 9802 0
139 TM 75368 0 4 0 1 0 1 9832 0
139 TX 327689 2609 6 0 1 0 5 9 2609
165 TX 655367 2097 6 0 0 0 10 7 2097
165 TM 75338 0 3 0 0 0 1 9802 0
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TX%'; 2 3
no rows selected# 75338번 객체 조회
SYS@ora19c> select owner, object_name, object_type from dba_objects where object_id = '75338';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -----------------------
HR EMP TABLE
# 75368번 객체 조회
SYS@ora19c> select owner, object_name, object_type from dba_objects where object_id = '75368';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -----------------------
HR SYS_JOURNAL_75367 TABLE- 두 세션이 75338 객체(EMP 테이블)에 TM Lock을 각각 RS(2)모드와 RX(3)모드로 획득 → 호환 가능
- 139번 세션이 75368 객체(journal 테이블)에 TM Lock을 S(4)모드로 획득
- 165번 세션은 EMP 테이블 100번 사원 삭제 (ID1 : 655367, ID2 : 2097)
- 139번 세션은 journal 테이블에 165번이 변경한 내용 작성(ID1 : 327689, ID2 : 2609)
SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 2 3 4 5
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
139 TX 327689 2609 6 0 16 0 5 9 2609
139 TM 75368 0 4 0 16 0 1 9832 0
139 TX 327686 2610 6 0 7 0 5 6 2610
139 TM 75338 0 2 0 16 0 1 9802 0
165 TX 655367 2097 6 0 15 0 10 7 2097
165 TM 75338 0 3 0 15 0 1 9802 0
6 rows selected.- 165번 세션이 EMP 테이블 100번 사원을 삭제하는 작업과 139번 세션이 journal 테이블에 165번 세션의 변경 사항 작성 작업은 아직 commit/rollback 전이므로 그대로 유지
- 139번 세션이 인덱스 생성 작업 수행하면서 새로운 TX Lock 추가됨(ID1 : 327686, ID2 : 2610)
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TX%'; 2 3
no rows selected
SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 2 3 4 5
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
139 TX 655367 2097 0 4 1 0 10 7 2097
139 TM 75338 0 2 0 40 0 1 9802 0
139 TX 327689 2609 6 0 40 0 5 9 2609
139 TM 75368 0 4 0 40 0 1 9832 0
165 TX 655367 2097 6 0 39 1 10 7 2097
165 TM 75338 0 3 0 39 0 1 9802 0
6 rows selected.
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TX%'; 2 3
SID SERIAL# USERNAME BLOCKING_SESSION EVENT SQL_ID PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------------------------------- ------------- -------------
139 44156 HR 165 enq: TX - row lock contention grrzv4vsk52vd apa0wxbh5gdyj
SYS@ora19c> select sql_text from v$sql where sql_id = 'grrzv4vsk52vd';
SQL_TEXT
----------------------------------------------------------------------------------------------------
create index hr.emp_name_idx on hr.emp(last_name, first_name) online tablespace users- 100번 사원 row에 대해서 TX Lock 경합 발생
- 165번 세션이 X(6)모드로 TX Lock을 가지고 있는 중에 139번 세션이 S(4)모드로 TX Lock 요청 중
HR 2 : temporary journal 테이블 조회
HR@ora19c> select * from SYS_JOURNAL_75367;
C0 C1 O PARTNO RID
------------------------- -------------------- - ---------- ------------------
Grant Douglas D 0 D/////AAHAAACpmACk- 온라인 중에 인덱스 생성 또는 재생성 중에 발생하는 데이터의 변경에 대해서 temporary journal table을 만들어서 그 변경사항을 저장
- 인덱스 생성 또는 재생성이 끝나면 만들어진 인덱스 내에 journal table 레코드를 병합 시킴
- SYS에서는 조회 불가
SYS@ora19c> select * from SYS_JOURNAL_75367;
no rows selectedrollback
HR@ora19c> rollback;
Rollback complete.
HR@ora19c> select * from SYS_JOURNAL_75367;
select * from SYS_JOURNAL_75367
*
ERROR at line 1:
ORA-00942: table or view does not exist- HR2 롤백 수행 시 HR1에서 인덱스가 생성되고 모든 락은 해제됨
- journal 테이블을 삭제됨
index rebuild 하는 중에 트랜잭션이 발생하는 경우
HR1 : 인덱스 rebuild
alter index hr.emp_name_idx rebuild;HR2 : 데이터 삭제
delete from hr.emp where emp_id = 100;Lock 조회
SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 2 3 4 5
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
139 TM 75338 0 4 0 2 1 1 9802 0
165 TM 75338 0 0 3 1 0 1 9802 0
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TM%' or event like '%TX%'; 3
SID SERIAL# USERNAME BLOCKING_SESSION EVENT SQL_ID PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------------------------------- ------------- -------------
127 41737 VKTM Logical Idle Wait
165 40846 HR 139 enq: TM - contention 6zq3b95sw9agv 6zq3b95sw9agv
SYS@ora19c> select sql_text from v$sql where sql_id = '6zq3b95sw9agv';
SQL_TEXT
----------------------------------------------------------------------------------------------------
delete from hr.emp where emp_id = 100 - 139번 세션은 인덱스 rebuild → TM Lock S(4)모드
- 165번 세션은 100번 사원 삭제 → TM Lock RX(3) 모드
- S와 RX는 호환 X → TM Lock 경합 발생
SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
139 TM 75338 0 4 0 12 1 1 9802 0
139 TX 458754 2131 6 0 4 0 7 2 2131
165 TM 75338 0 0 3 11 0 1 9802 0
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TM%' or event like '%TX%'; 2 3
SID SERIAL# USERNAME BLOCKING_SESSION EVENT SQL_ID PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------------------------------- ------------- -------------
127 41737 VKTM Logical Idle Wait
165 40846 HR 139 enq: TM - contention 6zq3b95sw9agv 6zq3b95sw9agv- 139번 세션은 인덱스 rebuild 작업 진행 → TX Lock X(6)모드로 획득
- 165번 세션은 TM Lock을 획득하지 못해서 대기 중
SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 2 3 4 5
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
165 TX 65541 2142 6 0 3 0 1 5 2142
165 TM 75338 0 3 0 3 0 1 9802 0
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TM%' or event like '%TX%'; 2 3
SID SERIAL# USERNAME BLOCKING_SESSION EVENT SQL_ID PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------------------------------- ------------- -------------
127 41737 VKTM Logical Idle Wait- 인덱스 rebuild 작업이 끝나면 165번 세션이 TM Lock RX(3) 모드로 획득하고 TX Lock X(6) 모드로 획득
index rebuild online 하는 중에 트랜잭션이 발생하는 경우
HR1 : 인덱스를 온라인으로 rebuild
alter index hr.emp_name_idx rebuild online;HR2 : 데이터 삭제
delete from hr.emp where emp_id = 100;Lock 조회
SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 2 3 4 5
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
139 TM 75338 0 2 0 2 0 1 9802 0
139 TM 75374 0 4 0 2 0 1 9838 0
139 TX 458755 2139 6 0 2 0 7 3 2139
165 TX 655382 2108 6 0 1 0 10 22 2108
165 TM 75338 0 3 0 1 0 1 9802 0
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TM%' or event like '%TX%'; 2 3
SID SERIAL# USERNAME BLOCKING_SESSION EVENT SQL_ID PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------------------------------- ------------- -------------
127 41737 VKTM Logical Idle Wait# 75338번 오브젝트 조회
SYS@ora19c> select owner, object_name, object_type from dba_objects where object_id = '75338';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -----------------------
HR EMP TABLE
# 75374번 오브젝트 조회
SYS@ora19c> select owner, object_name, object_type from dba_objects where object_id = '75374';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -----------------------
HR SYS_JOURNAL_75367 TABLE
- 139번과 165번 세션이 EMP 테이블에 대해 TM Lock을 각각 RS(2), RX(3) 모드로 획득 → 호환 가능
- 139번 세션이 journal 테이블에 대해 TM Lock을 S(4) 모드로 획득
- 165번 세션은 EMP 테이블 100번 사원 삭제 (ID1 : 655382, ID2 : 2108)
- 139번 세션은 journal 테이블에 165번이 변경한 내용 작성(ID1 : 458755, ID2 : 2139)
SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 2 3 4 5
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
139 TX 458755 2139 6 0 9 0 7 3 2139
139 TM 75374 0 4 0 9 0 1 9838 0
139 TX 458767 2138 6 0 0 0 7 15 2138
139 TM 75338 0 2 0 9 0 1 9802 0
165 TX 655382 2108 6 0 8 0 10 22 2108
165 TM 75338 0 3 0 8 0 1 9802 0
6 rows selected.
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TM%' or event like '%TX%'; 2 3
SID SERIAL# USERNAME BLOCKING_SESSION EVENT SQL_ID PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------------------------------- ------------- -------------
127 41737 VKTM Logical Idle Wait
- 165번 세션이 EMP 테이블 100번 사원을 삭제하는 작업과 139번 세션이 journal 테이블에 165번 세션의 변경 사항 작성 작업은 아직 commit/rollback 전이므로 그대로 유지
- 139번 세션이 인덱스 생성 작업 수행하면서 새로운 TX Lock 추가됨(ID1 : 458767, ID2 : 2138)
SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 2 3 4 5
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
139 TX 655382 2108 0 4 9 0 10 22 2108
139 TM 75338 0 2 0 44 0 1 9802 0
139 TX 458755 2139 6 0 44 0 7 3 2139
139 TM 75374 0 4 0 44 0 1 9838 0
165 TX 655382 2108 6 0 43 1 10 22 2108
165 TM 75338 0 3 0 43 0 1 9802 0
6 rows selected.
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TM%' or event like '%TX%'; 2 3
SID SERIAL# USERNAME BLOCKING_SESSION EVENT SQL_ID PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------------------------------- ------------- -------------
127 41737 VKTM Logical Idle Wait
139 44156 HR 165 enq: TX - row lock contention 289bmdk5pt856 g76jvpubrj45a
SYS@ora19c> select sql_text from v$sql where sql_id = '289bmdk5pt856';
SQL_TEXT
----------------------------------------------------------------------------------------------------
alter index hr.emp_name_idx rebuild online - 100번 사원 row에 대해서 TX Lock 경합 발생
- 165번 세션이 X(6)모드로 TX Lock을 가지고 있는 중에 139번 세션이 S(4)모드로 TX Lock 요청 중
HR 2 : temporary journal 테이블 조회
HR@ora19c> select * from SYS_JOURNAL_75367;
C0 C1 O PARTNO RID
------------------------- -------------------- - ---------- ------------------
Grant Douglas D 0 D/////AAHAAACpmACk데이터 블록에 트랜잭션 slot이 부족한 경우
- 변경하고자 하는 블록의 ITL(Interested Transaction List)에 자신에 해당하는 트랜잭션 엔트리를 등록해야 함
- 이 때 등록을 못하면
enq: TX - allocate ITL entry
테이블 생성
SYS@ora19c> create table hr.itl_table(id number, l_name varchar2(1000), f_name varchar2(1000))
initrans 1 maxtrans 2 pctfree 0 tablespace users; 2
Table created.
SYS@ora19c> select ini_trans, max_trans, pct_free from dba_tables where table_name = 'ITL_TABLE';
INI_TRANS MAX_TRANS PCT_FREE
---------- ---------- ----------
1 255 0데이터 입력
HR@ora19c> insert into hr.itl_table(id, l_name, f_name)
select level, rpad('x', 1000, 'x'), rpad('z', 1000, 'z')
from dual
connect by level <= 10; 2 3 4
10 rows created.
HR@ora19c> commit;
Commit complete.테이블 정보 조회
# 통계정보 수집
SYS@ora19c> exec dbms_stats.gather_table_stats('hr', 'itl_table')
PL/SQL procedure successfully completed.
SYS@ora19c> select num_rows, blocks, avg_row_len, ini_trans, max_trans, pct_free
from dba_tables
where table_name = 'ITL_TABLE'; 2 3
NUM_ROWS BLOCKS AVG_ROW_LEN INI_TRANS MAX_TRANS PCT_FREE
---------- ---------- ----------- ---------- ---------- ----------
10 5 2005 1 255 0- 10개 row에 대해 5개 블록 사용 중
- row의 평균 길이는 2005 Byte
- 고정 트랜잭션 slot 수는 1(2)개
- 최대 트랜잭션 slot 수는 255개
- pct_free 공간은 0
SYS@ora19c> select id, rowid, dbms_rowid.rowid_block_number(rowid) from hr.itl_table order by 1;
ID ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------ ------------------------------------
1 AAASZ9AAHAAAAKzAAA 691
2 AAASZ9AAHAAAAKzAAB 691
3 AAASZ9AAHAAAAKzAAC 691
4 AAASZ9AAHAAAAKzAAD 691
5 AAASZ9AAHAAAAK3AAA 695
6 AAASZ9AAHAAAAK3AAB 695
7 AAASZ9AAHAAAAK3AAC 695
8 AAASZ9AAHAAAAK3AAD 695
9 AAASZ9AAHAAAAK0AAA 692
10 AAASZ9AAHAAAAK0AAB 692
10 rows selected.
- 1~4번 row는 691 블록
- 5~8번 row는 695 블록
- 9~10번 row는 692 블록
hr에게 세션 아이디 조회할 수 있는 뷰에 대한 권한 부여
SYS@ora19c> grant select on v_$mystat to hr;
Grant succeeded.HR1(35) : 트랜잭션 수행 가능
HR@ora19c> select max(sid) my_sid from v$mystat;
MY_SID
----------
35
HR@ora19c> update hr.itl_table
set l_name = rpad('y', 1000, 'y'), f_name = rpad('a', 1000, 'a')
where id = 1; 2 3
1 row updated.HR2(49) : 트랜잭션 수행 가능
HR@ora19c> select max(sid) my_sid from v$mystat;
MY_SID
----------
49
HR@ora19c> update hr.itl_table
set l_name = rpad('y', 1000, 'y'), f_name = rpad('a', 1000, 'a')
where id = 2; 2 3
1 row updated.HR3(159) : 대기
HR@ora19c> select max(sid) my_sid from v$mystat;
MY_SID
----------
159
HR@ora19c> update hr.itl_table
set l_name = rpad('y', 1000, 'y'), f_name = rpad('a', 1000, 'a')
where id = 3; 2 3
# 대기 중- 트랜잭션 slot의 최대 수가 2이므로 트랜잭션 slot을 할당받지 못해서 lock 경합 발생
Lock 조회
SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 2 3 4 5
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
35 TM 75389 0 3 0 205 0 1 9853 0
35 TX 196636 2598 6 0 205 0 3 28 2598
49 TX 393243 2592 6 0 201 1 6 27 2592
49 TM 75389 0 3 0 201 0 1 9853 0
159 TM 75389 0 3 0 194 0 1 9853 0
159 TX 393243 2592 0 4 34 0 6 27 2592
6 rows selected.- 159번 세션이 TX Lock을 Share(4) 모드로 요청 중
- 49번 세션이 TX Lock을 Exclusive(6) 모드로 사용 중이기 때문에 경합 발생
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where username = 'HR'; 2 3
SID SERIAL# USERNAME BLOCKING_SESSION EVENT SQL_ID PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- ---------------------------------------------------------------- ------------- -------------
35 44881 HR SQL*Net message from client aznftx5916t72
49 30841 HR SQL*Net message from client ac525z35yfsjt
159 49740 HR 49 enq: TX - allocate ITL entry c7fwp0vqz8866 2uvn3hsphnpgj
SYS@ora19c> select sql_text from v$sql where sql_id = 'c7fwp0vqz8866';
SQL_TEXT
----------------------------------------------------------------------------------------------------
update hr.itl_table set l_name = rpad('y', 1000, 'y'), f_name = rpad('a', 1000, 'a') where id = 3
SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where sid = (select blocking_session from v$session where username = 'HR' and event like '%TX%'); 2 3
SID SERIAL# USERNAME BLOCKING_SESSION EVENT SQL_ID PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- -------------------------------------------------- ------------- -------------
49 30841 HR SQL*Net message from client ac525z35yfsjt
SYS@ora19c> select sql_text from v$sql where sql_id = 'ac525z35yfsjt';
SQL_TEXT
----------------------------------------------------------------------------------------------------
update hr.itl_table set l_name = rpad('y', 1000, 'y'), f_name = rpad('a', 1000, 'a') where id = 2 HR1(35) rollback
- HR1(35) 세션의 트랜잭션을 롤백해서 트랜잭션 slot이 생겨도 HR3(159) 세션은 여전히 대기 상태
HR4(173)
- 사용 가능한 트랜잭션 slot이 생겼으므로 트랜잭션 수행 가능
HR@ora19c> select max(sid) my_sid from v$mystat;
MY_SID
----------
173
HR@ora19c> update hr.itl_table
set l_name = rpad('y', 1000, 'y'), f_name = rpad('a', 1000, 'a')
where id = 1; 2 3
1 row updated.SYS@ora19c> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where username = 'HR'; 2 3
SID SERIAL# USERNAME BLOCKING_SESSION EVENT SQL_ID PREV_SQL_ID
---------- ---------- ------------------------------ ---------------- -------------------------------------------------- ------------- -------------
35 44881 HR SQL*Net message from client aznftx5916t72
49 30841 HR SQL*Net message from client ac525z35yfsjt
159 49740 HR 49 enq: TX - allocate ITL entry c7fwp0vqz8866 2uvn3hsphnpgj
173 34820 HR SQL*Net message from client aznftx5916t72HR2(49) rollback
HR@ora19c> rollback;
Rollback complete.- 대기 중이던 159번 세션 update 수행됨
ITL 부족에 의한 경합이 많이 발생하는 세그먼트 찾는 방법(enq: TX - allocate ITL entry 대기 이벤트가 많이 발생)
select * from v$segment_statistics where owner = 'HR' and statistic_name = 'ITL waits' and value > 0;
해결 방법 : initrans 또는 pctfree 값을 늘려줌
# 해결 방법
SYS@ora19c> alter table hr.itl_table initrans 3 pctfree 10;
Table altered.
SYS@ora19c> select num_rows, blocks, avg_row_len, ini_trans, max_trans, pct_free from dba_tables where table_name = 'ITL_TABLE';
NUM_ROWS BLOCKS AVG_ROW_LEN INI_TRANS MAX_TRANS PCT_FREE
---------- ---------- ----------- ---------- ---------- ----------
10 5 2005 3 255 10
- 블록 파라미터를 변경한 후 새로운 블록부터 적용된다. 이전 블록도 함께 적용하려면 꼭 테이블을 재구성해야 한다.
- 테이블 재구성 후 관련된 인덱스도 rebuild online 하자
SYS@ora19c> select object_id, data_object_id from dba_objects where object_name = 'ITL_TABLE';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
75389 75389
SYS@ora19c> select id, rowid, dbms_rowid.rowid_block_number(rowid) from hr.itl_table order by 1;
ID ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------ ------------------------------------
1 AAASZ9AAHAAAAKzAAA 691
2 AAASZ9AAHAAAAKzAAB 691
3 AAASZ9AAHAAAAKzAAC 691
4 AAASZ9AAHAAAAKzAAD 691
5 AAASZ9AAHAAAAK3AAA 695
6 AAASZ9AAHAAAAK3AAB 695
7 AAASZ9AAHAAAAK3AAC 695
8 AAASZ9AAHAAAAK3AAD 695
9 AAASZ9AAHAAAAK0AAA 692
10 AAASZ9AAHAAAAK0AAB 692
10 rows selected.
테이블 재구성
- 테이블 move하기 전에 테이블이 속한 테이블스페이스에 free 공간이 있는지 꼭 체크하고 수행하자
SYS@ora19c> alter table hr.itl_table move;
Table altered.
SYS@ora19c> select object_id, data_object_id from dba_objects where object_name = 'ITL_TABLE';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
75389 75390
SYS@ora19c> select id, rowid, dbms_rowid.rowid_block_number(rowid) from hr.itl_table order by 1;
ID ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------ ------------------------------------
1 AAASZ+AAHAAAAK7AAA 699
2 AAASZ+AAHAAAAK7AAB 699
3 AAASZ+AAHAAAAK7AAC 699
4 AAASZ+AAHAAAAK8AAA 700
5 AAASZ+AAHAAAAK9AAA 701
6 AAASZ+AAHAAAAK9AAB 701
7 AAASZ+AAHAAAAK9AAC 701
8 AAASZ+AAHAAAAK+AAA 702
9 AAASZ+AAHAAAAK8AAB 700
10 AAASZ+AAHAAAAK8AAC 700
10 rows selected.Lock 발생 X
HR@ora19c> select max(sid) my_sid from v$mystat;
MY_SID
----------
159
HR@ora19c> update hr.itl_table
set l_name = rpad('y', 1000, 'y'), f_name = rpad('a', 1000, 'a')
where id = 1; 2 3
1 row updated.HR@ora19c> select max(sid) my_sid from v$mystat;
MY_SID
----------
49
HR@ora19c> update hr.itl_table
set l_name = rpad('y', 1000, 'y'), f_name = rpad('a', 1000, 'a')
where id = 2; 2 3
1 row updated.HR@ora19c> select max(sid) my_sid from v$mystat;
MY_SID
----------
35
HR@ora19c> update hr.itl_table
set l_name = rpad('y', 1000, 'y'), f_name = rpad('a', 1000, 'a')
where id = 3; 2 3
1 row updated.SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 2 3 4 5
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
35 TM 75389 0 3 0 64 0 1 9853 0
35 TX 458770 2148 6 0 64 0 7 18 2148
49 TX 262148 2122 6 0 64 0 4 4 2122
49 TM 75389 0 3 0 64 0 1 9853 0
159 TX 65549 2148 6 0 65 0 1 13 2148
159 TM 75389 0 3 0 65 0 1 9853 0
6 rows selected.
Deadlock
- 둘 이상의 세션이 각각 lock된 데이터를 서로 대기하고 있을 때 발생
- 각 세션이 다른 세션을 대기하고 있기 때문에 어떤 세션도 트랜잭션을 완료하여 충돌을 해결할 수 없음
- 이 때 오라클은 deadlock을 자동으로 감지하여 처음 기다리는 세션의 마지막 DML문을 종료시켜줌
HR1 : 100번 사원 수정
HR@ora19c> select max(sid) my_sid from v$mystat;
MY_SID
----------
38
HR@ora19c> update hr.emp set salary = 1000 where emp_id = 100;
1 row updated.HR2 : 200번 사원 수정
HR@ora19c> select max(sid) my_sid from v$mystat;
MY_SID
----------
138
HR@ora19c> update hr.emp set salary = 2000 where emp_id = 200;
1 row updated.SYS@ora19c> select sid, type, id1, id2, lmode, request, ctime, block, to_char(trunc(id1/power(2, 16))) usn,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot, id2 sqn
from v$lock
where type in ('TX', 'TM') and sid in (select sid from v$session where username = 'HR')
order by 1; 2 3 4 5
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
38 TX 458759 2139 6 0 38 0 7 7 2139
38 TM 75338 0 3 0 38 0 1 9802 0
138 TX 196629 2596 6 0 37 0 3 21 2596
138 TM 75338 0 3 0 37 0 1 9802 0HR1 : 200번 사원 수정
HR@ora19c> update hr.emp set salary = 3000 where emp_id = 200;
# 대기 중SYS@ora19c> /
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
38 TX 458759 2139 6 0 61 0 7 7 2139
38 TM 75338 0 3 0 61 0 1 9802 0
38 TX 196629 2596 0 6 1 0 3 21 2596
138 TX 196629 2596 6 0 60 1 3 21 2596
138 TM 75338 0 3 0 60 0 1 9802 0
- 200번 사원 row에 대해 TX Lock 발생
HR2 : 100번 사원 수정 (Deadlock 발생)
HR@ora19c> update hr.emp set salary = 4000 where emp_id = 100;
# 대기 중- 3초 후 HR1 세션에서 수행한 DML이 Deadlock에 의해 종료됨
update hr.emp set salary = 3000 where emp_id = 200
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource- alert log에서 deadlock 확인 가능
2026-01-29T16:28:04.855324+09:00
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_3550.trc.SYS@ora19c> /
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
38 TX 458759 2139 6 0 84 1 7 7 2139
38 TM 75338 0 3 0 84 0 1 9802 0
138 TX 196629 2596 6 0 83 0 3 21 2596
138 TX 458759 2139 0 6 3 0 7 7 2139
138 TM 75338 0 3 0 83 0 1 9802 0- 100번 사원 row에 대해 TX Lock 경합 발생
HR1 rollback
- 처음으로 waiting 단계로 빠진 세션의 마지막 DML 작업을 취소
- 다른 세션에서는 계속 기다리고 있음
# HR1
HR@ora19c> rollback;
Rollback complete.- HR1 세션에서 롤백을 수행하면 HR2 update 작업이 수행됨
# HR 2
1 row updated.SYS@ora19c> /
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK USN SLOT SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
138 TX 196629 2596 6 0 181 0 3 21 2596
138 TM 75338 0 3 0 181 0 1 9802 0Row Migration
- 블록 안에 있는 행이 update 에 의해 데이터가 커지는 경우 블록 안에 프리 공간이 있어야 함
- 만약에 프리 공간이 없으면 행(row)가 새로운 블록으로 옮겨 간 후 수정
테이블 생성 및 데이터 입력
SYS@ora19c> create table hr.mig_table(id number, l_name varchar2(2000), f_name varchar2(2000)) tablespace users;
Table created.
SYS@ora19c> insert into hr.mig_table(id, l_name, f_name)
select
level,
decode(mod(level, 3), 1, null, rpad('x', 2000, 'x')),
decode(mod(level, 3), 1, null, rpad('x', 1000, 'x'))
from dual
connect by level <= 1000;
1000 rows created.
SYS@ora19c> commit;
Commit complete.통계 정보 수집 및 테이블 정보 조회
SYS@ora19c> exec dbms_stats.gather_table_stats('hr','mig_table');
PL/SQL procedure successfully completed.
SYS@ora19c> select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';
NUM_ROWS BLOCKS CHAIN_CNT
---------- ---------- ----------
1000 370 0인덱스 생성
SYS@ora19c> create index mig_table_idx on hr.mig_table(id) tablespace users;
Index created.row migration 유도
SYS@ora19c> update hr.mig_table
set l_name = rpad('x', 2000, 'x'), f_name = rpad('x', 2000, 'x')
where mod(id, 3) = 1; 2 3
334 rows updated.
SYS@ora19c> commit;
Commit complete.통계 정보 수집 및 테이블 정보 조회(블록 수가 370 → 748)
SYS@ora19c> exec dbms_stats.gather_table_stats('hr','mig_table');
PL/SQL procedure successfully completed.
SYS@ora19c> select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';
NUM_ROWS BLOCKS CHAIN_CNT
---------- ---------- ----------
1000 748 0시스템 전체에 대해 row migration으로 한 row를 여러 블록에서 읽은 횟수 조회
SYS@ora19c> col value format 999999
SYS@ora19c> select * from v$sysstat where name = 'table fetch continued row';
STATISTIC# NAME CLASS VALUE STAT_ID CON_ID
---------- -------------------------------------------------- ---------- ------- ---------- ----------
1017 table fetch continued row 64 38388 1413702393 0HR에 대해 row migration으로 한 row를 여러 블록에서 읽은 횟수 조회
SYS@ora19c> select
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') day,
a.sid, vss.username,
a.name,
a.value
from (select vst.sid,vst.value,vsn.name,vsn.statistic#
from v$statname vsn, v$sesstat vst
where vsn.statistic# = vst.statistic#
order by vst.value desc) a, v$session vss
where a.sid = vss.sid
and vss.username = 'HR'
and a.name = 'table fetch continued row'; 2 3 4 5 6 7 8 9 10 11 12
DAY SID USERNAME NAME VALUE
------------------- ---------- ------------------------------ -------------------------------------------------- -------
2026-01-29 17:19:49 38 HR table fetch continued row 0HR : 인덱스 스캔
HR@ora19c> select /*+ index(t mig_table_idx) */ count(l_name) from hr.mig_table t where id > 0;
COUNT(L_NAME)
-------------
1000row의 수(334)만큼 row migration 발생
SYS@ora19c> select
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') day,
a.sid, vss.username,
a.name,
a.value
from (select vst.sid,vst.value,vsn.name,vsn.statistic#
from v$statname vsn, v$sesstat vst
where vsn.statistic# = vst.statistic#
order by vst.value desc) a, v$session vss
where a.sid = vss.sid
and vss.username = 'HR'
and a.name = 'table fetch continued row'; 2 3 4 5 6 7 8 9 10 11 12
DAY SID USERNAME NAME VALUE
------------------- ---------- ------------------------------ -------------------------------------------------- -------
2026-01-29 17:20:17 38 HR table fetch continued row 334문제가 발생한 쿼리 조회
SYS@ora19c> select s.prev_sql_id, s.prev_child_number, v.sql_text
from v$session s, v$sql v
where s.username='HR'
and s.prev_sql_id = v.sql_id
and s.prev_child_number = v.child_number; 2 3 4 5
PREV_SQL_ID PREV_CHILD_NUMBER SQL_TEXT
------------- ----------------- ----------------------------------------------------------------------------------------------------
4y2m5q8hhv0r4 0 select /*+ index(t mig_table_idx) */ count(l_name) from hr.mig_table t where id > 0실행 계획 확인
SYS@ora19c> select * from table(dbms_xplan.display_cursor('4y2m5q8hhv0r4'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4y2m5q8hhv0r4, child number 0
-------------------------------------
select /*+ index(t mig_table_idx) */ count(l_name) from hr.mig_table t
where id > 0
Plan hash value: 3550918461
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 337 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2005 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MIG_TABLE | 1000 | 1958K| 337 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | MIG_TABLE_IDX | 1000 | | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">0)
21 rows selected.row migration 통계 수집
SYS@ora19c> select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';
NUM_ROWS BLOCKS CHAIN_CNT
---------- ---------- ----------
1000 748 0 <<- row migration 통계 정보
# row migration 통계 수집
SYS@ora19c> analyze table hr.mig_table compute statistics;
Table analyzed.
SYS@ora19c> select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';
NUM_ROWS BLOCKS CHAIN_CNT
---------- ---------- ----------
1000 748 334row migration 해결 방법은 테이블 재구성
SYS@ora19c> alter table hr.mig_table move;
Table altered.- 인덱스가 unusable 상태로 바뀜
SYS@ora19c> col index_name format a30
SYS@ora19c> select index_name, status from dba_indexes where table_name = 'MIG_TABLE';
INDEX_NAME STATUS
------------------------------ --------
MIG_TABLE_IDX UNUSABLE인덱스 rebuild online
SYS@ora19c> alter index mig_table_idx rebuild online;
Index altered.
SYS@ora19c> select index_name, status from dba_indexes where table_name = 'MIG_TABLE';
INDEX_NAME STATUS
------------------------------ --------
MIG_TABLE_IDX VALID통계 정보 수집
SYS@ora19c> analyze table hr.mig_table compute statistics;
Table analyzed.
SYS@ora19c> select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';
NUM_ROWS BLOCKS CHAIN_CNT
---------- ---------- ----------
1000 684 0인덱스 스캔 유도
HR@ora19c> select /*+ index(t mig_table_idx) */ count(l_name) from hr.mig_table t where id > 0;
COUNT(L_NAME)
-------------
1000row migration 발생 X
SYS@ora19c> select
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') day,
a.sid, vss.username,
a.name,
a.value
from (select vst.sid,vst.value,vsn.name,vsn.statistic#
from v$statname vsn, v$sesstat vst
where vsn.statistic# = vst.statistic#
order by vst.value desc) a, v$session vss
where a.sid = vss.sid
and vss.username = 'HR'
and a.name = 'table fetch continued row'; 2 3 4 5 6 7 8 9 10 11 12
DAY SID USERNAME NAME VALUE
------------------- ---------- ------------------------------ -------------------------------------------------- -------
2026-01-29 17:33:48 38 HR table fetch continued row 334- 세션이 열려있는 동안 value가 누적되므로 이전 값과의 차이를 계산해야 함
- 이전 조회 시 334번 발생했었는데 그 이후로 값이 증가하지 않음 ⇒ row migration 발생 안함
- row migration 발생 시 대기 이벤트 체크해보기
'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글
| [아이티윌 오라클 DBA 과정 91기] 260202 TIL (0) | 2026.02.02 |
|---|---|
| [아이티윌 오라클 DBA 과정 91기] 260130 TIL (0) | 2026.01.30 |
| [아이티윌 오라클 DBA 과정 91기] 260128 TIL (0) | 2026.01.29 |
| [아이티윌 오라클 DBA 과정 91기] 260127 TIL (0) | 2026.01.29 |
| [아이티윌 오라클 DBA 과정 91기] 260126 TIL (0) | 2026.01.29 |