FULL SCAN 방식은 알다시피 다중 블록단위 I/O를 수행한다. ‘db_file_multiblock_read_count’ 파라미터에 설정된 값만큼 EXTENT의 블록들을 한번의 I/O Call에 읽어 들임으로써 인덱스를 활용한 단일 블록단위 I/O보다 적은 I/O Call을 발생시킨다.

그래서 대량의 블록을 읽어야 할 경우에는 단연 FULL SCAN 방식이 유리하다. 예를 들어 살펴보면 TABLE FULL SCAN 방식은 첫 번째 EXTENT를 시작으로 HWM(High Water Mark) 표기된 지점까지 읽기를 수행하는데, [그림 1]과 같이 EXTENT가 20 Block이고 ‘db_file_multiblock_read _count’ 파라미터가 8로 설정되어 있다면 총 7번의 I/O Call 이 발생하게 된다.

여기서 특이사항은 해당 파라미터값이 아무리 높게 설정되어 있어도 EXTENT의 단위를 넘어서도록 읽을 수는 없다는 것이다.

• [그림 1] EXTENTS가 20 Block이고 'db_file_multiblock_read_count' 파라미터가 8로 설정된 예
• 

추가로 오라클 데이터 버퍼캐시는 디스크 I/O를 줄이기 위해 최근에 사용했던 블록에 우선순위를 두는 LRU(Least Recently Used) 알고리즘을 사용한다. 하지만 본래 LRU 알고리즘의 사상대로라면 FULL SCAN 방식으로 읽힌 대량의 블록들은 LRU 리스트를 점령해 기존에 관리되던 블록들을 밀어내게 된다.

이럴 경우 데이터 버퍼캐시의 적중률이 현저하게 낮아지는데 이것을 막기 위해 오라클은 TABLE FULL SCAN의 경우에는 LRU 리스트의 우선순위가 낮은 쪽의 블록들을 할당해주고 반복 재활용하게 함으로써 TABLE FULL SCAN으로 인한 악영향을 최소화한다.

병렬 스캔으로 응답속도 줄이기

대용량 테이블의 경우에 단일 프로세스에 의한 TABLE FULL SCAN 방식은 원하는 응답속도를 얻기 힘들다. 그래서 오라클은 병렬 스캔 방식을 지원하고 있으며 데이터베이스, 오브젝트, SQL 레벨별로 병렬도를 지정하고 원하는 만큼의 자원을 배정해 병렬 스캔을 수행할 수 있다.

[리스트 1]의 예제와 같이 PARALLEL 힌트를 병렬도 4로 사용할 경우 해당 테이블을 ROWID 기준으로 작업량을 나누어 4개의 프로세스가 병렬 읽기 작업을 수행한다. 전체 테이블 세그먼트를 1/4로 나누는 것은 아니며 본인의 작업량을 모두 마치면 새로이 작업량을 할당 받아서 처리하는 방식으로 진행된다.

• [리스트 1] PARALLEL 힌트를 병렬도 4로 사용

• 세션1> SELECT /*+ PARALLEL(B 4) */ * FROM SOURCE B;
  세션2> SELECT SID, SERIAL#, OPNAME, TARGET, START_TIME, SOFAR, TOTALWORK,
               SOFAR/TOTALWORK*100 RATIO 
          FROM V$SESSION_LONGOPS;
  
  
  SID   SERIAL# OPNAME           TARGET   START_TIME      SOFAR  TOTALWORK RATIO
  ----- ------  ----------------- ------  --------------- -----  --------- -----
  4522  63785   Rowid Range Scan  SOURCE  2012-07-24 9:40 10242  10242       100
  5492  31501   Rowid Range Scan  SOURCE  2012-07-24 9:41 10242  10242       100
  2685  27487   Rowid Range Scan  SOURCE  2012-07-24 9:42 10242  10242       100
  7836  25723   Rowid Range Scan  SOURCE  2012-07-24 9:43  5958  10243      58.16
  4522  63785   Rowid Range Scan  SOURCE  2012-07-24 9:43  5932  10242      57.91
  5492  31501   Rowid Range Scan  SOURCE  2012-07-24 9:43  6251  11197      55.82
  

단순 조회 SQL 수행에서 대기 이벤트 다량 발생

