문제는 수동이라는 점
예전에 필자가 Full Table Scan 의 비밀 이라는 글에서 _serial_direct_read 파라미터를 true 로 수정하여 Full Table Scan의 성능을 빠르게 한것을 기억하는가? 이것은 Full Table Scan시에 Disk 에서 데이터를 Scan 하여 SGA의 Buffer Cache에 적재한 후에 Logical Reads로 Fetch 를 하게되므로 성능이 저하될 수 있다. 이때 Disk 에서 Buffer Cache를 거치지 않고 바로 Fetch를 하면 성능향상이 가능한데 문제는 10g 까지는 항상 수동으로 파라미터를 수정하여야 한다는 것이다.

11g 부터 자동으로 바뀌어..
또한 테이블의 Size 가 중간크기인 경우 Direct I/O 를 하는것이 좋은지  Buffer Cache I/O 를 하는것이 좋은지 테스트를 해야만 한다. 하지만 Oracle 11g 부터 Direct I/O 를 수동으로 Control 할 필요가 없게 되었다. 11g 부터는 CBO(혹은 Cost Based 분석모듈)가 모든것을 알아서 한다. 이기능은 공식적으로 Cost Based Analysis of Direct I/O Access 라고 불린다.

2010 년 부터는 Oracle 11g 가 대세 라는데... 
세계적인 전문가들도 용어는 물론이며 이러한 기능의 허와 실을 모르고 있다. 전세계에서 손에 꼽는 사람들 까지도 ... 이번기회에 확실히 용어와 개념을 잡아서 선구자가 되길 바란다. 2010 년 부터는 Oracle 11g로 가는것이 대세가 될것이며 누구나 이기능을 사용하게 될것이기 때문이다.
그럼이제 11g 에서 Direct I/O가 어떻게 수행되는지 알아보자.

Oracle 11g 에서 Direct I/O 을 수행하기 위한 일반적인 SQL 의 수행과정은 아래와 같다.

1. SQL을 실행한다.

2. 옵티마이져가 DATA 를 FETCH 하기 위하여 최종 PLAN 을 만든다.

3. Direct I/O 의 후보인지 판별한다.
   예를 들면 full table scan 인가? , 테이블의 블럭사이즈가 _db_block_buffers 의 10% 보다 큰가? 등을 만족한다면 후보 이다.
   참고로 _small_table_threshold 파라미터는 아무상관도 없다. 값을 줄이거나 증가시켜서 테스트 해보기 바란다.

4. Direct I/O 의 후보라면 Cost Based 분석 모듈을 call 하여 Buffer Cache 를 엑세스 시의 Cost 와 Direct I/O 시의 Cost 를 비교하여 비용이 낮은것으로 선택한다. 4번의 로직은 중요하므로 아래의 Cost Based 분석 모듈에서 자세히 설명된다.

5. 4번의 비교에 의하여 Direct I/O를 한다고 판단되었다면 해당 테이블의 Commit 되지 않은 데이터가 Buffer Cache 에 있는경우 데이터를 Disk 로 내려쓴다.

6. 데이터를 Fetch 한다. 이때 Direct I/O 일 경우가 아니라면 Buffer Cache 에서 Fetch 하고 Cost Based 분석 모듈을 끝낸다.
   Direct I/O 일 경우 Disk 에서 Fetch 된다.  

7. Direct I/O 일 경우 4~6을 Fetch 단위마다 반복한다. Buffer Cache를 I/O 일 경우는 계속하여 Fetch만 하게된다.

이것이 전부이다. 7번 까지의 로직은 크게 어렵지 않은 것을 알수 있다. 다만 Direct I/O 를 수행할 경우 경우 잘못이해 하는 부분이 있는데 Disk 의 데이터를 PGA 로 내려쓰지 않는다는 것이다. _kdli_sio_pga 파라미터가 Default 로 False 이기 때문이다.

이제 4번에서 소개되었던 가장 중요한 Cost Based 분석 모듈을 소개할 차례다. 

이 모듈이 CBO 내의 모듈인지는 확실치 않다. 만약 아니라면 다양한 통계정보를 사용하여 Cost 를 구하는 최초의 모듈이 될것이다. 거의 CBO 의 기능에 준한다고 할수 있다. 과연 이것이 가능할까? 이점은 필자로서도 의문이다.
RAC의 경우 이모듈의 특징은 각 노드마다 모듈이 존재한다는 점이다. 각 노드마다 Cost 를 구해야 되기 때문이다.

401. SQL 이 Buffer 에 CACHING 하기 좋은 후보인지 알아본다.
     예를 들면 Buffer CACHE SIZE 가 충분한가? 자주 엑세스 되는가? 적어도 작은 부분이 CACHE 에 있는가?
     (자주 엑세스 되지만 CACHE 에 없을수 있다)

