enq: TX - row lock contention
enq: TX - row lock contention 이벤트는 로우 레벨 락(row level lock)에 의한 경합이 발생했다는 것을 의미합니다. 다음과 같은 세 가지 경우에 로우 레벨 락 경합이 발생합니다.
- 여러 세션이 동일 로우를 변경하는 경우
- 여러 세션이 동일 Unique Key 충돌을 일으키는 경우
- 여러 세션이 비트맵 인덱스 충돌을 일으키는 경우
* 여러 세션이 동일 로우를 변경하는 경우(mode=6)
동일 로우 변경은 TX 락에 의한 경합이 발생하는 가장 보편적인 경우입니다. 특정 프로세스가 특정 로우를 변경하기 위해 해당 로우를 방문했을 때, 현재 로우가 변경된 상태라면 ITL로부터 해당 로우를 변경한 트랜잭션을 확인하고, 자기 자신을 TX Enqueue 목록에 추가하여 enq: TX - row lock contention 이벤트를 대기합니다. 이 대기는 해당 로우에 대해 TX 락을 보유한 프로세스가 락을 해제할 때까지 계속 됩니다.
세션A: (SID = 150)
SQL> update test set id = 1 where rownum = 1;
세션B: (SID = 148)
SQL> update test set id = 1 where rownum = 1;
... Wait ...
세션B는 세션A가 이미 변경한 로우를 업데이트하기를 원하므로 대기상태에 빠지게 됩니다. 이 상태에서 TX 락의 대기현황을 V$LOCK 뷰를 통해 관찰하면 다음과 같습니다.
세션C:
SQL> exec print_table('select * from v$lock where type = TX');
ADDR : C0000000ED2DC938
KADDR : C0000000ED2DCAC0
SID : 150
TYPE : TX
ID1 : 1507368
ID2 : 7763
LMODE : 6 ? TX 락을 Exclusive 모드로 획득중
REQUEST : 0
CTIME : 235
BLOCK : 1
----------------------------------------------------------------
ADDR : C0000000EE0A78D8
KADDR : C0000000EE0A78F8
SID : 148
TYPE : TX
ID1 : 1507368
ID2 : 7763
LMODE : 0
REQUEST : 6 ? TX 락을 Exclusive 모드로 대기중
테스트) 동일 로우 업데이트에 의한 TX 락 경합
위의 결과는 다음과 같이 해석할 수 있습니다. "세션A(SID=150)가 TX 락(ID1=1507368, ID2=7763)을 Exclusive 모드(LMODE=6)로 획득한 상태이며, 세션B(SID=148)가 동일한 ID1, ID2에 대해 TX 락을 Exclusive 모드(REQUEST=6) 획득하기 위해 대기중이다." TX 락이 보호하는 자원은 "트랜잭션"으로, Part1의 "트랜잭션과 OWI"에서 설명한 바와 같이 트랜잭션은 USN+SLOT+SQN 정보로 표현됩니다. TX 락의 ID1 값은 USN + SLOT이며, ID2 값은 SQN에 해당됩니다. 따라서 V$LOCK 뷰를 통해 어떤 트랜잭션에서의 경합 현상인지 파악할 수 있습니다. enq: TX - row lock contention 이벤트의 P2, P3 값은 TX 락의 ID1, ID2 값과 매칭됩니다. V$SESSION_WAIT 뷰에서 세션B(SID=148)의 대기현상을 조회하면 다음과 같은 결과를 얻을 수 있습니다.
SQL> exec print_table('select * from v$session_wait where sid=148');
SID : 148
SEQ# : 341
EVENT : enq: TX - row lock contention
P1TEXT : name|mode
P1 : 1415053318
P1RAW : 0000000054580006
P2TEXT : usn<
* 여러 세션이 Unique Key 충돌을 일으키는 경우(mode=4)
Unique Key 또는 Primary Key 충돌이 발생할 때도 TX 락 경합이 발생하게 됩니다. 프로세스 A가 Insert를 수행한 후, 프로세스 B가 Unique Key 충돌이 발생하게끔 Insert를 수행하면, 프로세스 B는 TX 락을 Shared 모드로 획득하기 위해 대기합니다. 이때의 대기현상은 enq: TX - row lock contention 이벤트로 나타납니다. 프로세스 B는 프로세스 A가 커밋하거나 롤백할 때까지 대기하게 됩니다. 프로세스 A에서 커밋이 이루어지면, 프로세스 B는 ORA-0001 에러 상황이 되며 롤백이 이루어지게 됩니다.
세션A: (SID=144) Unique Key를 생성하고 값을 insert 합니다.
SQL> create unique index test_idx on test(id);
SQL> insert into test values(1);
세션B: (SID=148) 동일한 값을 insert 합니다.
SQL> insert int test values(1);
... Wait ...
세션C:
SQL> exec print_table('select * from v$lock where sid in (148,144) and type = TX');
ADDR : C0000000ED2EE058
KADDR : C0000000ED2EE1E0
SID : 144
TYPE : TX
ID1 : 917514
ID2 : 9024
LMODE : 6 ? TX 락을 Exclusive 모드로 획득중
REQUEST : 0
CTIME : 114
BLOCK : 1
----------------------------------------------------------------
ADDR : C0000000ED2B6738
KADDR : C0000000ED2B68C0
SID : 148
TYPE : TX
ID1 : 3407878
ID2 : 1985
LMODE : 6 ? TX 락을 Exclusive 모드로 획득중
REQUEST : 0
CTIME : 102
BLOCK : 0
----------------------------------------------------------------
ADDR : C0000000EE0A78D8
KADDR : C0000000EE0A78F8
SID : 148
TYPE : TX
ID1 : 917514
ID2 : 9024
LMODE : 0
REQUEST : 4 ? Shared 모드로 TX 락을 획득하기 위해 대기
CTIME : 102
BLOCK : 0
테스트) Unique Key 충돌에 의한 TX 락 경합
V$LOCK 뷰의 결과를 유심히 볼 필요가 있습니다. 먼저 Insert를 수행한 144번 세션은 하나의 TX 락(ID1=917514, ID2=9024)을 Exclusive하게 획득하고 있습니다. 반면 나중에 Insert를 수행한 148번 세션은 이미 하나의 TX 락(ID1=3407878, ID2=1985)을 Exclusive하게 획득한 상태에서 144번 세션에 의해 획득된 TX 락을 Shared 모드로 획득하기 위해 대기하고 있습니다. 이 결과 오라클인 Unique Key를 보호하기 위해서 어떤 방법을 사용하는지 간접적으로 추론할 수 있습니다. 오라클은 테이블에 로우를 추가한 후 인덱스도 함께 추가하게 되는데 인덱스를 추가하는 과정에서 Unique 속성을 위반하는지 확인합니다. 만일 동일 키값이 존재하고 선행 트랜잭션이 이미 종료된 상태라면 ORA-0001 에러가 나고, 아직 선행 트랜잭션이 종료되지 않은 상태라면 선행 트랜잭션이 획득한 TX 락을 Shared 모드로 획득하기 위해 대기하게 됩니다.
* Wait Time
Enqueue 대기 이벤트와 동일하게 최대 3초까지 기다립니다. 만일 TX 락을 획득하지 못하면 획득할 때까지 대기합니다.
* Parameter
P1(Enqueue 정보), P2(usn<), P3(시퀀스)
* Common Causes and Actions
- 원인: 동일한 Row에 대해 동시에 DML 수행
- 진단 방법: 동일 로우 변경에 따른 TX 락 경합은 철저하게 애플리케이션 이슈입니다. 장시간 수행되는 Update나 Delete 명령은 트랜잭션이 적은 시간대에 수행하는 것이 좋습니다. 또는 Update나 Delete 명령 자체의 성능을 개선하는 것 또한 방법이 됩니다. 특히 대량의 데이터를 Update하는 것은 굳이 TX 락 경합 문제 뿐만 아니라 수많은 성능 문제를 야기할 수 있습니다.
- 개선 방법
1) 기존 테이블인 old_table을 복사한 테이블인 new_table에 생성하되, 변경 내용을 저장합니다. 즉, "Create table new_table as selec id, name, register_date, decode(class, 1, 'A', 2, 'B') ... from old_table"과 같은 명령을 이용합니다.
2) 새로 생성한 테이블인 new_table old_table과 동일하게 인덱스 등을 생성합니다.
3) 기존 테이블인 old_table을 Drop하고 새로운 테이블인 new_table을 old_table로 rename합니다.
위의 작업을 수행할 때 nologging과 parallel 옵션을 같이 사용하면 원하는 작업을 더욱 빨리, 그리고 리소스를 적게 사용하면서 실행할 수 있습니다.
- 원인: Unique Key 관리
- 진단 방법: Insert 시 TX-row lock contention을 대기 한다면 PK 키 충돌로 인한 Lock입니다.
- 개선 방법: Unique Key 충돌에 의한 TX 락 경합은 철저하게 애플리케이션 이슈입니다. Unique Key를 생성하기 위해 별도의 계산법을 쓴다거나 기존 테이블에서 최대값(MAX)을 추출하는 방법 등을 사용할 경우 Unique Key 충돌에 의한 TX 락 경합은 언제든지 발생할 수 있습니다. 최선의 해결책은 시퀀스(Sequence)를 사용해서 Unique Key를 생성하는 것입니다.
- 원인: Bitmap Index의 비효율적인 사용
- 진단 방법: Row lock Contention이 Bitmap 인덱스가 원인인지 확인
- 개선 방법: 비트맵 인덱스는 읽기작업은 빈번하고 쓰기 작업은 드문 테이블에 대해 DSS성 Select 문의 성능을 극대화하기 위해 고안된 것입니다. DML이 빈번한 테이블에 대해 비트맵 인덱스를 함부로 사용하는 것은 대단히 위험합니다. 로우가 변경될 때마다 비트맵 값을 계산해야 하기 때문에 DML자체의 성능이 저하되며, 동시에 여러 세션이 DML을 수행하는 경우에는 과도한 TX 락 경합이 발생합니다.
만일 DML이 빈번한 테이블에 대해 DSS성 SQL 문의 성능을 보장하고 싶다면 비트맵 인덱스보다는 Materialized View와 같은 기능을 사용하는 것이 바람직 합니다.
Delayed block cleanout 개념
Delayed block cleanout의 개념을 이해하려면 우선 오라클에서 사용하는 몇 가지 용어에 대해 알아야 합니다. Cleanout 또는 block cleanout이란 블록에 설정된 로우 레벨 락을 해제(Cleanout)한다는 의미입니다. Cleanout이 발생하면 로우 레벨 락이 해제되고 ITL 정보(SCN, Flag, Lock Byte 등)가 갱신됩니다.
Fast Commit이란 커밋 시점에 모든 블록에 대해 cleanout을 수행하지 않는다는 것을 의미합니다.오라클은 성능의 문제로 변경된 데이터 블록들 중 버퍼캐시에 올라와 있는 일부 블록들에 대해서만 cleanout을 수행합니다. Fast Commit이 발생하는 경우, 변경되는 정보의 양을 최소화하기 위해 ITL 정보에서 Flag와 SCN 정보만 변경되고 lock byte 정보는 변경되지 않습니다. Lock byte 정보는 트랜잭션에 의해 변경된 모든 로우에 저장되므로 변경해야 할 데이터의 양이 많기 때문입니다. Fast Commit 기법은 변경된 일부 데이터 블록들의 헤더에 대해서만 변경 작업을 수행하기 때문에 리두 데이터가 생성되지 않고, 커밋 마크(Commit Mark) 만이 리두에 저장됩니다. 수백만 건의 데이터를 변경한 후에 커밋을 수행하는 경우에도 매우 빠른 속도로 커밋 처리가 되는 것은 이러한 기법 덕분입니다.
Delayed block cleanout이란 변경된 데이터 블록들 중 Fast Commit에 의해 cleanout 되지 않은 블록들을 나중에(Delayed) cleanout 처리한다는 의미입니다. Delayed block cleanout은 다음 번에 해당 블록을 스캔하는 프로세스에 의해 수행됩니다. Delayed block cleanout이 발생하는 경우, 순수한 Select 작업인 경우에도 cleanout 작업 자체에 대한 리두 데이터가 생성될 수 있습니다.
커밋 수행(Fast Commit)에 의해 cleanout이 수행되면 cleanout이 발생한 블록 수 만큼 commit cleanouts 통계 값이 증가합니다. Cleanout을 수행하는 도중 에러가 발생하면 에러의 발생 원인에 따라 commit cleanout failures: xxxx 류의 통계 값이 증가합니다. Cleanout 수행에 성공한 경우에는 commit cleanouts successfully completed 통계 값이 증가합니다. 각 값들은 다음과 같은 관계를 따릅니다.
commit cleanouts = commit cleanouts successfully completed + Sum of(commit cleanout failures: xxxx)
user commits 통계값이 사용자가 커밋을 수행한 횟수를 나타내는 반면 commit cleanouts는 커밋에 의한 cleanout이 발생한 블록 수를 나타낸다는 사실에 유의해야 합니다.
Fast Commit이 발생하면 ITL의 flag 값이 "U"(Upper Bound Commit)로 수정되고 SCN이 할당됩니다. 하지만 블록 내의 개별 로우의 lock byte 값은 변경되지 않습니다. 나중에 해당 블록을 다른 프로세스가 변경하는 경우에 flag 값이 "C"(Commit)로 변경되고 블록 내의 개별 로우의 lock byte 값도 정리(0의 값)이 됩니다.
enq: TX - index contention
enq: TX - index contention 대기는 보통의 상황에서는 잘 발생하지 않으며 동시에 려러 세션이 인덱스가 생성되어 있는 테이블에 대해서 많은 양의 DML을 수행하는 경우에 주로 발생합니다. 이 대기현상은 자주 발생하지 않지만, 생성된 인덱스의 수가 많고 인덱스를 이루는 컬럼들의 값이 커서 리프 노드 블록이 빈번하게 분할되는 경우에는 상당한 성능 저하의 원인이 됩니다. 특히 시퀀스 등을 사용해서 값을 생성하는 칼럼에 대해 인덱스가 생성된 경우, 항상 제일 마지막 리프 노드에만 값이 추가되는 현상이 생겨 인덱스 분할이 자주 발생할 수 있습니다. 이것은 정렬된 형태로 리프 노드를 유지하는 비트리 인덱스의 속성에 의해 발생하는 것으로 여러 세션에 의해서 많은 양의 데이터를 Insert하는 경우 buffer busy waits 대기와 함께, enq: TX-index contention 대기가 같이 발생하게 됩니다.
* Wait Time
Enqueue 대기 이벤트와 동일하게 최대 3초까지 가디립니다. 만일 TX 락을 획득하지 못하면 획득할 때까지 대기합니다.
* Parameter
P1(Enqueue 정보), P2(usn<), P3(시퀀스)
* Common Causes and Actions
- 원인: 동시에 여러 세션이 인덱스가 생성되어 있는 테이블에 대해서 많은 양의 DML 수행
- 진단 방법
: V$SESSION_WAIT 뷰를 통해 확인
: 해당 테이블에 생성되어 있는 인덱스의 수가 많고 인덱스를 이루는 컬럼의 값이 큰지 확인하고, 시퀀스를 사용하여 값을 생성하는 컬럼에 인덱스가 생성되어 있는지 확인.
- 개선 방법
: REVERSE 인덱스의 사용
: 인덱스 키 칼럼의 변경
: 인덱스 블록 크기의 변경
Index Split
오라클의 B-Tree 인덱스의 블록이 꽉 찰(Full) 경우, Split을 통해 새로운 공간을 확보합니다. Index Split의 종류는 다음과 같이 나뉩니다.
1) Branch Node Split: Branch Node나 Root Node가 꽉 찰 경우 발생하는 Split이다. 항상 50:50으로 Split이 이루어진다.
2) Leaf Node Split: Leaf Node가 꽉 찰 경우 발생하는 Split이다. Split이 발생하는 위치에 따라 50:50 Split과 90:10 Split으로 분류된다.
Index Split은 인덱스의 크기가 커가는 과정에서 필연적으로 발생하는 현상으로 이를 피할 방법은 없습니다. 하지만 동시에 많은 세션인 DML을 수행하는 과정에서 Index Split이 발생하면 경합이 발생하고 전반적인 성능 저하가 발생할 수 있습니다.
50:50 Split vs 90:10 Split
일반적인 Leaf Node Split은 50:50의 비율로 이루어집니다. 즉, Leaf Block이 꽉 차면 새로운 블록을 할당받은 다음, 기존 블록과 새 블록에 50:50으로 키 값을 재분배합니다. 하지만 가장 최우측 Leaf Node에서 최대 키 값이 삽입됨으로써 Split이 발생하는 경우에는 새로 할당된 블록에는 새로 추가된 키 값만이 추가됩니다. 즉, 기존의 꽉 찬 블록의 키값을 재분배하지 않습니다. 이런 이유 때문에 90:10 Split을 99:1 또는 100:0 Split으로 부르기도 합니다. 90:10 Split은 인덱스 키값이 단방향으로 증가하면서 키 값이 삽입되는 경우에 발생합니다.
Index Split과 통계값
Index Split과 관련된 오라클 통계값은 아래와 같습니다.
SQL> SELECT * FROM V$STATNAME WHERE NAME LIKE '%split%' AND NAME LIKE '%node%';
STATISTIC# NAME CLASS STAT_ID
262 leaf node splits 128 1417124053
263 leaf node 90-10 splits 128 1285138251
264 branch node splits 128 399027615
enq: TX - allocate ITL entry
ITL(interested transaction list)이란 특정 블록을 변경하고자 하는 트랜잭션의 목록을 의미하며, 블록의 헤더에서 그 정보를 관리합니다. 블록을 변경하고자 하는 모든 트랜잭션은 블록 헤더의 ITL의 엔트리 중 하나로 자신을 등록해야 합니다. 만일 ITL이 약속된 최대치, 즉 MAXTRANS에 의해 지정된 값을 초과하거나 블록 내의 여유공간이 부족해서 엔트리를 등록하는 것이 불가능한 경우, 프로세스는 이미 ITL에 엔트리를 등록한 프로세스가 Exclusive하게 획득한 TX 락을 Shared 모드로 획득하기 위해 대기하게 됩니다. 이때 대기 현상은 enq: TX - allocate ITL entry 이벤트로 관찰됩니다.
Row Chaning이나 Row Migration이 발생한 로우의 경우 하나의 로우를 업데이트할 때 여러 개의 블록에 대해 각각 ITL 엔트리를 할당해야 하므로 이로 인해 ITL 엔트리 부족에 의한 TX 락 경합이 발생할 확률이 높아집니다.
* Wait Time
최대 3초까지 기다립니다. 만일 TX 락을 획득하지 못하면 획득할 때까지 대기합니다.
* Parameter
P1(Enqueue 정보), P2(usn<), P3(sequence)
* Common Causes and Actions
- 원인: ITL 엔트리 부족에 의한 TX 락 경합
- 진단 방법: V$SEGMENT_STATISTICS 뷰를 통해 어떤 세그먼트에서 ITL 부족에 의한 경합이 많이 발생하는지 확인(STATISTIC_NAME='ITL waits')
- 개선 방법
: 높은 INITRANS 속성 부여
: 잘못된 애플리케이션 설계 수정
ITL에 영향을 주는 세그먼트 속성
- INITRANS: 블록 헤더마다 몇 개의 ITL 엔트리를 확보할지 결정한다.
- MAXTRANS: 최대 몇개의 ITL 엔트리를 허용할지 결정한다. MAXTRANS의 기본값은 255이며, 오라클 10g부터는 MAXTRANS는 255로 고정된다. 즉, 값을 지정해도 오라클은 이 값을 무시하며 MAXTRANS는 항상 255의 값을 사용한다.
- PCTFREE: 블록이 최초 생성될 때 INITRANS에 지정된 값만큼 ITL 엔트리가 확보되었다가 동시 트랜잭션이 증가하면 PCTFREE로 확보된 영역 내에서 MAXTRANS 값에 도달할 때까지 추가로 확장된다.
만일 동시 트랜잭션이 왕성할 것으로 예상되는 테이블이라면 INITRANS를 충분히 주는 것이 좋습니다. INITRNAS를 충분히 주면 동적으로 공간을 확보하는 오버헤드가 줄어들며, ITL 엔트리 부족에 따른 TX 락 경합이 발생할 확률도 줄어듭니다. 오라클 10g부터는 MAXTRANS의 값이 255로 고정되므로 잘못된 MAXTRANS 값 지정으로 인해 성능 문제가 생길 소지가 사라졌습니다.
PCTFREE 값을 비정상적으로 작게 설정하는 경우 ITL을 동적으로 확장할 여유공간이 부족해서 문제가 될 수 있습니다.
enq: TM - contention
DML이 수행되는 동안, DML과 관련된 객체에 대한 변경을 방지하기 위해 DML을 수행하는 프로세스는 반드시 해당 테이블에 대해 TM 락을 획득해야 합니다. TM 락을 획득하는 과정에서 경합이 발생하면 enq: TM - contention 이벤트를 대기하게 됩니다.
* Wait Time
최대 3초까지 기다린다. 만일 TM 락을 획득하지 못하면 획득할 때까지 대기한다.
* Parameter
P1(Enqueue 정보), P2(Object#), P3(Table/Partition 정보)
* Common Causes and Actions
- 원인: (오라클 9i 이전 버전)인덱스가 없는 Foreign Key
- 진단 방법: DBA_DML_LOCKS 뷰를 통해 확인(V$LOCK 뷰에서 락 유형이 TM인 것만 추출)
- 개선 방법: Foreign Key 칼럼에 인덱스를 생성
- 원인: 부적절한 DDL로 인한 TM 락 경합
- 진단 방법: DBA_DML_LOCKS 뷰를 통해 확인(V$LOCK 뷰에서 락 유형이 TM인 것만 추출)
- 개선 방법
: 일반적인 DDL 수행시 가능하면 온라인(Online) 옵션을 사용
: Parallel DDL 사용(Nologging 옵션 사용)
Online DDL
Update가 이루어지고, 아직 커밋이 되지 않은 테이블에 대해서는 DDL을 수행하는 것이 불가능합니다. 반면, DDL이 수행중인 테이블에 대해서 DML을 수행하는 경우에는 TM 락 경합이 발생할 수 있습니다. 특정 프로세스가 테이블에 대해서 인덱스를 생성하는 경우, 인덱스가 생성되는 동안에는 테이블에 대해서 TM 락을 Shared 모드로 획득하게 됩니다.
즉, create index 문은 테이블에 대해서 Shared 모드로 TM 락을 획득하게 됩니다. 문제는, 이 테이블에 대해 DML을 수행하는 세션은 Sub-Exclusive 모드로 TM 락을 획득해야 한다는 것입니다. Shared 모드와 Sub-Exclusive 모드는 호환성이 없기 때문에 DML을 수행하는 세션은 DDL 수행이 완료될 때까지 대기하게 되고, 이때 enq: TM-contention 대기 이벤트가 발생하게 됩니다. DDL로 인한 TM 락 경합을 줄일 수 있는 방법으로는 다음과 같은 것들이 있습니다.
- 데이터 량이 많은 테이블에 대한 부적절한 DDL을 수행하게 되면, 해당 테이블을 접근하는 모든 DML 세션이 대기상태에 빠지게 되고, 장애상황으로까지 발전할 수 있습니다. 적절한 관리를 통해 원천적으로 방지하는 것이 좋습니다.
- DDL 수행시 가능하면 온라인(Online) 옵션을 사용합니다. 오라클이 버전업될수록 온라인으로 수행가능한 DDL은 점점 증가하는 추세입니다. 대부분의 일반적인 DDL에서 온라인 옵션을 사용하는 것이 가능합니다. 가령, CREATE INDEX 명령을 온라인 옵션으로 수행할 경우 테이블에 대해 Shared 모드가 아닌 Sub-Shared(SS) 모드로 TM 락을 획득합니다. Sub-Shared 모드는 Sub-Exclusive 모드와 호환성이 있기 때문에 인덱스를 생성하는 도중에 DML을 수행하는 것이 가능합니다. 즉 enq: TM - contention 대기가 발생하지 않습니다.
enq: US - contention
언두 세그먼트를 온라인 또는 오프라인시키는 과정을 동기화하기 위해 해당 작업을 수행하는 서버 프로세스나 백그라운드 프로세스는 US 락을 획득해야 합니다. US 락은 언두 세그먼트마다 하나씩 할당되며, ID1=Undo Segment#입니다. US 락을 획득하는 과정에서 경합이 발생하면 enq: US - contention 이벤트를 대기하게 됩니다.
서버 프로세스는 트랜잭션을 시작하는 시점에 언두 세그먼트를 할당받아야 하는데, 사용 가능한 언두 세그먼트가 존재하지 않을 경우 언두 세그먼트를 신규로 생성하거나 오프라인 상태의 언두 세그먼트를 온라인시켜야 합니다. 이 작업이 이루어지는 동안 서버 프로세스는 US 락을 획득하기 위해 대기함으로써 언두 세그먼트가 확보되기를 기다리게 됩니다.
트랜잭션의 변동이 심한 경우에 언두 세그먼트를 온라인시키는 작업으로 인해 US 락 경합이 광범위하게 발생할 수 있습니다. 오라클이 최초에 구동된 후 온라인 상태의 언두 세그먼트의 개수가 적은 상태에서 트랜잭션이 갑자기 증가하는 경우나, 일정 시간 트랜잭션이 발생하지 않아 기존의 언두 세그먼트가 오프라인된 상태에서 갑자기 트랜잭션이 증가하는 경우 US 락 경합에 의한 대기현상이 발생하게 됩니다.
* Wait Time
최대 3초까지 기다립니다. 만일 US 락을 획득하지 못하면 획득할 때까지 대기합니다.
* Parameter
P1(Enqueue), P2(Undo Segment 정보), P3(0)
* Common Causes and Actions
- 원인: 온라인 Undo Segment의 개수가 적은 상태에서 다량의 트랜잭션 발생
- 진단 방법: V$LOCK을 통한 US락 발생 모니터링
- 개선 방법: 10511 이벤트를 수행하여 SMON이 Undo Segment를 오프라인 상태로 변경하지 못하도록 설정
10511 EVENT의 적용
보통의 시스템에서는 갑자기 많은 수의 언두 세그먼트를 온라인시키는 경우가 많지 않으므로 US 락 경합은 일반적으로 자주 발생하지는 않습니다. 하지만 시스템의 속성상 트랜잭션의 변동량이 크고, 이로 인해 US 락 경합이 계속해서 발생한다면 다음과 같이 10511 이벤트를 수행하도록 지정합니다. 10511 이벤트는 SMON이 언두세그먼트를 오프라인시키는 기능을 수행하지 않도록 하는 역할을 합니다.
event="10511 trace name context forever, level 2"
오라클을 재기동하면, 한번 온라인된 언두 세그먼트는 다시는 오프라인 상태로 변경되지 않습니다. 따라서 언두 세그먼트를 온라인하는데서 오는 US 락 경합현상도 발생하지 않게 됩니다.
오라클을 재기동하지 않고 위의 옵션을 온라인으로 적용시키려면 oradebug 툴을 이용해야 합니다. oradebug 툴에서 다음과 같은 명령을 수행합니다.
oradebug setospid
oradebug event 10511 trace name context forever, level 1
oradebug setmypid
그런데 위와 같은 방법의 문제점은 오라클을 재기동할 경우에는 1개의 인스턴스에 10개를 제외한 모든 언두 세그먼트들은 다시 오프라인 된다는 것입니다. 아래와 같은 방법을 이용하여 오라클 재기동 직후에 모든 언두 세그먼트를 수동으로 온라인시키는 것이 가능합니다.
alter system set "_smu_debug_mode"=4
alter rollback segment online;
alter system set "_smu_debug_mode"=0
AUM에 의한 Undo Segment의 관리
enq: US-contention 대기 이벤트에 대해 설명하기 전에 AUM(Automatic Undo Management)을 사용할 경우 언두 세그먼트가 온라인 또는 오프라인되는 시점이 언제인지를 이해할 필요가 있습니다. AUM의 경우 RBU(Rollback Segment Management)와는 달리 언두 세그먼트의 관리가 오라클에 의해 자동화됩니다. AUM을 사용하는 경우 언두 세그먼트가 온라인 또는 오프라인되는 시점은 다음과 같습니다.
1. 인스턴스 기동: 오라클 기동(Startup)은 크게 세 개의 과정으로 나누어집니다. 인스턴스 시작(Startup the instance), 인스턴스 마운트(Mount the instance), 데이터베이스 오픈(Open the database)의 과정으로 나누어 지며, 데이터베이스를 오픈(Open)하는 과정에서 언두 세그먼트를 온라인시킵니다.
2. alter system set undo_tablespace = xxxx를 통해 언두 테이블스페이스(Undo Tablespace)를 변경할 때 기존 언두 세그먼트들을 오프라인시키고, 새로운 언두 세그먼트들을 온라인시킵니다.
3. 트랜잭션의 증가와 감소: 트랜잭션의 양이 빠른 속도로 증가해서 기존에 사용하던 언두 세그먼트만으로는 트랜잭션을 감당할 수 없을 때 새로운 오프라인 상태의 언두 세그먼트를 온라인시키거나 새로운 언두 세그먼트를 생성합니다. 간단하게 이를 확인하려면 초기 사이즈가 작은 언두 테이블스페이스를 생성해서 트랜잭션이 늘어남에 따라 온라인 상태의 언두 세그먼트의 수가 늘어나는 것을 확인하면 됩니다. 일정 시간 트랜잭션이 없거나 줄어들면 오라클은 언두 세그먼트를 오프라인시킵니다. 이러한 작업은 SMON에 의해 이루어집니다. 오라클 9.2.0.4 이전 버전에서는 장시간 사용되지 않는 언두 세그먼트를 드롭시키는 경우도 있다고 알려져 있습니다.
enq: SQ-contention
오라클은 시퀀스를 관리하기 위해서 세가지의 락을 사용합니다.
- row cache 락: Sequence.nextval을 호출하는 과정에서 딕셔너리 정보를 물리적으로 변경하는 경우에 획득합니다. NOCACHE 속성을 부여한 시퀀스에서 사용됩니다.
- SQ 락: 메모리에 캐시되어 있는 범위 안에서 Sequence.nextval을 호출하는 동안 획득합니다. CACHE 속성을 부여한 시퀀스에서 사용됩니다.
- SV 락: RAC에서 노드 간에 순서가 보장된 상태로 Sequence.nextval을 호출하는 동안 획득합니다. CACHE + ORDER 속성을 부여한 시퀀스에서 사용됩니다.
CACHE 속성이 부여된 시퀀스에 대해 nextval을 호출하는 동안, SQ 락을 SSX 모드로 획득해야 합니다. 동시에 많은 세션인 SQ 락을 획득하기 위해 경쟁하는 과정에서 경합이 발생하면 enq: SQ - contention 이벤트를 대기하게 됩니다. enq: SQ - contention 이벤트의 P2 값은 시퀀스의 오브젝트 아이디입니다. 따라서 P2의 값을 이용해 DBA_OBJECTS 뷰와 조인하면 어떤 시퀀스에서 대기현상이 발생하는지 알 수 있습니다.
* Wait Time
최대 3초까지 기다린다. 만일 SQ 락을 획득하지 못하면 획득할 때까지 대기한다.
* Parameter
P1(Enqueue 정보), P2(Sequence ID, DBA_OBJECTS.OBJECT_ID 컬럼과 조인 가능), P3(0)
* Commons Causes and Actions
- 원인: Cache 속성이 부여된 시퀀스에 대해 nextval을 호출하는 세션의 경합 발생
- 진단 방법: P2 값을 이용하여 Sequence 명을 조회한 후 DBA_SEQUENCES를 통해 Cache Size 조회
- 개선 방법: 시퀀스의 캐시 크기를 크게 부여, RAC 환경에서는 가능한 NOORDER 속성 부여
시퀀스와 인덱스 리프 노드 블록의 경합
많은 애플리케이션들이 시퀀스 값을 이용해서 PK 키를 관리합니다. 이것은 일반적으로 바람직한 현상이라고 할 수 있으며, 오라클의 기본적인 권고안 중 하나입니다. 하지만 시퀀스 값을 이용한 PK 키는 이른바 우편향 인덱스(Right-Hand Index) 현상을 일으킬 우려가 있습니다. 우편향 인덱스란 키 값이 순차적으로 증가하는 경우 인덱스의 오른쪽 최말단 리프 노드에만 Insert가 집중되어 경합이 발생하는 현상을 말합니다. 우편향 인덱스는 다음과 같은 면에서 성능에 부정적인 영향을 줍니다.
오른쪽 말단 리프 노드에 Insert가 집중되므로 인덱스 분할(Index Split)이 빈번하게 발생합니다. 잦은 인덱스 분할은 리두 크기 증가, TX 락 경합 증가, DML 성능 저하 등의 문제를 유발합니다. 인덱스 분할에 의한 TX 락 경합은 enq: TX - index contention 이벤트 대기로 관찰됩니다. RAC 시스템에서는 gc current split 이벤트도 함께 관찰됩니다.
여러 프로세스가 동시에 Insert를 수행하는 경우 오른쪽 말단 리프 노드가 핫 블록이 될 가능성이 높아집니다. 핫 블록은 버퍼 락(Buffer Lock) 경합을 유발하며, 싱글 인스턴스 환경에서는 buffer busy waits 이벤트 대기로, RAC 시스템에서는 gc buffer busy 이벤트나 gc cr/current block busy, gc cr/current grant busy 등의 이벤트 대기로 관찰됩니다.
우편향 인덱스 현상은 잘 알려진 문제이며 다음과 같은 일반적인 해결방안이 제시되고 있습니다.
- 리버스 인덱스(Revserse Index)를 사용한다. 리버스 인덱스를 사용하는 경우에는 인덱스 리프 노드에 어느 정도 랜덤하게 분산되므로 우편향 현상을 줄일 수 있다.
- 인덱스 키를 변경한다. 순차적으로 증가하지 않는 키 조합을 사용함으로써 우편향 현상을 줄일 수 있다.
우편향 인덱스는 RAC 시스템에서 추가적인 오버헤드를 유발합니다. 클러스터 내의 모든 인스턴스들이 같은 리프블록을 변경하는 과정에서 글로벌 블록 경합이 발생하기 때문입니다. 각 인스턴스는 리프 블록을 변경하기 위해 블록의 최신 버전을 필요로 합니다. 각 인스턴스는 리프 블록을 변경하기 위해 블록의 최신 버전을 필요로 합니다. 각 인스턴스가 매번 블록을 변경하므로, 최신 버전의 블록이 노드 간에 끊임없이 전송되는 현상이 발생합니다. 즉, 글로벌 핫블록이 생기게되는 것입니다. 로컬 핫 블록은 버퍼 락 경합을 유발하고 buffer busy waits 이벤트 대기로 관찰되는 반면, 글로벌 핫블록은 글로벌 버퍼 락 경합 및 과다한 블록 전송을 유발하고 gc buffer busy 이벤트나 gc current request 류의 이벤트 대기로 관찰됩니다.
다행히 우편향 인덱스에 의한 글로벌 핫블록 현상은 글로벌 핫 블록 현상은 시퀀스 캐시 크기를 증가시킴으로써 어느 정도 해소가 가능합니다. 가령 두 개의 노드로 이루어진 RAC 시스템에서 시퀀스 캐시 크기가 10,000인 시퀀스의 값을 PK로 사용하는 인덱스를 가정해보겠습니다. 이 경우 인스턴스 1번은 { 1 ~ 10,000 }, 인스턴스 2번은 { 10,001 ~ 20,000 }의 시퀀스 값 집합을 사용합니다. 두 인스턴스가 다른 범위의 시퀀스 집합을 사용하기 때문에 같은 리프 블록을 사용하기 위해 경쟁할 확률이 감소합니다. 그만큼 글로벌 블록 경합이 감소하게 됩니다.
이런 의미에서 RAC 시스템에서 시퀀스 사용시 ORDER 속성을 부여하는 것은 성능면에서 상당히 불리한 결정이라고 할 수 있습니다. ORDER 속성의 시퀀스를 사용할 경우에는 노드간에 시퀀스 값을 끊임없이 동기화할 뿐만 아니라, 각 노드들이 동일한 시퀀스 캐시 값 집합을 사용하므로 우편향 인덱스에 의한 글로벌 핫 블록 현상을 피할 수 없기 때문입니다.
enq: HW - contention
HWM(High Water Mark)을 여러 프로세스가 동시에 변경하는 것을 막기 위한 락을 HW 락이라고 부릅니다. HWM을 이동시키고자 하는 프로세스는 반드시 HW 락을 획득해야 합니다. HW 락을 획득하는 과정에서 경합이 발생하면 enq: HW - contention 이벤트를 대기합니다. HW 락 경합은 대부분 대량 INSERT에 의해 발생하며, 간혹 대량 UPDATE에 의해 언두 세그먼트에서 HW 락 경합현상이 발생하는 경우도 있습니다. UPDATE의 경우 테이블 세그먼트의 추가확장은 크지 않지만 언두 데이터를 생성하는 과정에서 언두 세그먼트의 급속한 확장이 필요하기 때문입니다. HW 락 경합은 세그먼트의 급속한 공간확장이 필요한 경우에 매우 보편적으로 나타나는 대기현상이며, 극단적인 성능 저하를 야기하는 경우도 종종 발생합니다.
* Wait Time
Enqueue 대기 이벤트와 동일하다. 최대 3초까지 기다린다. 만일 HW 락을 획득하지 못하면 획득할 때까지 대기한다.
* Parameter
P1(Enqueue 정보), P2(Tablespace#), P3(Segment Header Block#)
* Common Causes and Actions
- 원인: FLM(Free List Management)을 사용하는 경우 대량의 DML 발생으로 인한 HW 락 경합
- 진단 방법: 세그먼트 공간 관리 기법을 확인한다. (수동/자동)
- 개선 방법
: FLM(수동 관리 기법)인 경우,
Storage 속성의 FREELISTS 속성 값(기본값은 1)을 동시 트랜잭션 수를 고려해 충분히 크게 설정 _BUMP_HIGHWATER_MARK_COUNT 히든 파라미터의 값을 크게 설정
: 적절한 크기의 익스텐트 사용 ASSM(자동 관리기법)인 경우
익스텐트의 크기를 크게 설정
세그먼트, 익스텐트, HWM의 개념
오라클은 세그먼트라는 개념을 이용해 데이터베이스 공간(Space)을 관리합니다. 따라서 오라클 공간과 관련된 성능 문제를 이해하려면 세그먼트 관리 기법에 대해 어느 정도의 지식이 필요합니다.
세그먼트(Segment)는 테이블, 인덱스, 언두, LOB 등 오라클이 제공하는 모든 종류의 논리적인 공간을 말합니다. 세그먼트는 다시 익스텐트(Extent)라는 논리적인 요소로 나누어지는데 익스텐트는 데이터 추가에 따른 세그먼트 공간의 확장을 담당하는 역할을 합니다. 가령, 세그먼트의 기존 여유공간이 다 소모된 상태에서 추가적인 Insert가 발생하면 오라클은 익스텐트를 추가로 할당해서 데이터를 저장합니다. 익스텐트는 블록(Block)이라는 연속적인 물리적 조각으로 이루어집니다. 블록은 오라클의 물리적/논리적 I/O의 최소 단위입니다.
오라클이 익스텐트에 의해 신규로 할당된 모든 공간을 다 실제로 사용 중인 것은 아니기 때문에 사용된 공간과 아직 사용되지 않은 공간을 구분하는 표식을 필요로 합니다. 이 표식을 고수위선(High Water Mark)이라고 부릅니다. HWM은 세그먼트 헤더 블록에 그 정보가 저장됩니다.
세그먼트, 익스텐트, 블록, HWM의 개념을 이해하기 위해 예를 들어 설명해보겠습니다.
현재 총 500블록이 할당되어 있으며 모든 블록들을 사용중인 테이블(세그먼트)에 1건의 로우를 추가로 Insert한다고 하겠습니다. 사용 가능한 공간이 없으므로 프로세스는 익스텐트를 추가로 할당해야 합니다. DMT(Dictionary Managed Tablespace)를 사용하는 경우에는 ST 락을 획득한 후에만 익스텐트 할당 작업이 가능합니다. ST 락을 획득하는 과정에서 경합이 발생하면 enq: ST - contention 이벤트를 대기합니다. ST 락 경합은 오라클 9i 이후로는 거의 발생하지 않습니다. 익스텐트가 추가로 할당되고 이에 따라 총 50 블록(1 익스텐트 = 50 블록이라고 가정)이 세그먼트에 추가됩니다. 50개의 블록 중에서 오라클은 몇 개의 블록(여기서는 5개라고 가정)만을 포맷하고 사용가능공간으로 등록합니다. 즉, 전체 550 블록 중 Used Block = 500 + 5 = 505, Unused Block = 45가 됩니다. 이러한 기준을 오라클에서는 HWM이라고 부릅니다. 즉, HWM은 세그먼트의 전체 공간 중 사용가능 공간(포맷된 공간)과 비사용 공간(미포맷 공간)을 구분하는 기준이 됩니다. 만일 추가로 계속해서 Insert가 이루어져서 포맷된 5개의 블록을 다 소진하게 되면 추가로 5개의 블록을 포맷하고 이를 사용가능 공간으로 전환하고 HWM을 이동하게 됩니다. HWM을 이동하는 작업은 HW 락을 통해 보호됩니다. HWM을 이동하고자 하는 프로세스는 반드시 세그먼트에 대해 HW락을 획득해야 합니다. HW 락을 획득하는 과정에서 경합이 발생하면 enq: HW - contention 이벤트를 대기합니다. 동시에 여러 세션이 동일 세그먼트에 대해 대량의 데이터를 추가하는 경우 잦은 HWM 이동에 의해 HW 락 경합 현상이 광범위하게 발생할 수 있습니다. HWM의 이동은 하나의 익스텐트가 모두 사용될 때까지 계속됩니다. 만일 하나의 익스텐트를 다 사용하게 되면 오라클은 추가적으로 다시 익스텐트를 할당합니다.
enq: TC - contention
인위적은 체크 포인트를 수행하는 작업들 중 일부는 TC 락(Thread Checkpoint Lock, 혹은 Tablespace Checkpoint Lock)을 획득해야 합니다. TC 락을 획득하는 과정에서 경합이 발생하면 enq: TC - contention 이벤트를 대기하게 됩니다. TC 락을 획득하는 과정은 아래와 같습니다.
1) 서버 프로세스가 우선 TC 락을 X 모드로 획득한다.
2) 서버 프로세스는 획득한 TC 락을 SSX 모드로 변경하고, 동시에 CKPT 프로세스가 SS 모드로 해당 락을 획득한다. CKPT는 락을 획득하고 체크 포인트 작업을 수행하게 된다.
3) 서버 프로세스는 다시 TC 락을 X 모드로 획득하려고 시도하는데, 해당 락이 CKPT에 의해 해제될 때까지 대기하게 된다. 이때의 대기 이벤트가 enq: TC - contention이다.
4) 체크 포인트 작업이 완료되면 CKPT 프로세스는 TC 락을 해제하고, 서버 프로세스는 TC 락을 획득함으로써 체크 포인트 작업이 끝났다는 것을 알게 된다.
enq: TC - contention 대기는 여러 프로세스에 의해 경합이 발생하지 않더라도 관찰이 된다는 점에서 락 경합에 의한 다른 대기현상들과는 구별이 됩니다. 대기현상에는 경합에 의해서만 발생할 수 있는 것들도 있지만, 경합이 발생하지 않더라도 특정 작업이 끝나기를 단순히 기다리는 경우도 있다는 것을 이해할 필요가 있습니다.
체크 포인트가 발생하는 경우는 매우 다양하지만 모든 경우에 의해 TC 락에 의한 대기가 발생하는 것은 아니며 서버 프로세스에 의해 유발된 체크 포인트 작업을 동기화시키는 과정에서만 발생합니다.
* Wait Time
최대 3초까지 기다린다. 만일 TC 락을 획득하지 못하면 획득할 때까지 대기한다.
* Parameter
P1(Enqueue 정보), P2(Checkpoint ID), P3(0)
* Common Causes And Actions
- 원인: 잦은 Parallel SQL 수행에 의한 TC 락 경합
- 진단 방법: 슬레이브 세션에서 Direct Path Read를 대기하는지 확인한다.
- 개선 방법
: Parallel Query(이하 PQ)에서 체크 포인트가 발생하는 이유는 슬레이브 세션(Slave Session)에 의한 direct path read 때문입니다. direct path read는 버퍼 캐시를 거치지 않고, 데이터 파일을 직접 읽는 것을 말합니다.
데이터 파일에서 직접 데이터를 읽은 경우 SGA를 경유하지 않기 때문에 현재 SGA에 있는 블록과 데이터 파일에 있는 블록 사이에 버전 불일치 현상이 생길 수 있습니다. 이러한 현상을 방지하기 위해 오라클은 데이터 파일에 대해 direct path read를 수행하기 전에 체크 포인트를 수행해야 합니다. 코디네이터 세션(Coordinator Session)은 슬레이브 세션을 구동하기 전에 direct path read를 수행할 객체에 대해 세그먼트 레벨의 체크 포인트를 요청하게 되고 체크 포인트 작업이 끝날 때까지 enq: TC - contention 이벤트를 대기합니다. 코디네이터 세션에서는 enq: TC - contention 대기가 목격되고 슬레이브 세션에서는 direct path read 대기가 목격된다는 것에 주의해야합니다. TC 락 경합 현상에 대한 글을 관련 사이트 등에서 검색하면 하이브리드 시스템, 즉 OLTP와 DSS가 혼용되어 사용되는 시스템에서 많이 발생하는 것으로 나오는데, 잦은 TC 락 경합이 발생할 경우 무분별한 PQ 수행에 의한 것이 아닌지 확인이 필요합니다.
MTTR(Mean Time To Recovery)와 TC락
Truncate 및 Drop과 같은 DDL문 수행에 의해 Buffer Cache에서 관련 데이터를 동기화할 목적으로 디스크로 내려쓰는 작업이 발생하기도 하지만, Fast Start Checkpoint에 의해서도 발생할 수 있습니다.
MTTR, 즉 복구에 필요한 최소시간을 부여하기 위해 FAST_START_MTTR_TARGET 파라미터를 0보다 큰 값으로 설정한 경우 오라클은 시스템 Redo 발생량을 계산하여 자동적으로 Checkpoint 주기를 설정하게 됩니다.
이처럼 FAST_START_MTTR_TARGET 값과 Checkpoint 주기는 반비례하기 때문에 복구에 필요한 최소시간을 크게 줄 경우 인위적인 Checkpoint 발생에 의한 TC락 경합을 줄일 수 있으며, 0으로 줄경우(Disable) Redo Log File Switch 발생 시에만 Checkpoint가 발생하게됩니다.
'데이터베이스(DA, AA, TA) > Oracle' 카테고리의 다른 글
| [오라클] RAC(Real Application Cluster)이란? (2) | 2017.12.17 |
|---|---|
| [오라클] Redo Log 튜닝 방법 (0) | 2017.12.16 |
| [오라클] Library Cache 튜닝 방법 (0) | 2017.12.05 |
| [오라클] I/O SQL튜닝 방법 정리(2) (0) | 2017.12.03 |
| [오라클] I/O SQL튜닝 방법 정리(1) (0) | 2017.12.03 |