[SQLP] 1장. 아키텍처 기반 튜닝 원리 - 데이터베이스 I/O 원리

데이터베이스 I/O 원리

1. 블록 단위 I/O

• 하나의 레코드를 읽더라도 레코드가 속한 블록 전체를 읽는다.

• SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스 하는 블록 개수이다.

• 캐시와 데이터 단위 모두 적용된다.

2. 메모리 I/O vs 디스크 I/O

가. I/O 효율화 튜닝의 중요성

디스크 입출력 : 디스크의 액세스 arm이 움직이면서 데이터를 Read Write 하기 때문에 느리다. 
메모리 입출력 : 전기적 신호에 불과하기 때문에 빠르다.

나. 버퍼 캐시 히트율(Buffer Cache Hit Ratio)

버퍼 캐시 효율을 측정하는 지표로서, 전체 읽은 블록 중에서 메모리 버퍼 캐시에서 찾은 비율을 나타낸다.

BCHR = (버퍼 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) x 100

다. 네트워크, 파일시스템 캐시가 I/O 효율에 미치는 영향

• RAC(Oracle Real Application Clusters) 같은 클러스터링(Clustering) 데이터베이스 환경에선 인스턴스 간 캐시된 블록을 공유하므로 메모리 I/O 성능에 네트워크 속도가 영향을 미침

• 논리적인 블록 요청 횟수를 최소화하는 것이다.

3. Sequential I/O vs Random I/O

• Sequential 액세스는 논리적/물리적 순서를 따라 차례대로 읽어 나가는 방식

• Random 액세스는 레코드간 논리적, 물리적인 순서를 따르지 않고, 한건을 읽기 위해 한 블록씩 접근하는 방식

• I/O 튜닝의 핵심 원리

  • Sequential 액세스에 의한 선택 비중을 높인다.

  • Random 액세스 발생량을 줄인다.

가. Sequential 액세스에 의한 선택 비중 높이기

• 읽는 총 건수 중에서 결과 집합으로 선택되는 비중을 높여야 함

• 같은 결과를 얻기 위해 얼마나 적은 레코드를 읽느냐로 효율성이 판단됨

• 조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성에 의해 효율성이 결정됨

• 인덱스 순서를 변경해줌으로써 효율성 ↑

나. Random 액세스

• 인덱스에 조건절 컬럼을 추가해줌으로써 랜덤액세스 감소

4. Single Block I/O vs MultiBlock I/O

• Single Block I/O : 한번의 I/O Call에 하나의 데이터 블록만 읽어서 메모리에 적재하는 방식

• Multi Block I/O : I/O Call 이 필요한 시점에, 인접한 블록들을 같이 읽어 메모리에 적재하는 방식

  • Table Full Scan 처럼 물리적으로 저장된 순서에 따라 읽을때 인접한 블록들을 같이 읽는것이 유리함

  • 인접한블록 : 하나의 익스텐트에 속한 블록

5. I/O 효율화 원리

논리적인 I/O 요청 횟수를 최소화하는 것이 I/O 효율화 튜닝의 핵심 원리다.

가. 필요한 최소 블록만 읽도록 SQL 작성

예) SELECT 절에 SUB QUERY 사용하여 반복 액세스 하던 것을 
한번만 조회하여 GROUP BY로 묶어 사용하는 것이 더 COST가 적음

나. 최적의 옵티마이징 팩터 제공

• DBMS가 제공하는 기능 활용 
  인덱스 외에도 DBMS가 제공하는 다양한 기능을 적극적으로 활용한다. 인덱스, 파티션, 클러스터, 윈도우 함수 등을 적극 활용해 옵티마이저가 최적의 선택을 할 수 있도록 한다.

• 옵티마이저 모드 설정 
  옵티마이저 모드(전체 처리속도 최적화, 최초 응답속도 최적화)와 그 외 옵티마이저 행동에 영향을 미치는 일부 파라미터를 변경해 주는 것이 도움이 될 수 있다.

다. 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도