SQL 튜닝이론 - DirectPath I/O 를 활용한 DML 성능개선(DIRECT PATH I/O, 병렬 DML, APPEND, EXCLUSIVE MODE TX LOCK)

SQL 튜닝이론 - DirectPath I/O 를 활용한 DML 성능개선(DIRECT PATH I/O, 병렬 DML, APPEND, EXCLUSIVE MODE TX LOCK)

 

 

버퍼캐시는 반복적으로 데이터를 읽는 업무가 많은 온라인 트랜잭션의 성능 향상에 큰 도움을 줍니다. 반면, 대량의 데이터를 처리하는 정보계 시스템이나 배치 프로그램에서 버퍼캐시를 경유하는 I/O 메커니즘은 오히려 성능을 저하를 유발합니다. 그래서 오라클은 버퍼캐시를 경유하지 않고 곧바로 데이터 블록에 접근하는 DIRECT PATH I/O 기능을 제공합니다.

 

DIRECT PATH I/O

 

일반적으로 블록 I/O는 DB 버퍼캐시를 경유합니다. 읽고자 하는 블록을 버퍼캐시에서 먼저 찾아보고 없으면 디스크에서 읽습니다. 데이터를 변경할때도 대상 데이터를 먼저 버퍼캐시에서 찾습니다. 찾은 버퍼블록에 변경을 가하면 DBWR 프로세스가 변경된 블록(DIRTY BLOCK)을 주기적으로 데이터파일에 반영합니다.

 

대량 데이터에 경우 읽고 쓸때 버퍼캐시에서 블록을 찾을 가능성은 매우 낮습니다. 대량 블록을 건건이 디스크로부터 버퍼캐시에 적재하고 읽어야 하는 부담도 큽니다. 또한 대용량 처리 프로그램이 읽어 들인 데이터는 대게 재사용성이 낮습니다. DIRECT PATH I/O 가 작동하는 경우는 다음과 같습니다.

 

1. 병렬 쿼리로 FULL SCAN을 수행할 때
2. 병렬 DML을 수행할 때
3. DIRECT PATH INSERT 를 수행할 때
4. TEMP 블록을 읽고 쓸 때
5. DIRECT 옵션을 지정하고 EXPORT를 수행할 때
6. NOCHACHE 옵션을 지정하고 LOB 칼럼을 읽을 때

 

병렬 쿼리

 

아래 쿼리문처럼 parallel , parallel_index 힌트를 사용하면 사용자가 지정한 병렬도 만큼 분산해서 작업을 처리합니다.

 

select /*+full(t) parallel(t 4)*/ * 
from big_table;

select  /*+index_ffs(t big_table_x1) parallel_index(t big_table_x1 4)*/
count(*)
from big_table

 

위 쿼리는 병렬도를 4로 지정했습니다. 성능이 4배보다 훨씬 더 빨라지는데 그 이유는 DIRRECT PATH I/O입니다. 버퍼캐시를 탐색하지 않고 디스크로부터 버퍼캐시에 적재하는 부담도 없습니다. ORDER BY, GROUP BY, HASH JOIN, SORT MERGE JOIN으로 처리할 때는 힌트로 지정한 병렬도 보다 두 배 더 많은 프로세스가 사용됩니다.

 

DIRECT PATH INSERT

 

INSERT 작업이 느린 이유는 다음과 같습니다.

 

1. 데이터를 입력할 수 있는 블록을 FREELIST에서 찾습니다.
2. 대상 레코드가 버퍼캐시에 없으면 데이터파일에서 읽어 버퍼캐시에 적재합니다.
3. INSERT 내용을 UNDO 세그먼트에 기록합니다.
4. INSERT 내용을 REDO 로그에 기록합니다.

 

DIRECT PATH INSERT 방식을 사용하면, 대량 데이터를 일반적인 INSERT 보다 훨씬 더 빠르게 입력할 수 있습니다. DIRECT PATH INSERT 방식이 빠른 이유는 다음과 같습니다.

 

1. FREELIST를 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력합니다.
2. 블록을 버퍼캐시에서 탐색하지 않습니다.
3. 데이터를 버퍼캐시에 적재하지 않고, 데이터파일에 직접 기록합니다.
4. UNDO 로깅을 하지 않습니다.
5. REDO 로깅을 하지 않도록 설정할 수 있습니다. 아래 쿼리로 nologging 모드로 전환한 상태에서 DIRECT PATH INSERT를 진행합니다.

 

alter table t NOLOGGING;

 

일반 INSERT 문을 실행할 때 REDO로깅을 하지 않게 하는 방법은 없습니다. ARRAY PROCESSING도 DIRECT PATH INSERT 방식으로 처리할 수 있습니다. APPEND_VALUES 힌트를 사용합니다.

 

...
procedure insert_target(p_source in type_source) is
begin
  forall i in p_source.first..p_source.last
    insert /*+ append_value */ into target values p_source(i);