402. Buffer 에 CACHING 하기 좋은 후보라면 Cost Based 분석 모듈을 끝낸다. 이후로 Buffer Cache를 I/O 하게된다.

403. Direct I/O 가 NETWORK I/O 를 증가 시킨다면 Cost Based 분석 모듈을 끝낸다. 이후로 Buffer Cache를 I/O 하게된다.
     왜냐하면 Direct I/O를 해봐야 Fetch Call에 의해서 수행속도가 매우 저하되므로 Direct I/O를 할필요가 없는것이다.
     다행히 Insert~Select, 혹은 CTAS(Create Table as Select), Delete, Update 등은 NETWORK I/O 를 증가시키지
     않으므로 Direct I/O로 동작할 것이다. 이부분을 테스트 해보라. ROWNUM 을 계속 변경해서 테스트 하다보면 어느시점에
     Direct I/O 가 사라지게 된다. 필자가 Heurastic Rule 이 아닌 Cost Based 분석 모듈임을 확신하게 만든 대목이다.

404. Buffer Cache를 엑세스 하는 Cost가 Direct I/O 의 Cost + Commit 되지않은 데이터를 디스크에 쓰는 Cost 보다 큰지
      비교한다.

405. Buffer Cache 엑세스의 Cost가 크다면 Direct I/O 를 해야 하므로 Commit 되지않은 데이터를 디스크에 쓰고
      Cost Based 분석 모듈을 종료한다. 이때 Commit 된 데이터도 Buffer Cache에 남아 있을수 있는데 이 데이터 또한 Disk 로
      내려쓴다. Cost Based 분석 모듈이 체크포인트를 좋아하는 이유가 이것이다. 체크포인트를 만나면 디스크로 내려쓰므로
      Direct I/O 의 Cost 가 줄어들기 때문이다. 참고로 Log File Switch 가 발생하면 같은 효과를 볼수 없다.
      필자의 기억으로 (9i 혹은 8i 마지막 버젼 부터) Log File Switch 발생시 체크포인트가 발생하지 않을수 있기 떄문이다.

406. Commit 되지않은 데이터를 디스크에 쓰는 작업이 끝났으면 Cost Based 분석 모듈을 종료한다.

407. 만약 404번에서 Buffer Cache를 엑세스 하는 Cost가 비용이 적게든다면 이번에는 Direct I/O 의 Cost와 비교한다.

408. 407번에서 Buffer Cache를 엑세스 하는 Cost가 크다면 Direct I/O 의 후보 FLAG 를 Y 로 한다.
     이작업은 Cost Based 분석 모듈이 종료된 이후에 5~7번에서 FLAG 값을 이용하기 위함이다.

409. 408번에서 Direct I/O 의 후보 FLAG 를 Y 로 했다면 Buffer Cache를 엑세스 하기위한 준비를 하고 분석을 끝낸다.
     이경우는 Direct I/O 가 좋은지 Buffer Cache I/O 가 좋은지 확실히 알수 없기 때문에 일단 Buffer Cache I/O를 하고 다음번
     Fetch 시에 재평가 하게된다.

410. 407번에서 Buffer Cache를 엑세스 하는 Cost가 비용이 적게든다면 Buffer Cache를 엑세스 하기 위한 준비를 하고 분석을
       끝낸다.

Cost Based 분석 모듈의 모든기능을 설명하였다. 문제는 이렇게 구해진 Cost 가 Plan 상이나 10053 Event 의 Trace 상에 반영되지 않는다는 점이다. Plan 상에서는 Direct I/O를 로 수행되건 Buffer Cache I/O 로 수행되건 간에 Cost의 변화가 전혀 없는데 이것은 매우 아쉬운 부분이다.

Cost 가 반영되지 않을것 이라면 따로 보여주던지...
Cost 는 수행시간에 비례하는 개념인데 엑세스 방식이 바뀜에 따라 수행시간이 바뀜에도 불구하고 Cost 는 아무런 변화가 없는 것이다. 그럴것이라면 위에서 구한 Cost 라도 따로 보여 주었으면 하는것이 필자의 바램이다. 그렇지 않다면 Cost 와 현실과의 괴리감이 11g 에서 증폭될 것이기 때문이다. Plan 상이나 10053 Event 의 Trace 상에 변화가 나타나지 않으므로 Direct I/O를 확인 할수 있는 제일 위운 방법은 10046 이벤트 Trace에서 Direct Path Read 이벤트를 확인 하는것이다.
 
이제 Cost를 구하기 위해 참조되는 통계정보는 어떤것이 있는지 알아보자.

아래와 같이 CBO 혹은 CBAM(Cost Based Analysis Module)은 여러가지 통계정보들을 사용한다.

