5._Direct_Path_IO

조회 수 6348 추천 수 0 2012.05.22 17:50:28
남송휘 *.73.93.2

  • 일반적 블록 I/O DB 버퍼 캐시 경유
    • 읽고자 하는 블록을 버퍼 캐쉬 확인후 없을경우 디스크 에서 읽음
    • 변경역시 버퍼 캐시에서 이루어지며 dbwr 프로세스가 dirty 블록을 기록
    • 시스템 전반의 I/O 성능 향상
    • 개별 프로세스입장에서는 좋지 않음


  • Direct Path I/O
    • 버퍼 캐시를 경유하지 않고 데이터 블록을 읽고 쓰는 기능
    • Temp 세그먼트 블록들을 읽고 쓸때
    • 병렬 쿼리로 Full Scan을 수행할때
    • nocache 옵션을 지정한 LOB 컬럼을 읽을때
    • direct 옵션을 지정하고 export 를 수행할때
    • parallel DML 을 수행할때
    • Direct Path Insert 를 수행할때




(1) Direct Path Read/Write Temp


  • 데이터 정렬시 PGA메모리의 Sort Area를 이용
  • 정렬 데이터가 많을경우 Temp 테이블 스페이스를 사용
  • Sort Area에 정렬된 데이터를 Temp 테이블스페이스에 쓰고 읽을때 Direct Path I/O 방식사용



Session altered.

SQL> create table ta as select * from all_objects;

Table created.

SQL> alter session set workarea_size_policy=manual;

Session altered.

SQL> alter session set sort_area_size=5224288;

Session altered.

SQL> select *
  2  from (
  3     select a.*, rownum no
  4     from (select * from ta order by object_name) a
  5  )
  6  where no <=10;



call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0        110          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.30       0.51       1060       1037          3          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.31       0.51       1060       1147          3          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  VIEW  (cr=1037 pr=1060 pw=1060 time=0 us cost=2615 size=9571554 card=55974)
  72407   COUNT  (cr=1037 pr=1060 pw=1060 time=330632 us)
  72407    VIEW  (cr=1037 pr=1060 pw=1060 time=221959 us cost=2615 size=8843892 card=55974)
  72407     SORT ORDER BY (cr=1037 pr=1060 pw=1060 time=118412 us cost=2615 size=8843892 card=55974)
  72407      TABLE ACCESS FULL TA (cr=1037 pr=0 pw=0 time=81744 us cost=394 size=8843892 card=55974)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  direct path write temp                         42        0.02          0.22
  direct path read temp                         845        0.00          0.01
  SQL*Net message from client                     1        0.00          0.00




(2) Direct Path Read

  • 병렬 쿼리로 Full Scan 수행시 Direct Path Read
  • 병렬도 (DOP)를 2 -> 병렬쿼리 수행 ->수행속도 2배이상
  • 읽기 Call 이 완료될때 까지 대기가 발생 -> direct path read 이벤트 로 측정
  • Direct Path Read를 수행하려면 메모리와 디스크간 동기화를 먼저 수행 -> Dirty 버퍼 해소
  • 10gR2이전 -> 병렬쿼리 수행시 체크포인트를 통해 버퍼 캐시 전체를 데이터파일에 기록
  • 10gR2이후 -> 병렬쿼리와 관련된 세그먼트만 동기화 수행
  • Adaptive Direct Path Reads
    • 오라클 8.1.5 이후 -> 병렬쿼리가 아니라도 Direct Path Read 가능
      • _serial_direct_read true : 변경
    • 11g 부터 이파라미터가 false인 상태에서 Serial Direct Path Read 방식이 작동 할수도 있음
      • 이미 캐싱돼 있는 블록 개수, 디스크에 기록해야 할 Dirty 블록 개수
        (-> 세그먼트 단위 체크포인트 일량을 결정) 등


(3) Direct Path Write


  • 병렬로 DML을 수행하거나 Direct Path Insert 방식으로 데이터를 insert할때 사용
    • I/O Call 발생시마다 direct path write 이벤트
  • Direct Path Insert 방식으로 데이터 입력하는 방법
    • insert … select 문장에 /*+append*/ 힌트사용
    • 병렬 모드로 insert
    • direct 옵션을 지정하고 SQL*Loader(sqlldr)로 데이터를 로드
    • CTAS (create table … as select )문장을 수행


  • 일반적인 insert시
    • Freelist를 통해 데이터를 삽입할 블록을 할당
    • Freelist 조회하면서 Random 액세스 방식으로 버퍼 캐시에서 해당 블록 찾음
    • 없다면 데이터파일에서 읽어 캐시에 적재
    • 데이터 삽입
    •  대량의 데이터 insert 시 매우 느림


  • Direct Path Insert 시
    • Freelist를 참조하지 않고 테이블 세그먼트 또는
      각 파티션 세그먼트의 HWM (High-Water Mark) 바깥 영역에 데이터를 순차적으로 입력
    • Freelist로 부터 블록 할당 작업 생략, insert 할 블록을 버퍼 캐시에 적재하지 않음
    • 직업 insert로 인해 속도가 빠름


  • HWM 바깥 영역에 데이터 입력 Undo 발생량도 최소화
  • HWM 뒤쪽에 입력한 데이터는 커밋전가지 다른 세션에 읽히지 않음
    (undo 필요없음,롤백시 딕셔너리 정보만 롤백하면 됨)
  • Redo 로그까지 최소화 (데이터딕셔너리 변경사항만 로깅) 하도록 옵션을 줄수 있음
    -> 더 빠른 isnert 가능 -> nologging
alter table t NOLOGGING;
insrt into t NOLOGGING select * from test; <- X 아무 의미 없음


  • insert 문을 로깅하지 않도록 하는 방법 없음
  • Direct Path Insert 방식으로 데이터를 입력하면 Exclusive 모드 테이블 Lock이 발생


alter session enable parallel dml;
delete /*+ parallel(b 4) */ from big_table b;  -> Exclusive 모드 TM Lock!!


  • Exclusive 모드 테이블 Lock을 사용하면 해당 테이블에 다른 트랜잭션의 DML 수행을 막음
    (일반 DML은 Row Exclusive 모드 테이블 LOCK 사용 다른 트랜잭션의 DML 막지 않음)





  • 오라클 고도화 원리와 해법 1 (bysql.net 2012년 1차 스터디)
  • 작성자:  남송휘
  • 최초작성일: 2012년 5 월 20 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^