친절한 SQL 튜닝_6장.DML 튜닝_2

 

DML 튜닝

6.2 Direct Path I/O 활용

 

온라인 트랜잭션은 기준성 데이터, 특정 고객, 특정 상품, 최근 거래 등을 반복적으로 읽기 때문에 버퍼캐시가 성능 향상에 도움을 준다. 반면, 정보계 시스템(DW/OLAP 등)이나 배치 프로그램에서 사용하는 SQL은 주로 대량 데이터를 처리하기 때문에 버퍼캐시를 경유하는 I/O메커니즘이 오히려 성능을 떨어뜨릴 수 있다. 그래서 오라클은 버퍼캐시를 경유하지 않고 곧 바로 데이터 블록을 읽고 쓸 수 있는 Direct Path I/O 기능을 제공하는데, 지금부터 살펴보자.

 

 

 

6.2.1 Direct Path I/O

 

일반적인 블록 I/O는 DB 버퍼캐시를 경유한다. 즉, 읽고자 하는 블록을 먼저 버퍼캐시에서 찾아보고, 찾지 못할 때만 디스크에서 읽는다. 데이터를 변경할 때도 먼저 블록을 버퍼캐시에서 찾는다. 찾은 버퍼블록에 변경을 가하고 나면, DBWR 프로세스가 변경된 블록(Dirty 블록)들을 주기적으로 찾아 데이터파일에 반영해 준다.

 

자주 읽는 블록에 대한 반복적인 I/O Call을 줄임으로써 시스템 전반적인 성능을 높이려고 버퍼캐시를 이용하지만, 대량 데이터를 읽고 쓸 때 건건이 버퍼캐시를 탐색한다면 개별 프로그램 성능에는 오히려 안 좋다. 버퍼캐시에서 블록을 찾을 가능성이 거의 없기 때문이다.

 

대량 블록을 건건이 디스크로부터 버퍼캐시에 적재하고서 읽어야 하는 부담도 크다. 그렇게 적재한 블록을 재사용할 가능성이 있느냐도 중요한데, Full Scan 위주로 가끔 수행되는 대용량 처리 프로그램이 읽어 들인 데이터는 대개 재사용성이 낮다. 그런 데이터 블록들이 버퍼캐시를 점유한다면 다른 프로그램 성능에도 나쁜 영향을 미친다.

 

그래서 오라클은 버퍼캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있는 Direct Path I/O 기능을 제공한다. 아래는 그 기능이 작동하는 경우다.

 

1. 병렬 쿼리로 Full Scan을 수행할 때

 

2. 병렬 DML을 수행할 때

 

3. Direct Path Insert를 수행할 때

 

4. Temp 세그먼트 블록들을 읽고 쓸 때

 

5. direct 옵션을 지정하고 export를 수행할 때

 

6. nocache 옵션을 지정한 LOB 컬럼을 읽을 때

 

 

 

6.2.2 Direct Path Insert

 

일반적인 INSERT가 느린 이유는 다음과 같다.

 

1. 데이터를 입력할 수 있는 블록을 Freelist에서 찾는다. 테이블 HWM(Higt-Water Mark) 아래쪽에 있는 블록 중 데이터 입력이 가능한(여유 공간이 있는) 블록을 목록으로 관리하는데, 이를 ‘Freelist’라고 한다.

 

2. Freelist에서 할당받은 블록을 버퍼캐시에서 찾는다.

 

3. 버퍼캐시에서 없으면, 데이터파일에서 읽어 버퍼캐시에 적재한다.

 

4. INSERT 내용을 Undo 세그먼트에 기록한다.

 

5. INSERT 내용을 Redo 로그에 기록한다.

 

 

 

Direct Path Insert 방식을 사용하면, 대량 데이터를 일반적인 INSERT 보다 휠씬 더 빠르게 입력할 수 있다. 데이터를 Direct Path Insert 방식으로 입력하는 방법은 다음과 같다.

 

- INSERT … SELECT 문에 append 힌트 사용

 

- parallel 힌트를 이용해 병렬 모드로 INSERT

 

- direct 옵션을 지정하고 SQL*Loader(sqlldr)로 데이터 적재

 

- CTAS(CREATE TABLE … AS SELECT) 문 수행

 

 

 

Direct Path Insert 방식이 빠른 이유는 다음과 같다.

 

1. Freelist를 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력한다.

 