-디스크에 미쳐 쓰지못한 BUFFER CACHE 에 존재하는 데이터의 블럭수
-반드시 엑세스 해야하는 데이터의 BLOCK 수
-해당 테이블의 데이터가 Buffer Cache에 존재하는 데이터의 BLOCK 수
-RAC 의 경우 분산된 INSTANCE 의 DISK 블럭수와 CACHE 의 블럭수
-Direct I/O 시 하나의 블럭을 읽는 속도
-Buffer Cache I/O 시 하나의 블럭을 읽는 속도
-RAC 의 경우 다른 인스탄스의 CACHE 된 BLOCK 과 DISK 에 있는 BLOCK 을 가져오는 속도

지면의 한계상 Cost 를 구하는 공식(Formular)은 생략한다. 그것은 다음 기회에...
지면이 길어지면 도망가는 독자를 많이 보아 왔다.^^    

결론 :
우리는 이제 Oracle11g 의 새기능인 Cost Based Analysis of Direct I/O Access 에 대하여 알아보았으며 Cost Based 분석모듈이 어떻게 동작하는지도 알게 되었다. 한가지더 첨언 하자면 위의 기능이 끝이 아니다 라는 사실을 강조 하고 싶다.

11g 부터 Parallel 힌트를 사용한 Select 문이 Serial 하게 수행될수있다. 기준은 테이블의 블럭수가 _small_table_threshold
파라미터 보다 작아야 한다. 필자는 이기능을 더 좋아한다. 왜냐하면 덩치가 작은 테이블은 사실상 Parallel Query 가 필요없으며 오히려 수행속도가 저하되는 경우를 많이 보아왔기 떄문이다.

기억하자!
11g 부터는 Parallel SQL이 Serial로 수행될수 있으며 Parallel 을 사용하지 않는 Full Table Scan 이 Direct Path Read 가 가능하다는 사실을..

P.S
작년 봄부터 이글을 쓸지 고민하다가 이제서야 올리게 되었다. 위의 글이 난위도가 있기 때문에 쉽게 쓰려고 고민 한 것이다. 어려운 개념이지만 단 한명이라도 이글을 보고 이해한다면 필자의 기쁨이 될것이다.

아래의 link 는 유명한 오라클 Guru 인 Doug Burns의 블로그인데 몇년째 위에서 설명한 사실들을 몰라서 고생하고 있다.
누가 대신 설명해주길 바란다.(필자는 영어가 짧아서....)

http://oracledoug.com/serendipity/index.php?/archives/1321-11g-and-direct-path-reads.html
http://oracledoug.com/serendipity/index.php?/archives/1320-Parallel-Query-and-11g-Part-2.html

Posted by extremedb