end insert_target
...

 

DIRECT PATH INSERT 방식으로 입력하는 방법은 다음과 같습니다.

 

1. INSERT SELECT 문에 APPEND 힌트 사용
2. parallel 힌트를 이용해 병렬 모드로 INSERT
3. Direct 옵션을 지정하고 SQL LOADER 데이터 적제
4. CREATE TABLE AS SELECT 문 실행

 

DIRECT PATH INSERT 방식을 사용할 때 주의할 점은 다음과 같습니다. 이 방식은 EXECLUSIVE TM LOCK을 유발합니다. 따라서 커밋하기 전까지 다른 트랜잭션은 해당 테이블에 DML이 불가합니다. 트랜잭션이 빈번한 시기에 이 옵션을 사용해선 안됩니다. FREELIST를 조화히지 않고 하드웨어 메모리 바깥 영역에 데이터를 입력하므로 테이블에 여유 공간이 있어도 사용하지 않습니다. DIRECT PATH INSERT 방식으로 입력한 과거 데이터를 주기적으로 삭제해서 여유공간이 생겨도 테이블은 사이즈가 줄지 않고 계속 늘어갑니다.

 

병렬 DML

 

INSERT는 APPEND 힌트를 이용해 DIRECT PATH INSERT 방식으로 유도할 수 있지만, UPDATE, DELETE는 기본적으로 DIRECT PATH WRITE  가 불가능합니다. 유일한 방법은 병렬 DML로 처리하는 것입니다. 병렬처리는 대용량 데이터가 전제이므로 오라클은 병렬 DML에 항상 DIRECT PATH WRITE 방식을 사용합니다. DML을 병렬로 처리하기 위해선 병렬 DML을 활성화해야 합니다.

 

alter session enable parallel dml;

 

그러고 나서 각 DML 문에 아래와 같이 힌트를 사용하면, 대상 레코드를 찾는 작업은 물론 데이터의 추가/변경/삭제도 병렬로 진행합니다.

 

insert /*+PARALLEL (C 4*/ into 고객 C
select /*+FULL(O) PARALLEL(O 4)*/ * from 외부가입고객 O;

update /*+full(c) parallel(c 4)*/ 고객 c set 고객상태코드 = 'WD'
where 최종거래일시 < '20100101'

delete /*+full(c) parallel(c 4)*/ from 고객 c
where 탈퇴일시 < '20100101'

 

병렬 DML을 활성화하지 않고 병렬처리하면 대상 레코드를 찾는 과정(insert의 select / update와 Delete의 조건절)은 병렬로 진행하지만 추가/변경/삭제는 QC가 단독으로 담당하므로 병목현상이 발생합니다. 병렬 INSERT는 append 힌트를 지정하지 않아도 DIRECT PATH INSERT 방식을 사용합니다.

 

하지만 병렬 DML 이 작동하지 않을 경우를 대비해 append 힌트를 명시하는 것이 좋습니다. 병렬 DML이 작동하지 않더라도 QC가 DIRECT PATH INSERT를 사용하면 성능을 올릴 수 있습니다. 12C부터는 enable_parallel_dml 힌트를 사용해 dml을 병렬로 처리할 수 있습니다

 

insert /*+ append parallel(c 4)*/ into 고객 C
select /*+full(o) parallel (o 4)*/ * from 외부가입고객 o;

-- 12c 부터는 아래와 같이 enable_parallel_dml 힌트도 지원한다.
update /*+enable_parallel_dml full(c) parallel(c 4)*/ 고객 c
set 고객상태코드 = 'WD'
where 최종거래일시 < '20100101'

Delete  /*+enable_parallel_dml full(c) parallel(c 4) */ from 고객 c
where 탈퇴일시 < '20100101';

 

병렬 DML은 DIRECT PATH WRITE 방식을 사용하므로 EXCLUSIVE MODE TM LOCK이 발생합니다.

 

병렬 DML이 잘 작동하지는 확인하는 방법

 

DML 작업을 각 병렬 프로세스가 처리하는지, 아니면 QC가 처리하는지는 실행계획에서 확인할 수 있습니다. 아래의 실행계획과 같이 UPDATE/DELETE/INSERT 가 PX COORDINATOR 아래쪽에 나타나면 DML을 각 병렬 프로세스가 처리한 상태입니다.

 

-- UPDATE STATEMENT
--        PX COORDINATOR
--            PX SEN QC (RANDOM)
--                UPDATE
--                    PX BLOCK INTERATOR
--                        TABLE ACCESS FULL OF 고객

 

반면 UPDATE/DELETE/INSERT 가 PX COORDINATOR 위쪽에 나타나면 QC가 처리한 상태입니다.

 

-- UPDATE STATEMENT
--        UPDATE
--            PX SEN QC (RANDOM)
--                    PX BLOCK INTERATOR
--                        TABLE ACCESS FULL OF 고객