by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

db file sequential read II




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는 메모리에 단 하나의 블록만을 담을 수 있는 공간이 있다고 가정하고, 인덱스 스캔에 따라 테이블을 얼마나 스캔해야 하는지를 계산한 값
      이다.
        예) 5개의 블록으로 이루어진 인덱스와 5개의 블록으로 이루어진 테이블이 있다. 하나의 블록에는 4개의 로우가 있다. 따라서 총 로우수는 5*4 = 20개이다. 인덱스를 차례로 스캔하면서 이와 매칭되는 테이블을 읽어오는 경우, 두개의 극단적인 경 우가 있을 수 있다.
      
        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/디바이스 레이어

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.