댓글을 달아 주세요

  1. 김시연 2009.07.22 14:24  댓글주소  수정/삭제  댓글쓰기

    새로운내용 잘 보고갑니다. 'Cost Based Analysis of Direct I/O Access "로 구글을 검색해보니 해당 내용이 특허로 등록이 되어있네요. 특허자료까지 공부를 하시다니...:)
    그럼 내년에 나올책 기대하고 있겠습니다.

  2. Favicon of https://ukja.tistory.com BlogIcon 욱짜 2009.07.22 22:53 신고  댓글주소  수정/삭제  댓글쓰기

    위의 내용을 보고 2가지 정도 추가로 논의되었으면 하는게 있습니다.

    1. 일부 경험적인 테스트에 의하면 _small_table_threshold * 5 의 Segment 크기가 Direct I/O의 후보를 결정하는 기준이 되는 것 같습니다.

    2. Direct I/O를 수행하고자 할 때 Object Checkpoint가 일어나는 것은 마치 Parallel Execution에 의해 Direct I/O가 발생할 때 Object Checkpoint가 일어나는 것과 동일한 원리입니다. 속도를 위한 것이 아니라 읽기 일관성 때문입니다. 두 경우 모두 Direct I/O가 Data File에 대해 발생하기 때문에 Data File에 적어도 Query가 시작하는 시점의 이미지가 존재해야 합니다. 따라서 Buffer Cache에 존재하는 블록들 중 아직 Checkpoint가 되지 않은 것들을 Data File에 내려쓰게 됩니다. Oracle 10gR2이전까지는 이 Object Checkpoint의 성능이 느린 경우가 많이 보고되었는데, 10gR2이후에는 알고리즘이 크게 개선되었다고 알려져 있습니다.

    특히 _small_table_threshold가 정확하게 어떻게 기준이 되는지 조사와 검증이 필요할 거 같네요.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.07.23 01:30 신고  댓글주소  수정/삭제

      방문에 감사드립니다.
      두가지 논의사항에 대한 답변을 드리겟습니다.
      첫번째로 _small_table_threshold 파라미터는 제가 테스트를 진행 할때 Direct I/O 에 아무런 영향을 끼치지 못햇습니다. 동욱님이 _small_table_threshold * 5 가 기준이 된다고 하시니 이부분은 추가로 다양한 테스트를 진행 해 보겠습니다.

      두번째로 제가 체크포인트를 읽기 일관성 관점이 아니라 Cost 관점에서 말씀드린 이유 입니다.
      Buffer Cache I/O 를 할것이냐 아니면 Direct I/O 를 할것이냐 의 갈림길에서 CBAM 이 잘못판단하여 Buffer Cache I/O 로 결정 할수 있습니다. 이때 강제로(수동으로) 체크포인트를 발생시키면 Cost 가 낮아지므로 Direct I/O 를 선택할 확률이 무척 높게 됩니다. 특히 대량 UPDATE + COMMIT 후에 체크포인트를 발생시켰더니 100% DIRECT I/O 를 선택 하였습니다. SQL 수행전에 수동 체크포인트를 발생시키는것은 COST 를 낮추는 일종의 트릭이라고 할수 있습니다.

      동욱님이 이야기 하신부분은 DIRECT I/O 로 가기로 결정이 되고 그후의 읽기 일관성을 이야기 하신것 같습니다. 제가 말씀드린것은 그이전의 COST 를 이야기 한것입니다. 글의 내용이 애매하여 혼란을 일으켰나요? 그렇다면 글을 좀더 다듬어야 할거 같습니다.

      10g R2 이후에 Object CheckPoint의 알고리즘이 개선되었다니 다행입니다. 좋은 정보 감사합니다.

  3. Favicon of https://ukja.tistory.com BlogIcon 욱짜 2009.07.23 08:29 신고  댓글주소  수정/삭제  댓글쓰기

    해석상의 문제가 조금 있었네요. ^^

    Direct I/O의 Cost에 Checkpoint의 Cost가 고려된다는 것으로 이해하면 정확하겠죠?

    _small_table_threshold의 역할에 대해서는 공식적인 자료는 존재하지 않는 것 같고, 몇 가지 테스트를 통해 제안된 값으로 보입니다.

    http://www.freelists.org/post/oracle-l/different-physical-access-method-because-of-disabling-Automated-Memory-Management,2

    이 의견에 대해서는 더 엄밀한 검증이 필요해보입니다.

  4. Ejqj 2013.06.25 16:31  댓글주소  수정/삭제  댓글쓰기

    사이트에 문제가 있어서 찾다찾다 여기까지 왔습니다.
    그런데 예전에 많이 들렀던 곳이군요.

    TABLE FULL SCAN할 때와 FULL PARALLEL 힌트를 줄때 생기는 이벤트가 조금 이상하여 문의드립니다.

    <버전11g 테스트시>

    sQL형태는

    select /*+ full(a) parallel(a 4) */
    ~~
    from tab a
    where 조건들
    ;

    수행시 db file scattered read발생 -> 성능저하

    <10g 테스트>

    수행시 direct path read 발생 -> 성능양호

    해당 테이블 사이즈는 70GB정도 되며, 파티션으로 구성되어있습니다. 단일 FULL 스캔시에는 direct path read발생하나 parallel로 안하면 이 또한 성능이 나빠서 paralllel 힌트를 줘야합니다. parallel 힌트까지 주면 다시 db file scattered read로 parallel process로 수행이 됩니다.

    관련 파라미터를 찾아봐도 잘 안나와 있어서 여기서 힌트좀 얻어 갈 수 있을까해서 문의드립니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2013.06.25 21:32 신고  댓글주소  수정/삭제

      Ejqj 님 오랜만 입니다.
      1.먼저 통계정보에 이상이 없는지 확인해 보세요. %를 좀 더 크게 주어 통계정보를 재생성 하는 것도 방법이 될 수 있습니다.
      2.해당 SQL로 보아 하루에 몇번 실행되지 않는 것 같습니다. 만약 그렇다면 변수 대신에 상수를 사용하시기 바랍니다. 이렇게 하면 더 정확한 카디날리티와 COST가 계산됩니다.
      3.혹시 INSERT ~ SELECT나 CTAS라면 전체문장으로 TRACE를 거시기 바랍니다. SELECT 만 실행하면 FETCH 부하 때문에 BUFFER CACHE를 경유할 가능성이 큽니다.
      4.쿼리를 수행시키기 직전에 수동으로 체크포인트를 발생시켜 보시기 바랍니다.
      5.NOCACHE 힌트를 사용해보세요. (이 힌트로 되는지 아직 테스트 해보지 않았습니다.)
      감사합니다.