Oracle11g 에서 Full Table Scan의 성능 향상

문제는 수동이라는 점
예전에 필자가 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