SQL 옵티마이저

1. 통계정보와 비용 계산 원리

1) 선택도와 카디널리티

- 선택도: 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율

- 선택도 = 1 / NDV(Number of Distinct Values, 컬럼 값 종류 갯수)

- 카디널리티: 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수

- 카디널리티 = 총 로우 수 * 선택도 = 총 로우 수 / NDV

- 옵티마이저는 카디널리티를 구하고, 그만큼의 데이터를 액세스하는 데 드는 비용을 계산해서 테이블 엑세스 방식, 조인 순서, 조인 방식 등을 결정한다.

 

2) 통계정보

 

3) 비용 계산 원리

비용(Cost)의 정확한 의미

'I/O 비용 모델'을 사용할 때 실행계획에 나타나는 Cost는 '예상 I/O Call 횟수'를 의미한다.

반면, 최신 'CPU 비용 모델'에서 Cost는 Single Block I/O를 기준으로 한 상대적 시간을 표현한다. 예를 들어, Cost가 100으로 표시된다면, '우리 시스템에서 Single Blick I/O를 100번 하는 정도의 시간'으로 해석하면 된다. 상대적 시간개념이다.

똑같이 I/O Call을 100번 해도 그것이 Single Block I/O냐 Multiblock I/O냐에 따라 속도가 다르고, 시스템마다 Single Block I/O와 Multiblock I/O 속도도 모두 다르다.

 

2. 옵티마이저에 대한 이해

1) 옵티마이저 종류

- 비용기반(Cost-Based) 옵티마이저(CBO)는 사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출하고, 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저다. CBO가 사용하는 통계정보로는 데이터량, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등이 있다.

 

2) 옵티마이저 모드

ALL_ROWS: 전체 처리속도 최적화

- 이 모드에서 옵티마이저는 쿼리 결과집합 '전체를 읽는 것을 전제로' 시스템 리소스(I/O, CPU, 메모리 등)를 가장 적게 사용하는 실행계획을 선택한다.

FIRST_ROWS: 최초 응답속도 최적화

- 이 모드에서 옵티마이저는 전체 결과집합 중 '앞쪽 일부만 읽다가 멈추는 것을 전제로' 응답 속도가 가장 빠른 실행계획을 선택한다.

FIRST_ROWS_N: 최초 N건 응답속도 최적화

- 이 모드에서 옵티마이저는 사용자가 '앞쪽 N개 로우만 읽고 멈추는 것을 전제로' 응답 속도가 가장 빠른 실행계획을 선택한다.

 

3) 옵티마이저에 영향을 미치는 요소

(1) SQL과 연산자 형태

(2) 인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이징 팩터

(3) 제약 설정

(4) 통계정보

(5) 옵티마이저 힌트

(6) 옵티마이저 관련 파라미터

 

4) 옵티마이저의 한계

- 보편적으로 좋은 선택을 하지만, 그 선택이 항상 최선은 아니다. DBA가 통계정보를 아무리 잘 수집하고 개발자가 SQL을 잘 작성해도 실수가 있기 마련이다. 

 

5) 개발자의 역할

- 데이터베이스 세계에서도 불완전한 옵티마이저에만 의존할 것이 아니라 개발자 스스로 옵티마이저가 되어야 한다. 바쁘니 일단 옵티마이저에 맡기지만, 그 결과물이 올바른지 실행계획을 통해 늘 점검하고, 더 개선할 여지는 없는지 찾으려고 노력해야 한다.

새로운 개발 언어를 익히는 노력 이상으로 SQL 수행원리와 튜닝방법을 익히는 데도 많은 노력과 시간을 투자해야 한다. 

- RDMBS 환경에서 SQL 성능을 높이기 위해 개발자가 할 일은 다음과 같다.

  ① 필요한 최소 블록만 읽도록 쿼리를 작성한다.

  ② 최적의 옵티마이징 팩터를 제공한다.

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

 

6) 튜닝 전문가가 되는 공부방법

- 가장 필요한 지식과 기술력은 다음과 같다

  ① 옵티마이저가 SQL을 파싱하고 통계정보를 활용해 실행계획을 생성하는 원리

  ② 옵티마이저 쿼리변환 원리를 바탕으로 실행계획을 분석하는 방법

  ③ 옵티마이저 힌트를 이용해 실행계획을 제어하는 방법

  ④ 옵티마이저가 좋은 실행계획을 생성하도록 유도하기 위한 효과적인 SQL 작성법

  ⑤ 애플리케이션에서 SQL을 실행할 때 사용하는 프로그래밍 인터페이스

  ⑥ SQL을 빠르게 처리할 수 있는 좋은 데이터 구조와 파티션/인덱스 설계

  ⑦ 정확성과 안정성을 확보할 수 있는 통계정보 수집 정책

 

출처: 도서 <친절한 SQL 튜닝>