2. 블록을 버퍼캐시에서 탐색하지 않는다.

 

3. 버퍼캐시에 적재하지 않고, 데이터파일에 직접 기록한다.

 

4. Undo 로깅을 안 한다.

 

5. Redo 로깅을 안 하게 할 수 있다. 테이블을 아래와 같이 nologgin 모드로 전환한 상태에서 Direct Path Insert 하면 된다.

 

alter table t NOLOGGING;

 

참고로, Direct Path Insert가 아닌 일반 INSERT 문을 로깅하지 않게 하는 방법은 없다.

 

 

 

Direct Path Insert를 사용할 때 주의할 점이 두 가지 있다.

 

첫째, 이 방식을 사용하면 성능은 비교할 수 없이 빨리지지만 Exclusive 모드 TM Lock이 걸린다는 사실이다. 따라서 커밋하기 전까지 다른 트랜잭션은 해당 테이블에 DML을 수행하지 못한다. 트랜잭션이 빈번한 주간에 이 옵션을 사용하는 것은 절대 금물이다.

 

둘째, Freelist를 조회하지 않고 HWM 바깥 영역에 입력하므로 테이블에 여유 공간이 있어도 재활용하지 않는다는 사실이다.

 

과거 데이터를 주기적으로 DELETE 해서 여유 공간이 생겨도 이 방식으로만 계속 INSERT하는 테이블은 사이즈가 줄지 않고 계속 늘어만 간다. Range 파티션 테이블이면 과거 데이터를 DELETE가 아닌 파티션 DROP 방식으로 지워야 공간이 반환이 제대로 이루어진다. 비 파티션 테이블이면 주기적으로 Reorg 작업을 수행해 줘야 한다.

 

 

 

6.2.3 병렬 DML

 

INSERT는 append 힌트를 이용해 Direct Path Write 방식으로 유도할 수 있지만, UPDATE, DELETE는 기본적으로 Direct Path Write가 불가능하다. 유일한 방법은 병렬 DML로 처리하는 것이다. 병렬 처리는 대용량 데이터가 전제이므로 오라클은 병렬 DML에 항상 Direct Path Write 방식을 사용한다.

 

DML을 병렬로 처리하려면, 먼저 아래와 같이 병렬 DML을 활성화해야 한다.

 

ALTER SESSION SET ENABLE PARALLED DML;

 

그리고 나서 각 DML 문에 아래와 같이 힌트를 사용하면, 대상 레코드를 찾는 작업(INSERT는 SELECT 쿼리, UPDATE/DELETE는 조건절 검색)은 물론 데이터 추가/변경/삭제도 병렬로 진행한다.

 

INSERT /*+ PARALLEL(C 4) */ 
  INTO 고객 C
SELECT /*+ FULL(O) PARALLEL(O 4) */ 
  FROM 외부가입고객 O;
 
UPDATE /*+ FULL(O) PARALLEL(O 4) */ 고객 C 
   SET 고객상태코드 = ‘WD’
WHERE 최종거래일시 < ‘20100101’;
 
DELETE /*+ FULL(O) PARALLEL(O 4) */ 고객 C
 WHERE 탈퇴일시 < ‘20100101’;

 

 

 

힌트를 제대로 기술했는데, 만약 실수로 병렬 DML을 활성화하지 않으면 어떻게 될까? 대상 레코드를 찾는 작업은 병렬로 진행하지만, 추가/변경/삭제는 QC(Query Coordinator)가 혼자 담당하므로 병목이 생긴다.

 

병렬 INSERT는 append 힌트를 지정하지 않아도 Direct Path Insert 방식을 사용한다. 하지만, 병렬 DML이 작동하지 않을 경우를 대비해 아래와 같이 append 힌트를 같이 사용하는게 좋다. 혹시라도 병렬 DML이 작동하지 않더라도 QC가 Direct Path Insert를 사용하면 어느 정도 만족할 만한 성능을 낼 수 있기 때문이다.

 

INSERT /*+ APPEND PARALLEL(C 4) */ 
  INTO 고객 C
SELECT /*+ FULL(O) PARALLEL(O 4) */ 
  FROM 외부가입고개 0;

병렬 DML도 Direct Path Write 방식을 사용하므로 데이터를 입력/수정/삭제할 때 Exclusive 모드 TM Lock이 걸린다는 사실을 꼭 기억하자.

 

출처 : 친절한 SQL 튜닝 - 조시형 지음