Direct Path I/O활용

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

1. Direct Path I/O

• 일반적인 블록 I/O는 DB 버퍼캐시를 경유
• 읽고자하는 블록을 버퍼캐시에서 찾아보고 찾지못할때만 디스크에서 읽는다.
• 데이터 변경시에도 먼저 블록을 버퍼캐시에서 찾는다, 찾은 버퍼블록에 변경을 하고나면, DBWR프로세스가 변경된 블록(Dirty 블록) 들을 주기적으로 찾아 데이터파일에 반영해 준다.
• 자주 읽는 블록에 대한 반복적인 I/O Call을 줄임으로써 시스템 전반적인 성능을 높이려고 버퍼캐시를 이용하지만, 대량 데이터를 읽고 쓸때 건건이 버퍼캐시를 탐색한다면 개별 프로그램 성능에는 오히려 안 좋다.
  버퍼캐시에서 블록을 찾을 가능성이 거의 없기 때문이다.
• 대량 블록 재사용할 가능성이 있느냐도 중요한데, Full Scan위주로 가끔 수행되는 대용량 처리 프로그램이 읽어들인 데이터는 대개 재사용성이 낮다.
• 오라클 Direct Path I/O가 작동하는 경우
  • 병렬 쿼리로 Full Scan을 수행할 때
    • parallel, parallel_index힌트사용
    • /*+ full(t) parallel(t 4)*/ 와같이 병렬도 4로지정하면 네베빨라지는게 아니라
    • 수십배빨라진다 Direct Path I/O 때문
    • 버퍼캐시 탐색안함, 디스크로부터 퍼버캐시 적재부담도없으니 빠름
  • 병렬 DML을 수행할 때
  • Direct Path Insert를 수행할 때
  • Temp 세그먼트 블록들을 읽고 쓸 때
  • Direct 옵션을 지정하고 Export를 수행할때
  • Nocache옵션을 지정한 LOB컬럼을 읽을때

2. Direct Path Insert

• 일반적으로 Insert가 느린 이유
  • 데이터 입력할 수 있는 블록을 Freelist에서 찾는다. 테이블 HWM(High-Water-Mark)아래쪽에 있는 블록 중 데이터 입력이 가능한(여유 공간이 있는)블록을 목록으로 관리하는데 이를 'Freelist'라고한다
  • Freelist에서 할당받은 블록을 버퍼캐시에서 찾는다.
  • 버퍼캐시에 없으면 데이터파일에서 읽어 버퍼캐시에 적재
  • INSERT내용을 Undo 세그먼트에 기록
  • INSERT내용을 Redo 로그에 기록
• Direct Path Insert방식은 일반적인 대용량 Insert보다 훨씬빠르다
  Direct Path Inser방식
  • INSERT ... SELECT 문에 append힌트 사용
  • parallel 힌트를 이용해 병렬모드로 INSERT
  • direct옵션을 지정하고 SQL*Loader(sqldr)로 데이터 적재
  • CTAS(create table ... as select)문 수행
• Direct Path Insert방식이 빠른 이유
  • Freelist를 참조하지않고 HWM바깥영역에서 데이터를 순차적으로 입력
  • 블록을 버퍼캐시에서 탐색하지 않는다.
  • 버퍼캐시에 적재하지않고, 데이터파일에 직접기록
  • Undo로깅을 안한다
  • Redo로깅을 안 하게 할 수 있다. 테이블을 nologging모드로 전환한상태에서 Direct Path Insert하면된다

    alter table t NOLOGGING;

• Array Processing도 Direct Path Insert방식으로 처리할 수 있다(append_values힌트 사용)
• Direct Path Insert사용시 주의점
  • 첫째, 성능은 매우빨라지지만 Exclusive모드 TM Lock이 걸린다.>커밋하기전까지 다른 트랜잭션은 해당테이블에 DML을 수행하지 못한다. 트랜잭션이 빈번한 주간에 이 옵션을 사용하는것은 절대 금물
  • 둘째, Freelist를 조회하지 않고 HWM바깥 영역에 입력하므로 테이블에 여유공간이 있어도 재활용하지 않는다. 과거데이터를 주기적으로 DELETE해서 여유공간이 생겨도 이방식으로만 계속 INSERT하는 테이블은 사이즈가 줄지않고 계속 늘어만간다.

  3. 병렬 DML

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

  alter session enable parallel dml;

• 그리고 각 DML문에 parallel힌트 사용시 대상레코드를 찾는작업은 물론 데이터 추가/변경/삭제도 병렬로 진행
• 병렬 INSERT는 append힌트지정하지않아도 Direct Path Insert방식을 사용

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

• 실행계획에서 UPDATE/DELETE/INSERT가 'PX COORDINATOR'아래쪽에 나타나면 각 병렬 프로세스가 처리
• PX COORDINATOR위에서 나타나면 QC가 처리
• QC는 Query Coordinator의 줄임말, SQL을 병렬로 실행하면 병렬도로 지정한 만큼 또는 두배 병렬 프로세스를 띄워 동시에 작업을 진행하는데, 이때 최초 DB에 접속해서 SQL을 수행한 프로세스는 Query Coordinator역할을 맡는다. 단, 병렬로 처리할 수 없거나 병렬로 처리하도록 지정하지않은 작업은 Query Coordinator가 직접 처리한다.