어느 날 아침 DB 상태를 체크하던 중 중요 테이블의 PK 인덱스가 unusble 상태로 깨진 것을 발견했다. 급하게 재생성을 시도했지만 PK 컬럼의 중복 데이터가 있어 인덱스 재생성이 되지 않았다. 당연히 PK 인덱스가 깨져 있으므로 기존 PK 인덱스를 사용하던 SQL의 수행속도는 엄청나게 느려졌고, 결국 장애 상황이 발생하고 말았다.

그런데 이 테이블에는 야간에 수행되는 배치작업 밖에 없었다. 이를 들여다보니 SQL Laoder로 데이터를 로딩하는 작업이 이번 장애의 원인임을 알게 됐다.

그렇다면 SQL Loader로 데이터를 로딩하는 것과 테이블의 PK 인덱스가 무슨 관련이 있을까? SQL Loader로 데이터를 로딩하는 작업이 PK 인덱스를 unusable로 만드는 것일까?

이러한 물음에 대한 대답은 SQL Loader 작업이 PK 인덱스(정확하게는 unique 인덱스들)를 Unusable로 만들 수 있다는 것인데, 지금부터 그 이유를 살펴보자.

SQL Loader

SQL Loader는 OS 플랫(flat) 파일로 존재하는 기존의 응용 프로그램이나 다른 DB에 저장된 데이터를 오라클 DB에 넣기 위한 유틸리티로, 오라클 DB를 설치하면 기본적으로 사용 가능한 툴이다.

쉽고 간단하게 데이터를 DB에 로드할 수 있어 각광받고 있는데, 써드파티 툴인 오렌지 또는 토드 Loader 기능을 사용하면 더욱 편리하게 사용할 수 있다.

Conventional path 방식과 Direct path 방식

SQL Loader의 동작 방식에는 Conventional path와 Direct path 두 가지가 있다. 그 중 Conventional path는 SQL Loader의 rows & bindsize option으로 설정된 일정량의 버퍼를 채우면 SQL Insert 문장을 이용해 로딩하는 방식이다.

즉 Array Insert로 동작하며 Insert 문장으로 동작하기에 일반 Insert 문장과 부하가 같다. 또한 Insert 문장이므로 Row-level locking을 지원하는데 다른 OLTP 트랜잭션을 블록킹하지 않는다.

Direct path 방식은 Direct path load Engine을 사용해 오라클의 버퍼 캐시를 거치지 않고 디스크에 직접 쓰기 때문에 Conventional path 방식에 비해 부하 및 상호 간의 경쟁 없이 빠르게 수행된다.

그러나 로딩되는 테이블 전체에 Lock(TM-X mode lock)이 걸려 로딩 중에는 OLTP 트랜잭션을 블록킹하는 점이 Conventional path 방식과의 차이다.

참고로 파티션 테이블의 경우 Conventional path 방식은 특정 파티션에 대한 로딩은 insert into [table] partition [partition]과 동일한 SQL Insert 문장으로 수행한다.

Direct path 방식은 특정 파티션에 대해서만 Lock을 걸고 작업을 수행하기 때문에 로딩 중에는 파티션 DML이 불가능한 대신 다른 파티션에 대한 DML 작업은 가능하다.

그렇다면 Conventinoal path 방식과 Direct path 방식 중 어떤 방식을 사용해야 할까? 앞서 설명한 Locking mode에 의한 제약사항을 제외할 경우 수행 속도나 부하 측면에서 Direct path 방식이 더 좋다.

그러나 Conventional path와 Direct path 방식의 적용은 케이스에 따라 구별해 사용해야 한다. 참고로 다음 사항들을 모두 만족하며 수행 속도를 빠르게 하고자 할 때에는 Direct path를 사용하면 된다.

Direct path 방식은 Conventional 방식과는 다르게 동작하며 그에 따라 제약조건 체크 및 트리거 사용 유무에 차이가 있다.

Conventional path 방식을 사용해야 하는 상황

• - 데이터 로딩 중에도 로딩되고 있는 테이블에 DML이 계속 수행돼야 하는 경우
• - Direct path 방식을 지원하지 않는 클러스터 테이블, 오라클 시퀀스를 사용할 때
• - 데이터 로딩 중에 제약조건(unique, check 등)에 의해 에러가 발생한 데이터를 걸러야 할 필요가 있을 때
• - 사이즈가 큰 인덱스가 있는 테이블에 적은 양의 데이터를 로딩해야 할 때
• - 데이터 로딩 중에 트리거가 동작해야 할 때