SELECT SQL을 수행할 때 ‘latch: row cache objects’ 이벤트가 다량으로 발생하면서 조회 속도가 매우 느려지는 상황이 발생할 수 있다. 이것은 오라클이 Delayed Block Cleanout 방식을 사용하기 때문에 발생하는 현상이다.

오라클은 DML 수행 후에 commit되면 관련된 블록의 락 해제 및 ITL 정보를 갱신하는 작업을 진행한다. 이 작업을 Block Cleanout이라고 한다.

하지만 대량의 DML 작업 후에 이 작업을 수행할 경우 엄청난 부하가 발생할 수 있기 때문에 오라클은 Commit 시에 블록에 대한 변경 작업을 미루고 차후에 해당 블록을 디스크에서 버퍼캐시로 적재하는 시점에 변경하는 방식(Delayed Block Cleanout)을 사용하고 있다.

이 작업을 수행하기 위해 dc_ rollback_segments 영역을 빈번하게 참조하게 되는데, 이때 발생하는 이벤트가 ‘latch: row cache objects’이다.

이러한 원리 때문에 온라인 서비스 시스템에서 간혹 업무시간 동안 발생한 DML에 대한 블록 클린아웃이 새벽 배치작업 시간에 수행되어 배치작업이 제시간에 끝나지 않는 상황이 발생한다.

이 문제를 해결하기 위해서는 다음 예제와 같이 관련 테이블에 대해 TABLE FULL SCAN을 수행해 미리 블록 클린아웃을 수행하는 것이 좋다.

SELECT /*+ FULL(A) */ * FROM TARGET_TABLE A; 

힌트가 없어도 작동하는 Direct Path Read

11g부터는 이와 같은 Serial TABLE FULL SCAN도 특정 기준에 부합하면 버퍼캐시를 통하지 않는 Direct Path Read 방식을 자동으로 사용한다. 만약 이 기능이 자동으로 작동된다면 위의 SQL은 해결방법이 될 수 없다.

블록 클린아웃을 수행하기 위해서는 버퍼캐시를 통한 Conventional Path Read 방식을 반드시 사용해야 하기 때문이다. 오라클 11g부터는 다음의 우선순위로 Serial Direct Path Read 여부를 결정한다.

'_serial_direct_path_read' 파라미터 설정 값

• - ALWAYS : 항상 Serial Direct Path 수행
• - NEVER : Serial Direct Path 수행하지 않음
• - AUTO(Default) : 조건에 따라 Conventional Path Read와 Direct Path Read를 혼용

_serial_direct_read=AUTO인 경우의 수행 조건

1) Table의 크기에 따라 SMALL, MEDIUM, LARGE, VERY LARGE Table로 구분해 각 조건별로 수행

• [SMALL Table]
• ① 테이블 Block 수가 _small_table_threshold보다 작은 경우 해당
• ② '_small_table_threshold' 미설정 시 '_db_block_buffers'의 2% 또는 20 Block 중 큰 것을 택함
• ③ Conventional Path Read 수행

• [MEDIUM Table]
• ① 테이블 Block 수가 _small_table_threshold보다 크고 _db_block _buffers의 10% 이하인 경우 해당
• ② Object Stats.이 존재하지 않을 경우 Conventional Path Read 수행
• ③ Object Stats.이 존재할 경우 비용 분석 후 읽기 방식 결정

• [LARGE Table]
• ① MEDIUM Table보다 크고 VERY LARGE Table보다 작을 경우
• ② Conventional Path Read와 Direct Path Read 간의 비용 분석 후 결정

• [VERY LARGE Table]
• ① Block 수가 _db_block_buffers * _very_large_object_threshold / 100보다 큰 경우
• ② 무조건 Direct Patch Read 수행

2) Event 10949 설정 여부

• - MEDIUM, LARGE Table의 경우 Event 10949로 Direct Path Read를 DISABLE 할 수 있음
• - VERY LARGE Table일 경우 Event 10949에 상관없이 무조건 Direct Patch Read 수행

결론적으로 Serial Direct Path Read 기능을 활용하면서 원하는 대로 컨트롤하려면 '_serial_direct_path_read= AUTO'로 설정하고 세션별로 기능을 사용하고 싶지 않은 경우에는 '_small_table_threshold' 값을 크게 설정하고 10949 이벤트를 사용한다.