[SQLP] 3장. 옵티마이저 원리 - 옵티마이저

1. 옵티마이저 소개

가. 옵티마이저란?

SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해 주는 DBMS 핵심엔진

• 사용자가 던진 쿼리수행을 위해, 후보군이 될만한 실행계획을 찾는다.

• 딕셔너리에 미리 수집해 놓은 오브젝트 통계정보를 이용해 예상비용을 산정한다.

• 각 실행계획을 비교해서 최저비용을 갖는 하나를 선택한다.

나. 옵티마이저 종류

1) 규칙기반 옵티마이저

• Rule-Based Optimizer (RBO). 다른 말로 ‘휴리스틱 옵티마이저’

• 경로별 우선순위로 규칙산정

• 인덱스 구조, 연산자, 조건절 형태가 순위를 결정짓는 주요인

2) 비용기반 옵티마이저

• Cost-Based Optimizer (CBO)

• 비용이란 쿼리를 수행하는데 소요되는 일량 또는 시간

• 레코드 개수, 블록 개수, 평균 행 길이, 칼럼 값의 수, 칼럼 값 분포, 인덱스 높이 등으로 비용 산정

스스로 학습하는 옵티마이저 
예상한 값과 실제 런타임 수행 결과를 비교하고, 예상치가 빗나갔을 때 실행계획을 조정하는 옵티마이저로 발전 중

다. SQL 최적화 과정

• Parser 
  SQL문장을 이루는 개별 구성요소를 분석하고 파싱해서 파싱트리(내부적인 구조체)를 만듬

• 옵티마이저

  • Query Transformer 
    파싱된 SQL을 좀 더 일반적이고 표준적인 형태로 변환

  • Estimator 
    시스템 통계정보를 이용해 카디널리티, 비용, 실행계획 총 비용 계산

  • Plan Generator 
    하나의 쿼리를 수행하는 데 있어, 후보 실행계획들을 생성

• Row-Source Generator 
  옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드 형태로 포맷

• SQL Engine 
  SQL 실행

라. 최적화 목표

1) 전체 처리속도 최적화

시스템 리소스(I/O, CPU, 메모리 등)를 가장 적게 사용하는 실행계획 선택

alter system set optimizer_mode = all_rows; -- 시스템 레벨 변경
alter session set optimizer_mode = all_rows; -- 세션 레벨 변경
select /*+ all_rows*/ *  -- 쿼리 레벨 변경
from t
where... 

2) 최초 응답속도 최적화

• 전체 결과집합 중 일부만 읽다가 멈추는 것을 전제

• 오라클에서 옵티마이저 모드를 first_rows_10으로 지정하면, 사용자가 전체 결과집합 중 처음 10개 로우만 읽고 멈추는 것을 전제로 가장빠른 응답 속도를 낼 수 있는 실행계획을 선택

select /*+first_rows(10)*/ *
from t
where ;

2.옵티마이저에 영향을 미치는 요소

가. SQL과 연산자 형태 : 어떤 연산자를 사용했는지에 따라서 쿼리 성능에 영향을 미침 
나. 옵티마이징 팩터 : 쿼리를 똑같이 작성해도 인덱스, 클러스터링, 파티셔닝 에 따라 실행계획 달라짐 
다. DBMS 제약 설정 : 참조 무결성, PK, FK 등의 제약 설정은 쿼리 성능 최적화 하는데 중요한 정보 제공 
라. 옵티마이저 힌트 : 옵티마이저 판단보다 사용자 지정 힌트가 우선적임 
마. 통계정보 : CBO의 모든 판단 기준은 통계정보에서 나옴 
바. 옵티마이저 관련 파라미터 : DBMS 버전을 업그레이드 하면 파라미터 추가 또는 변경 
사. DBMS 버전과 종류

3. 옵티마이저의 한계

가. 옵티마이징 팩터의 부족

사용자가 지정한 인덱스, 클러스터링 등의 옵티마이징 팩터를 제공하지 않는다면 좋은 실행계획이 되지 못한다.

나. 통계정보의 부정확성

100% 정확한 통계정보를 유지하기는 현실적으로 불가능 함. 
예를 들어 컬럼 값의 분포가 일정하다는 보장이 없음.

다. 바인드 변수 사용 시 균등분포 가정

정확한 컬럼 히스토그램을 보유하더라도 바인드 변수를 사용한 SQL에는 무용지물임. 
조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용을 계산.

라. 비현실적인 가정

예를 들어, 예전 DBMS에서는 Single Block I/O와 Multi Block I/O의 비용을 같게 평가함.

마. 규칙에 의존하는 CBO

비용기반 옵티마이저라도 부분적으로는 규칙에 의존.

바. 하드웨어 성능

옵티마이저 개발팀이 사용한 하드웨어 성능에 맞춰져 있음. 실제 운영 하드웨어와 사양이 다를 때 잘못된 실행 계획을 세울 수 있음.

4. 통계정보를 이용한 비용계산 원리

가. 선택도

선택도(Selectivity)는 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율

선택도 = 1/(Distinct Value 개수)

나. 카디널리티

카디널리티 = 총 로우 수 X 선택도 = num_rows / num_distict

다. 히스토그램

• 도수분포 히스토그램 
  값별로 빈도수(frequency number)를 저장하는 히스토그램

• 높이균형 히스토그램 
  각 버킷의 높이가 같게 저장하는 히스토그램

라. 비용

• I/O 비용 모델 : 예상되는 I/O요청(Call) 횟수만을 쿼리 수행 비용으로 간주해 실행계획을 평가함

  • 인덱스를 경유한 테이블 액세스 비용 
    디스크 I/O Call 횟수(논리적/물리적으로 읽은 블록 개수가 아닌 I/O Call 횟수)

  • Full Scan에 의한 테이블 액세스 비용 
    테이블 전체를 순차적으로 읽어 들이는 과정에서 발생하는 I/O Call 횟수로 비용 산정

• CPU 비용 모델 : I/O 요청 횟수에 시간 개념을 더해 비용 산정

5. 옵티마이저

• Oracle 힌트

  select /+ all_rows/ * 
  위와 같이 기술

• 힌트 종류

  • 최적화 목표 
    all_rows

  • 액세스 경로 
    indwx, index_join, cluster

  • 쿼리 변환 
    use_concat, merge

  • 조인 순서 
    ordered, leading

  • 조인 방식 
    use_merge, use_nl

  • 병렬 처리 
    parallel

  • 기타 
    append, cache

• SQL Server 힌트

  • 테이블 힌트 
    테이블명 다음에 WITH절을 통해 지정한다. fastfirstrow, holdlock, nolock 등

  • 조인 힌트 
    FROM절에 지정하며, 두 테이블 간 조인 전략에 영향을 미친다. loop, hash, merge, remote 등

  • 쿼리 힌트 
    쿼리당 맨 마지막에 한번만 지정할 수 있는 쿼리 힌트는 아래와 같이 OPTION절을 이용한다.