SQL 처리 과정과 I/O

SQL튜닝

• 논리적인 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 SQL 튜닝이다.
  • 물리적 I/O와 논리적 I/O의 정의
    • 물리적 I/O란 DB 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O
    • 논리적 I/O란 SQL을 수행하면서 읽은 총 블록 I/O
  • 논리적 I/O는 항상 일정하게 발생하지만, SQL 튜닝을 통해 통재 가능한 변수이다.
  • BCHR(버퍼 캐시 히트율)
    • 전체 블록에서 물리적인 디스크 I/O를 수반하지 않고 곧바로 찾은 비율
    • (캐시에서 곧바로 작은 블록 수 / 총 읽은 블록 수) * 100
    • ( (논리적 I/O - 물리적 I/O) / 논리적 I/O ) * 100
    • (1 - 물리적 I/O / 논리적 I/O ) * 100
    • 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야한다.
  
  • 논리적 I/O는 (Query + Current)의 값 217개이고 이는 Disk에서 읽은 값 174개를 포함한 값이다. 이는 디스크에서 찾은 데이터를 버퍼캐시에 적재하고 읽기 때문이다.
  • BCHR = (1 - (Disk / (Query + Current))) * 100 = 약 20%
    • BCHR이 높다고 해서 반드시 효율적인 SQL을 의미하는 것은 아니다. 같은 블록을 비효율적으로 반복해서 읽는 경우도 있기 때문이다.

SQL 파싱과 최적화

옵티마이저

• 'SQL 최적화'란 DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정을 의미한다.
  

  • 프로시저를 만들어내는 DBMS 내부 엔진을 SQL 옵티마이저라고 한다.
    1. 사용자로 부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아낸다.
    2. 데이터 딕셔너리에 미리 미리 수집해 준 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
    3. 최저 비용을 나타내는 각 실행계획의 예상비용을 산정한다.

  

  • 옵티마이저 진행시 고려 사항

    • 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
    • 오브텍트 통계: 테이블 통계, 인덱스 통계, 컬럼 통계
    • 시스템 통계: CPU 속도, Single Block I/O 속도, MultiBlock I/O 속도
    • 옵티마이저 관련 파라미터
    
    

  • 옵티마이저 힌트 사용시 주의사항

    • 힌트 안에 인자를 나열할 땐 ','를 사용할 수는 있지만, 힌트와 힌트 사이에는 사용하면 안된다.
      • /*+ INDEX(A A_X01) INDEX(B, B_X01) */
      • /*+ INDEX(C), FULL(D) */ -> 첫 번째 힌트만 유효
    • 테이블을 지정할 때 아래와 같이 스키마명까지 명시하면 안된다.
      • SELECT /*+ FULL(SCOTT.EMP) */ FROM EMP -> 무효
    • FROM 절 테이블명 옆에 ALIAS를 지정했다면, 힌트에도 반드시 ALIAS를 사용해아한다.
      • SELECT /*+ FULL(EMP) */ FROM EMP E -> 무효

SQL 파싱

• SQL 파싱 진행 과정
  • 사용자로부터 SQL를 전달받으면 가장 먼저 SQL 파서가 파싱을 진행한다.
    1. 파싱 트리 생성: SQL문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
    2. Syntax 체크: 문법적 오류가 없는지 확인
    3. Semantic 체크: 의미상 오류가 없는지 확인 (테이블 존재 유무, 사용 권한)
• 소프트 파싱과 하드 파싱
  • 소프트 파싱
    • SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 소프트 파싱
  • 하드 파싱
    • SQL을 캐시에서 찾지 못해 최적화 및 로우 소스 생성까지 거치는 것을 하드 파싱
• SQL은 일회성이기 때문에 자주 사용되는 SQL은 프로시저 생성을 통해서 하드 파싱 발생을 최소화 시켜야한다.

데이터 저장 구조 및 I/O 메커니즘

데이터베이스 저장 구조

• 테이블 스페이스: 세그먼트를 담는 컨테이너
  • 하나의 테이블 스페이스를 여러 데이터 파일로 구성하는 이유
    • 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 여러 데이터파일로 분산 저장
• 세그먼트: 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)
• 데이터파일: 디스크 상의 물리적인 OS 파일
• 익스텐트: 공간을 확장하는 단위. 연속된 블록 집합
  • 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 수 있다.
  • 한 익스텐트에 담긴 블록은 모두 같은 테이블 블록이다.
