1. db file sequential read 대기이벤트

1. 싱글 블록 IO를 수행할때마다 한번의 db file sequential read 대기이벤트가 발생한다.
2. 싱글 블록 IO는 인덱스 스캔, ROWID에 의한 테이블 스캔, 컨트롤 파일 및 파일 헤더를 읽을 때 발생한다.
3. P1=file#, P2=block#(이 값이 1이면 파일 헤더 블록을 읽어다는 의미), P3=blocks
4. 비효율적인 인덱스 스캔이나 Chained Row, Migraed Row에 의해 추가적인 IO가 발생하는 경우 성능상 문제가 생긴다.

2. db file sequential read 대기 해결책

1. 어플리케이션 레이어

• SQL튜닝
  • 선택도가 좋지 않은 인덱스를 스캔하는 SQL을 확인한다.
  • 인덱스 종류별 작동원리를 이해한 후 인덱스를 생성한다.
• 최신 통계 정보 유지
• 바인드 피킹 기능에 의해 잘못된 실행계획을 세우게 되는 경우는 _OPTIM_PEEK_USER_BINDS 히든 파라메터를 FALSE로 변경하여 해당 기능을 사용하지 않게 한다.

2. 오라클 메모리 레이어

• 버퍼 캐시의 크기를 적절히 조정 : 크기가 작은 경우 db file sequential read 대기와 free buffer waits 대기가 함께 나타날 확률이 높다.
• 다중 버퍼 풀 사용
• 클러스터링 팩터를 높지 않게 처리
  • 클러스터링 팩터(이하 CF)는 인덱스의 테이블에 대한 군집도(Clustering factor)를 의미한다. CF는 메모리에 단 하나의 블록만을 담을 수 있는 공간이 있다고 가정하고, 인덱스 스캔에 따라 테이블을 얼마나 스캔해야 하는지를 계산한 값
    이다.

1) CF가 가장 낮은 경우: 하나의 인덱스 블록에 포함된 4개의 ROWID가 하나의 테이블 블록에 모두 포함된다면 인덱스를 통해 테이블을 스캔할 때 인덱스5번 + 테이블5번으로 10번의 스캔만으로 원하는 데이터를 얻을 수 있다. 이 경우 CF는 5(테이블 블록 스캔회수)가 된다. CF의 최소값은 테이블 블록수와 같다.

2) CF가 가장 높은 경우: 하나의 인덱스 블록에 포함된 4개의 ROWID가 모두 다른 테이블 블록에 포함된다면 인덱스를 통해 테이블을 스캔할 때 5(인덱스 블록수) + 5(인덱스 블록수)*4(각 인덱스 블록마다 스캔해야하는 테이블 블록수) = 25번의 스캔을 해야 원하는 데이터를 얻을 수 있다. 이 경우 CF는 20(테이블 블록의 스캔회수)가 된다. CF의 최대값은 테이블 로우수와 같다.

결론)
CF가 높을수록 테이블 블록을 읽는 회수가 증가하고 이로 인해 물리적 I/O가 증가하게 된다. 이와 비례해서 db file sequential read 대기가 증가하게 된다.
CF가 테이블의 블록수와 유사하다면 좋은 것이고, 로우수와 유사하다면 좋지 않다.
버퍼 캐시를 통해 한번 읽은 블록은 이후 추가적인 물리적 I/O가 발생하지 않으므로 CF가 높다고 해서 반드시 SQL 문의 성능이 저하되는 것은 아니다.
하지만 CF값이 높은 인덱스를 넓은 범위로 스캔하게 되면, 그만큼 읽어야 할 테이블 블록수가 늘어나 성능에 치명적인 영향을 줄 수 있다.

|
  ** ANALYZE 명령문이나, DBMS_STAS 패키지를 이용하면 인덱스의 CF를 구할 수 있다. 
  ** 인덱스에 대해 통계정보를 생성하면 DBA_INDEXES.CLUSTERING_FACTOR에 CF의 값이 들어간다. 
  ** SQL문의 성능 문제의 원인이 CF에 있는 것으로 판단되면, 인덱스 스캔 대신 FTS를 사용하거나 다른 인덱스를 이용하게끔 유도할 수도 있다. 이 모든 것이 여의치 않은 경우에는 테이블을 인덱스의 정렬순서와 동일한 순서로 재생성함으로써 해결할 수 있다.
  ** CF가 좋지 않다고 해서 항상 성능이 느린 것은 아니며 문제의 원인을 정확하게 파악하는 것이 매우 중요하다. 더구나 ASSM과 같은 관리기법을 사용할 경우 기존에 비해서 CF 값이 높아지는 경향이 있다.

 * Row chaining, Row migration 발생하지 않도록 PCTFREE 조정하거나 어플리케이션 보완한다.
  ** 인덱스의 ROWID를 이용해 테이블을 스캔하는 경우, 해당 로우에서의 Row chaining이나 Row migration에 의해 추가적인 I/O가 발생한다.
  ** DBA_TABLES 뷰의 CHAIN_CNT 컬럼에 chaining이나 migration이 발생한 로우 수가 기록된다. 
  ** V$SYSSTAT 뷰나 V$SESSTAT 뷰에서 chaining이나 migration 현황을 확인한다.
   *** table fetch by rowid 통계값은 ROWID를 통해 테이블 로우를 스캔한 회수이다. 인덱스를 경유해서 테이블을 페치하는 경우에 증가한다. 
   *** table fetch continued row 값은 chaining이나 migration에 의해 추가적으로 페치가 이루어진 회수이다. 만일 chaining이나 migration이 여러 블록에 거쳐있으면 블록수만큼 증가하게 된다.
  ** Row chaining은 로우의 크기가 블록보다 큰 경우에 발생한다. 테이블 정의를 변경하거나 PCTFREE 값을 작게 해서 테이블을 재생성하거나, 혹은 더 큰 크기의 블록을 사용한다.
  ** Row chaining이 발생하더라도 Select ... 에 명시된 모든 컬럼이 처음에 방문한 블록안에 있다고 하면 한번의 I/O만으로 원하는 결과를 얻을 수 있다. 이 경우에는 table fetch continued row 값이 증가하지 않는다. Select에서 불필요한 컬럼을 페치하지 않는다.
  ** Row migration  제거 방법
 |

• export 후 import 한다.
• alter table xxx move ... 를 수행한다.
• analyze table xxx list chained rows into yyyy 를 수행해서 migration이 발행한 로우들을 추출하고 해당 로우들에 대해 Delete/Insert 를 수행한다.

|

3. OS/디바이스 레이어 

h2. 문서에 대하여

* 최초작성자 : [~kwlee55]
* 최초작성일 : 2011년 01월 06일
* 이 문서는 [오라클클럽|http://www.gurubee.net] [코어 오라클 데이터베이스 스터디|5차 코어 오라클 데이터베이스 스터디] 모임에서 작성하였습니다.
* {color:blue}{*}이 문서의 내용은 (주)엑셈에서 출간한 'PRACTICAL OWI IN ORACLE 10G'와 'Advanced OWI in Oracle 10g'를 참고하였습니다.*{color}