Troubleshooting Wait Event enq: TX - allocate ITL entry

참고 : [Troubleshooting waits for 'enq: TX - allocate ITL entry' (Doc ID 1472175.1)] 

테이블을 생성할 때 테이블을 구성하게 될 블록의 아래 3가지 값을 지정할 수 있습니다.
(10g 이후로 MAXTRANS는 255로 고정)

- INITRANS : 블록의 헤더마다 몇 개의 ITL 슬롯을 미리 확보할 지 정합니다. (default 로 table은 1, index 는 2)

- MAXTRANS : 최대 몇 개의 ITL 슬롯을 사용할지 정합니다. 10g 이후로 255로 고정입니다.

- PCTFREE : 블록의 사용자데이터를 저장하지 않을 공간의 비율로, 블록이 최초 생성될 때 INITANS 값 만큼 ITL 슬롯을 확보하고 추가로 동시 트랜잭션이 증가하면 PCTFREE로 확보된 영역 내에서 MAXTRANS 만큼 추가로 확장합니다. (default 10%)

 

대기 이벤트 관련 튜닝 방법

1.   어떤 테이블에 ‘enq: TX - allocate ITL entry’ 대기 이벤트가 일어나는지 확인합니다.   ASH(ACTIVE SESSION HISTROY) 에서 대기이벤트로 해당 오브젝트를 찾거나 혹은 아래 쿼리로 ITL 대기가 높은 object를 확인해 보실 수도 있습니다.

select * from v$segment_statistics
where statistic_name = 'ITL waits'
and owner not in ('SYS','SYSTEM')
order by value desc;

 

2.    대상 테이블, 인덱스의 initrans 값과 pctfree 값을 확인합니다.

select owner, table_name, ini_trans, pct_free from dba_tables where owner =계정명 and table_name=테이블명;
select owner, index_name, ini_trans, pct_free from dba_indexes where owner =계정명 and index_name=인덱스명;

 

3.    Initrans 및 pctfree 값 튜닝

문서에 ‘enq: TX - allocate ITL entry’ 대기 이벤트를 줄이기 위해, initrans 값 또는 pctfree 값 또는 둘 다를 증가시켜 보기를 가이드 하는데, 그 적정 값에 대한 명시는 없습니다. 

alter table move 명령어 시 테이블에 Lock 이 걸리며, 테이블의 블록구조를 바꾸는 것이기 때문에 상당한 시간이 걸릴 것으로 예상됩니다.

그리고 그 테이블의 모든 인덱스가 UNUSABLE 상태로 빠져서 모두 Rebuild 해주어야 합니다. 이 또한 테이블이 크다면 상당한 시간이 걸릴 것 입니다.

alter table move 명령어에 online 옵션이 가능은 하나, 작업 완료 시 PK 인덱스 관련 에러가 발생하면서 정상작업이 안되는 것으로 보입니다. Online 옵션의 사용도 어려울 것 같습니다.

작업을 진행하실 것이라면 테스트 DB에 먼저 테스트 해보시고, 시간 측정 후 운영DB에 해당 테이블 트랜잭션이 발생하지 않는 상황을 만드시고 작업하시는 것이 안전할 것으로 보입니다.

테이블의 INITRANS 값, PCTFREE 값 변경, 신규 블록에 대해서만 적용
Alter table <계정>.<테이블명> pctfree 20  initrans 50;

기본 블록에도 위 설정값을 적용, 단, 테이블에 Lock이 발생하며 Index는 모두 UNUSABLE 상태가 된다.
Alter table <계정>.<테이블명> move;

기존 인덱스 Rebuild 작업, 인덱스에도 initrans 값과 pctfree 값을 적용한다.
Alter index <계정>.<인덱스명> rebuild online pctfree 20  initrans 50;