[10/23] index leaf node에서 split이 발생하는 경우

enq : TX - index contention, mode = 4

b*tree 인덱스는 데이터를 추가하는 과정에서 리프 노드가 꽉 차면 split을 함으로써 균형(balance)을 맞추게 된다 세션 A가 TX 락을 Exclusive하게 획득한 상태에서 split을 수행하는 도중에 세션 B가 해당 리프 노드를 변경하고자 하는 경우 세션 B는 세션 A의 트랜잭션에 대해 TX락을 Shared 모드로 획득하기 위해 기다려야 하며 그 동안 enq : TX - index contention 이벤트를 대기한다

enq : TX - index contention 대기 이벤트는 보통의 경우 잘 발생하지 않으며 동시에 여러 세션이 인덱스가 생성되어 있는 테이블에 대해서 많은 양의 DML을 수행하는 경우에 주로 발생한다 자주 발생하지 않는 대신 생성된 인덱스의 수가 많고 인덱스를 이루는 컬럼들의 값이 커서 리프 노드 블록이 빈번하게 Split 되는 경우에는 상당한 성능저하의 원인이 된다 특히 시퀀스 등을 사용해서 값을 생성하는 컬럼에 대해 인덱스가 생성된 경우 항상 제일 마지막 리프 노드에만 값이 추가되는 현상이 생겨 인덱스 split이 자주 발생할 수 있다 이것은 정렬된 형태로 리프 노드를 유지하는 비트리 이덱스의 속성에 의해 발생하는 것으로 여러 세션에 의해서 많은 양의 데이터를 insert 하는 경우 buffer busy waits 대기와 함께 enq : TX - index contention 대기가 같이 발생하게 된다

인덱스 split에 의해 발생하는 경합을 줄이는 기본적인 방법은 동일한 리프 노드 블록에 집중적으로 데이터가 추가되는 현상을 막는 것이다 가령 파티셔닝 기법을 적용해서 물리적으로 분산을 시키거나 인덱스를 구성하는 컬럼의 순서를 변경하여 자연스럽게 흩어지게 만드는 방법 등을 사용할 수 있다 하지만 반드시 특정 키를 기준으로 정렬이 된 형태를 유지해야 한다는 제약조건이 있다면 이런 방법을 사용할 수 없다 대표적인 경우가 Primary Key의 값을 Sequence를 이용해 부여하고 /*+ INDEX_DESC */와 같은 힌트 등을 이용해 이 인덱스에 대해 정렬된 방식으로 데이터를 스캔하는 쿼리를 사용하는 것이다 이런 방식으로 인덱스를 사용할 경우에는 반드시 해당키를 기준으로 정렬이 보장이 되어야 하므로 인덱스에 파티셔닝을 적용하거나 인덱스를 구성하는 컬럼의 순서를 변할 수 없다

인덱스의 블록 크기를 크게 설정하는 것도 하나의 해결책이 된다 큰 크기의 블록을 사용할 경우 하나의 블록에 들어가는 엔트리의 수가 많으므로 split이 그만큼 덜 발생하기 때문이다 하지만 블록 크기가 증가하게 되면 buffer lock 경합에 의해 buffer busy waits 대기 현상이 유발될 수 있으므로 조심해야 한다

인덱스가 생성되어 있지 않은 테이블을 변경하는 과정에서도 enq : TX - index contention 대기가 발생하는 경우도 있다는 것에 유의하자 테이블에 LOB 컬럼이 있는 경우다 테이블에 LOB 컬럼이 있으면 내부적으로 LOB 데이터에 대한 인덱스(LOB 인덱스라 한다)를 생성하기 때문에 여러 세션이 동시에 LOB 데이터를 변경하는 경우 인덱스 경합이 발생할 수 있다.