OLTP는 기준성데이터, 특정 고객, 특정 상품, 최근 거래 등을 반복적으로 읽어 버퍼캐시가 성능 향상에 도움을 준다.
DW/OLAP 이나 배치 프로그램에서 사용하는 SQL은 주로 대량 데이터를 처리하기 때문에 버퍼캐시를 경유하는 I/O 매커니즘이 오히려 성능을 떨어뜨릴 수 있다.
오라클은 버퍼캐시 경유하지 않고 곧바로 데이터 블록 읽고 쓸 수 있는 Direct Path I/O 기능을 제공한다.
Direct Path I/O
대량의 데이터를 읽고 쓰는데 버퍼캐시를 탐색하면 더 안좋은 이유
- 버퍼캐시에서 블록을 찾을 가능성이 거의 없음.
- 대량의 블록을 건건이 디스크로부터 버퍼캐시에 적재하고 읽어야 하는 부담도 크다.
- 버퍼캐시에 적재해도 재사용 할 가능성이 거의 없다.
- 재사용성 적은 데이터가 버퍼캐시 점유하면 다른 프로그램에 성능에도 안좋다.
Direct Path I/O가 작동하는 경우
- 병렬 쿼리로 Full Scan을 수행 할 때
- 병렬 DML을 수행할 때
- Direct Path Insert를 수행할 때
- Temp 세그먼트 블록들을 읽고 쓸 때
- Direct 옵션을 지정하고 export를 수행할 때
- Nocache 옵션을 지정한 LOB 컬럼을 읽을 때
Direct Path Insert
INSERT가 느린 이유
- 데이터를 입력할 수 있는 블록을 Freelist에서 찾는다. 테이블 HWM(High water mark) 아래쪽에 있는 블록 중 데이터 입력이 가능한(여유공간이 있는)블록을 목록으로 관리하는데 이를 ‘Freelist’ 라고 한다.
- Freelist에서 할당받은 블록을 버퍼캐시에서 찾는다
- 버퍼캐시에 없으면 데이터파일에서 읽어 버퍼캐시에 적재
- insert 내용을 Undo 세그먼트에 기록
- 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 방식이 빠른 이유
- Freelist를 참조하지 않고 HWM 바깥 영역에서 데이터 순차 입력함.
- 블록을 버퍼캐시에서 탐색하지 않음
- 버퍼캐시에 적재하지 않고, 데이터파일에 직접기록
- Undo 로깅 안함
- Redo 로깅을 안하게 할 수 있다.
방법 :alter table t NOLOGGING;
Array Processing 도 Direct Path Insert 방식으로 처리가능함. append_values 힌트 사용하면 된다.
Direct Path Insert 사용하면 주의점 두가지
- 이 방식은 성능이 빨라지지만 Exclusive 모드 TM Lock이 걸린다.
그래서 커밋하기 전까지 다른 트랜잭션은 해당 테이블에 DML을 수행할 수 없다. - Freelist를 조회하지 않고 HWM 바깥영역에 입력하므로 테이블 여유 공간이 있어도 재활용하지 않는다.
- 과거 데이터를 주기적으로 DELETE해서 여유공간이 생겨도 이 방식으로만 INSERT 하는 테이블은 사이즈가 계속 늘어난다.
- DROP해야 공간 반환이 제대로 이루어짐.
- 비 파티션 테이블이면 주기적으로 Reorg 작업 수행
병렬 DML
update, delete는 기본적으로 Direct Path Write가 불가능함.
방법은 DML로 처리해야 한다.
병렬처리는 대용량 데이터가 전제이므로 오라클은 병렬 DML에 항상 Direct Path Write 방식을 사용해야 한다.
DML을 병렬로 처리하면 병렬 DML을 활성화 해야함.
alter session enable parallel dml;각 DML 문에 parallel 또는 full parallel 힌트 사용.
그러면 대상 레코드 찾는 작업은 물론 데이터 추가,변경,삭제도 병렬로 진행한다
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 활성화 안하면 찾는 작업은 병렬로 하나, 추가/변경/삭제는 QC가 혼자 담당해서 병목생긴다.
병렬 insert는 append 힌트 지정 안해도 direct path insert 방식을 쓴다.
그러나 혹시나 대비하여 append 힌트를 쓰는게 좋다.
12C부터는 enable_parallel_dml 힌트도 지원한다.
병렬 DML이 잘 작동하는지 확인하는 방법
DML 작업을 각 병렬 프로세스가 잘 처리하는지 QC가 처리하는지는 실행계획에서 확인 가능.