• 블록: 데이터를 읽고 쓰는 단위
  • 한 블록에 저장된 레코드는 모두 같은 테이블 레코드이다.

Block 단위 IO

• 데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽을 때 블록 단위로 읽는다.
  즉, 1KB짜리 컬럼 하나를 읽고 싶어도 블록을 통째로 읽는다.
• 오라클은 기본적으로 8KB 크기의 블록을 사용

시퀀셜 액세스와 랜던 액세스

• 테이블 또는 인덱스 블록을 액세스하는 방식으로는 시퀄셜 액세스와 랜덤 액세스 두가지 방식이 있다.
  • 시퀄셜 액세스
    • 논리적 또는 물리적 연결된 순서에 따라 차례대로 블록을 읽는 방식
  • 테이블 블록 간에는 서로 논리적인 연결고리를 갖고 있지 않은데 어떻게 시퀀셜 방식으로 액세스 할까??
    • 세그먼트에 할당된 익스텐트 목록을 세크먼트 헤더에 맵으로 관리하고 있어서 읽어야 하는 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽는다. -> Full Table Scan
  • 랜덤 액세스
    • 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식이다.

Single Block I/O vs Multiblock I/O

• Single Block I/O
  • 'Single Block I/O'란 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
  • 인덱스를 사용할 때 사용하며 인덱스를 사용할 때 소량 데이터를 읽기 때문에 효율적이다
  • Single Block I/O Operation
    • 인덱스 루트 블록을 읽을 때
    • 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
    • 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
    • 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
• 'Multi Block Block I/O'
  • 'Multi Block Block I/O'는 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식
    • I/O 캐시에서 찾지 못해서 I/O Call을 할 때 디스크 상에 그 블록과 인접한 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 기능
    • OS의 I/O 단위에 종속적인데 이는 일반적으로 OS I/O 단위인 1MB이상의 데이터를 한번에 가져 올수 없다는 것을 의미한다.
  • 많은 데이터 블록을 읽을 때 효율적이며 인덱스를 사용하지 않고 테이블 전체를 스캔할 때 사용한다.
  • 대용량 테이블을 Full Scan 할 때 Multiblock I/O 단위를 크게 설정하면 성능이 좋아진다.

Table Full Scan vs Index Range Scan

• 테이블에 저장된 데이터를 읽는 방식은 Table Full Scan과 Index Range Scan이 있다.

  • Table Full Scan은 테이블에 속한 블록 '전체'를 읽어서 사용자가 원하는 데이터를 찾는 방식
    • 시퀀셜 액세스와 MultiBlock I/O 방식으로 디스크 블록을 읽는 방식.
    • 스토리지 스캔 성능에 영향을 많이 받는다.
  • Index Range Scan은 인덱스에서 '일정량'을 스캔하면서 찾은 ROWID로 테이블 레코드를 찾는 방식
    • 큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스를 사용해한다.
    • Singleblock I/O 방식을 사용하기 때문에 많은 데이터를 읽을 때 비효율적이다.

  

• 튜닝 방식 예시

  • Table Full Scan이 반드시 나쁜 것은 아니다. 그렇기 때문에 실행 계획에 Table Full Scan 방식이 많다고해서 성능이 나쁜 것이 아니다
    • 한 번에 많은 데이터를 처리하는 집계용 SQL과 배치 프로그램은 Table Full Scan 방식이 효율적이며 Join을 포함한 SQL은 Join Method를 Hash Join으로 선언하는 것이 좋다.

캐시 탐색 메커니즘

• Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유한다.
  • 버퍼캐시 탐색 과정
    • 인덱스 루트 블록을 읽을 때
    • 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
    • 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
    • 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
    • 테이블 블록을 Full Scan
      
  • 버퍼캐시는 공유 자원이기 때문에 '동시성' 이슈가 있다.
    - '직렬화'를 통해서 프로세스가 순차적으로 접근하도록 해야한다,
    - 해시 체인을 스캔하는 동안 프로세스가 체인 구조를 변경하는 일을 방지하기 위한 해시 체인 래치가 필요하다.
    - 버퍼블록 자체에도 직렬화 메커니즘이 필요하며 Buffer Lock을 이용한다.
    하지만 직렬화를 사용해도 캐시 경합이 발생하기 때문에 결국 논리적 I/O수를 줄이도록 노력해야한다.