Direct path 방식의 제약조건 및 트리거 사용 유무

• - Not Null : 데이터가 로딩되는 과정에서 검증됨(Conventional path 방식과 동일)
• - Unique : 데이터가 로딩된 후 최종 인덱스가 생성되는 시점에 검증됨
• - Primary key : 데이터가 로딩된 후 최종 인덱스가 생성되는 시점에 검증됨
• - Check : 데이터가 로딩되기 전에 자동으로 disable됨
• - 트리거 사용 유무 : Direct path 방식을 사용할 경우 데이터 로딩 이전에 자동으로 Disable됐다가 로딩 후에는 Enable됨. 즉 로딩 중에는 트리거가 동작하지 않음

Conventional path와 Disable 방식의 특징을 요약한 결과는 [표 1]과 같다.

• [표 1] Conventional path와 Direct path

• 	Conventional path	Direct path
  사용법	Default	SQL Loader 실행 시 direct=true 옵션 시용
  속도	느림	빠름
  Lock mode	Row-level locking	Table-level locking
  Redo량	항상 리두 항목 생성	특정조건(Archive mode)에 서만 리두 생성
  제약조건 검증	모든 제약조건 적용	PK，Unique, Not Null 제약 만 가능
  클러스터 테아블로 로딩	가능	불가능
  로딩 중 트리거 사용	가능	불가능
  가용성	높음	낮음

SQL Loader 사용 시 PK 인덱스가 unusable로 빠지는 이유

자 이제 처음 질문에 답을 할 차례다. 결론부터 말한다면 SQL Loader를 사용해 데이터를 로딩할 때 무조건 PK 인덱스가 unusable로 빠지는 것이 아니라 Direct path 방식(direct= true)을 사용할 때에만 PK 인덱스가 unusable로 빠진다는 점이다.

Conventional path 방식은 로딩 데이터의 매 건마다 인덱스 키를 생성하기 때문에 절대로 이런 경우가 발생하지 않는다. Direct path 방식을 사용할 때에만 PK 인덱스가 unusable로 빠지는 것은 왜 그럴까? 그 해답은 Direct path 방식의 인덱스를 갱신하는 부분에서 찾을 수 있다.

① 데이터를 로딩하면서 인덱스 키에 해당하는 데이터는 Sort Segment (temporary) 영역에 저장됨

② 데이터 로딩이 끝난 후(commit) 기존의 인덱스와 Sort Segment를 합치는 작업(Merge)이 수행됨

③ 데이터가 합쳐진 후 최종적인 인덱스가 완성됨

이러한 순서로 Direct path는 동작하는데 ②번 작업을 수행할 때 데이터 중복이나 테이블 스페이스 공간이 부족하면 인덱스가 unusable 상태로 빠지게 된다.

제약조건 체크 방식에서 설명했듯 unique, primary key 제약조건은 데이터 로딩 이후에 최종 인덱스가 생성되는 시점(②)에 검증이 이루어지기 때문이다. 이로 인해 Direct path 방식으로 데이터를 로딩하면 PK 인덱스가 unusable로 빠지는 것이다.

Direct path & Parallel이 로딩될 때 select의 인덱스 사용 여부

추가로 Direct path나 parallel로 데이터를 로딩할 때 다른 세션에서 날린 select 문장의 실행 계획은 어떻게 되는지 지금부터 살펴보자.

테이블에 데이터가 로딩 중일 때 다른 세션에서 전송한 select문이 인덱스를 잘 탈까? 이러한 물음에 대한 답은 인덱스 로딩 중에도 사용 가능하다는 것이다. 그 이유는 Direct path나 Parallel의 로딩 중에 인덱스가 unusable로 되는 것이 아니라 계속 usable 상태로 존재하기 때문이다.

기존 인덱스와 Sort Segment를 합쳐 새로운 인덱스로 교체되는데, 새로운 인덱스로 교체되는 잠깐의 시간을 제외한다면 항상 인덱스를 사용할 수 있다.

이렇듯 SQL Laoder 작업 방식에 따라 내부적으로 수행되는 방식이나 결과는 크게 달라질 수 있다. 그러므로 작업 시 어떤 방식을 사용할지 적절히 선택해야 한다.