이 포스트는 “SQL 전문가 가이드”의 “3-3장 옵티마이저 원리”를 학습한 내용으로 이루어져 있습니다.

옵티마이저

옵티마이저 소개

옵티마이저(Optimizer)

  • SQL을 처리하는 최저비용의 경로를 생성해주는 DBMS 내부 핵심엔진
  • 옵티마이저가 생성한 처리경로를 실행 계획(Execution Plan)이라 함
  • 대략적인 SQL 최적화 과정
    • 쿼리 수행을 위해 후보군이 될만한 실행 계획을 검색
    • 데이터 딕셔너리에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계 정보를 이용해 각 실행계획의 예상비용을 선정
    • 각 실행계획을 비교해서 최저비용을 갖는 하나를 선택

옵티마이저 종류

  • 규칙 기반 옵티마이저
    • Rule-Based Optimizer, Heuristic Optimizer
    • 미리 정해 놓은 규칙에 따라 액세스 경로를 평가하고 실행 계획을 선택
  • 비용기반 옵티마이저
    • Cost-Based Optimizer
    • 여러 통계 정보를 기초로 오퍼레이션 단계 별 예상 비용을 산정, 합산한 총비용이 가장 낮은 계획을 선택
    • 레코드 갯수, 블록 개수, 평균 행 길이, 칼럼 값의 수, 칼럼 값 분포, 인덱스 높이, 클터스터링 팩터 등을 고려
  • 스스로 학습하는 옵티마이저
    • Self-Learning Optimizer
    • 예상치와 런타임 수행 결과를 비교하고, 실행계획을 조정

SQL 최적화 과정

서브엔진별 역할

  • Parser: SQL 문장을 이루는 개별 요소를 분석하고 파싱해서 파싱트리를 생성 이 과정에서 Syntex, Sematic check가 이루어짐
  • Optimizer
    • Query Transformer: 파싱된 SQL을 표준적인 형태로 변환
    • Estimator: 오브젝트 및 시스템 통계정보를 이용해 실행계획 전체에 대한 총 비용을 계산
    • Plan Generator: 후보군이 될만한 실행계획들을 생성
  • Row-Source Generator: 옵티마이저가 생성한 실행계획을 SQL 엔진이 실행할 수 있는 코드 형태로 변환
  • SQL Engine: SQL을 실행

최적화 목표

  • 전체 처리속도 최적화
    • 쿼리 최종 결과집합을 끝까지 읽는 것을 전제로, 시스템 리소스를 가장 적게 사용하는 실행계획을 선택
    • 대부분의 DBMS의 기본 옵티마이저 모드
  • 최초 응답속도 최적화
    • 결과집합 중 일부만 읽다가 멈추는 것을 전제로, 가장 빠른 응답속도를 낼 수 있는 샐행계획을 선택
    • 만약 이 모드에서 데이터를 끝까지 읽는다면 전체 처리속도 최적화 실행계획 보다 많은 리소스를 사용하고 수행 속도가 느려질 수 있음

옵티마이저 행동에 영향을 미치는 요소

SQL과 연산자 형태

  • 결과가 같더라도 SQL을 어떤 형태로 작성했는지 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있고, 성능에 영향을 미친다

옵티마이징 팩터

  • 같은 쿼리라도 인덱스, IOT, 클러스터링, 파티셔닝, MV 드을 어떻게 구성했는지에 따라 실행계획과 성능이 크게 달라진다

DBMS 제약 설정

  • 데이터 무결성을 위해 PK, FK, Check, Not Null과 같은 제약 설정 기능을 이용
  • 제약 설정은 옵티마이저가 쿼리 성능을 최적화하는데 중요한 정보를 제공
  • 인덱스 칼럼에 Not Null 제약이 있으면 옵티마이저는 전체 수 Count 쿼리에 이 인덱스를 활용할 수 있음

옵티마이저 힌트

  • 옵티마이저의 판단보다 사용자가 지정한 옵티마이저 힌트가 우선

통계정보

  • 통계정보가 옵티마이저에게 미치는 영향력은 절대적임

옵티마이저 관련 파라미터

  • SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 동일하더라도 DBMS 버전을 업그레이드하면 옵티마이저가 다르게 동작할 수 있음
  • 이는 옵티마이저 관련 파라미터가 추가, 변경되면서 나타나는 현상임

DBMS 버전과 종류

  • 옵티마이저 관련 파라미터가 같더라도 버전에 따라 실행계획이 다를 수 있음
  • 같은 SQL 이라도 DBMS 종류에 따라 내부적으로 처리하는 방식이 다를 수 있음

옵티마이저의 한계

옵티마이저는 사람이 만든 소프트웨어 엔진에 불과하며 완벽할 수 없다. 현재의 기술수준으로 어려운 문제가 있는가 하며, 기술적으로 가능하나 현실적인 제약 때문에 적용하지 못하는 것들도 있다.

  • 옵티마이징 팩터의 부족
  • 통계정보의 부정확성
  • 바인드 변수 사용시 균등분포 가정
  • 비현실적인 가정
  • 규칙에 의존하는 CBO
  • 하드웨어 성능 특성

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

옵티마이저가 참조하는 네 가지 종류의 통계정보

  • 테이블 통계: 전체 레코드 수, 총 블록 수, 빈 블록 수, 한 행당 평균 크기 등
  • 인덱스 통계: 인덱스 높이, 리프 블록 수, 클러스터링 팩터, 인덱스 레코드 수 등
  • 칼럼 통계: 값의 수, 최저 값, 최고 값, 밀도, null값 개수, 칼럼 히스토그램 등
  • 시스템 통계: CPU 속도, 평균적인 I/O 속도, 초당 I/O 처리량 등

선택도

  • 선택도는 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율을 말한다
  • 선택도 -> 카디널리티 -> 비용 -> 액세스 방식, 조인 순서, 조인 방법 등 결정
  • 히스토그램이 없거나 있더라도 조건절에 바인드 변수를 사용하면 옵티마이저는 데이터 분포가 균일하다고 가정한 상태에서 선택도를 구한다
  • 히스토그램 없이 등치 조건에 대한 선택도를 구하는 공식은 다음과 같다.
  • 선택도 = 1 / Distinct Value 개수 = 1 / num distinct

카디널리티

  • 카디널리티는 특정 액세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수를 말한다
  • 카디널리티 = 총 로우 수 * 선택도
  • 칼럼 히스토그램이 없을 때 등치 조건일 때는 다음과 같다
  • 카디널리티 = 총 로우 수 * 선택도 = num_rows / num_distinct

바인드 변수 사용 시 카디널리티 계산

  • 바인드 변수를 사용하면 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고 실행시점에서 그것을 그대로 가져와 값만 다르게 바인딩하며 반복 재사용한다
  • 변수를 바인딩하는 시점이 최적화 시점보다 나중인 실행시점이다
  • SQL을 최적화하는 시점에 조건절 칼럼의 데이터 분포를 활용하지 못한다
  • 그러므로 바인드 변수를 사용할 때 옵티마이저가 평균 분포를 가정한 실행계획을 생성하여 사용한다
  • 칼럼 분표가 균일할 때는 상관없겠지만, 그렇지 않을 때는 바인딩되는 값에 따라 쿼리 성능이 다르게 나타날 수 있다
  • OLTP성 쿼리더라도 값의 종류가 적고 분포가 균일하지 않을 때는 상수 조건을 쓰는 것이 유용할 수 있다

히스토그램

미리 저장된 히스토그램 정보가 있으면, 옵티마이저는 그것을 사용해 더 정확하게 가디널리티를 구할 수 있다 분포가 균일하지 않은 칼럼으로 조회할 때 효과를 발휘한다.

  • 도수분포 히스토그램
    • 칼럼이 가진 값 별로 빈도수를 저장하는 히스토그램을 말한다
    • 칼럼이 가진 값의 수가 적을 때 사용된다
    • 값의 수가 적기 때문에 각각 하나의 버킷을 할당하는 것이 가능하다
  • 높이균형 히스토그램
    • 칼럼이 가진 값이 수가 아주 많아 각각 하나의 버킷을 할당하기 어려울 때 사용한다
    • 하나의 버킷이 여러 개의 값을 담당한다

비용

비용이란 쿼리를 수행하는데 소요되는 일량 또는 시간 예상한 값을 의미한다. 옵티마이저 비용 모델은 I/O 비용 모델과 CPU 비용 모델이 있다. I/O 비용 모델은 예상되는 I/O call 횟수만을 쿼리 수행 비용으로 간주해 실행계획을 평가한다. 반면 CPU 비용 모델은 여기에 시간 개념을 더해 비용을 산정한다.

  • 인덱스를 경유한 테이블 액세스 비용
    • 인덱스를 경유한 테이블 액세스 시에는 Single Block I/O 방식이 사용된다
    • 비용 = blevel + (리프 블록 수 * 유효 인덱스 선택도) + (클러스터링 팩터 * 유효 테이블 선택도)
항목 설명
blevel 브랜치 레벨을 의미, 리프 불록에 도달하기 전에 읽게 될 브랜치 블록 갯수
클러스터링 팩터 특정 칼럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도. 인덱스를 경유해 테이블 전체 로우를 액세스 할 때 읽을 것으로 예상되는 논리적인 블록 개수로 계수화
유효 인덱스 선택도 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율(%)
유효 테이블 선택도 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율(%)
  • Full Scan에 의한 테이블 액세스 비용
    • 테이블 전체를 순차적으로 읽어 들이는 과정에서 발생하는 I/O Call 횟수로 비용을 계산
    • Full Scan할 때는 I/O Call로 Multiblock I/O 방식을 사용

옵티마이저 힌트

통계정보가 정확하지 않거나 기타 이유로 옵티마이저가 잘못된 판단을 할 수 있다. 그럴 때 옵티마이저 힌트롤 이용해 개발자가 직접 인덱스를 지정하거나 조인 방식을 변경함으로써 더 좋은 실행계획으로 유도할 수 있다.

힌트 기술 방법

SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk)
		   USE_MERGE(j) FULL(j) */
	e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1, employess e2, job_history j
WHERE e1.employe_id = e2.manager_id
	AND e1.employee_id = j.employee_id
	AND e1.hire_date = j.start_date
GROUP BY e1.first_name, e1.lsat_name, j.job_id
ORDER BY total_sal;

힌트가 무시되는 경우

  • 문법적으로 안 맞게 힌트를 기술
  • 의미적으로 안 맞게 힌트를 기술
  • 잘못된 참조 사용
  • 논리적으로 불가능한 액세스 경로
  • 버그

힌트 종류

  • 최적화 목표
    • all_rows
    • first_rows(n)
  • 액세스 경로
    • full
    • cluster
    • hash
    • index, no_index
    • index_asc, index_desc
    • index_combine
    • index_join
    • index_ffs, no_index_ffs
    • index_ss, no_index_ss
    • index_ss_asc, index_ss_desc
  • 쿼리 변환
    • no_query_transformation
    • use_concat
    • no_expand
    • rewrite, no_rewrite
    • merge, no_merge
    • star_transformation, no_start_transformation
    • fact, no_fact
    • unset, no_unset
  • 조인 순서
    • ordered
    • leading
  • 조인 방식
    • use_nl, no_use_nl
    • use_nl_with_index
    • use_merge, no_use_merge
    • use_hash, no_use_hash
  • 병렬 처리
    • parallel, no_parallel
    • pq_distribute
    • parallel_index, no_parallel_index 기타
    • append, no_append
    • cache, nocache
    • push_pred, no_push_pred
    • push_subq, no_push_subq
    • qb_name
    • cursor_sharing_exact
    • driving_site
    • dynamic_sampling
    • model_min_analysis

쿼리변환

쿼리변환이란

옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 의미한다. 이는 실행계획을 생성하고 비용을 계산하기에 앞서 사용자 SQL을 최적화에 유리한 형태로 재작성하는 것으로서, 비용기반 옵티마이저의 서브엔진 중 Query Transformer가 그런 역할을 담당한다.

  • 휴리스틱 쿼리 변환
    • 결과만 보장된다면 무조건 쿼리 변환을 수행
    • 일종의 규칙기반 최적화 기법
  • 비용기반 쿼리 변환
    • 변환된 쿼리의 비용이 더 낮을 때만 쿼리 변환을 수행

서브쿼리 Unnesting

중첩된 서브쿼리를 풀어내는 것을 말한다. 서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다. 옵티마이저는 많은 조인 테크닉을 가지기 때문에 조인 형태로 변환했을 때 더 나은 실행계획을 찾을 가능성이 높다.

중첩된 서브쿼리는 메인쿼리와 부모와 자식이라는 종속적이고 계층적인 관계가 존재한다. 논리적인 과점에서 그 처리과정은 필터 방식이어야 한다. 즉 메인쿼리에서 읽히는 레코드마다 서브쿼리를 반복 수행하면서 조건에 맞지 않는 데이터를 골라내는 것이다.

옵티마이저의 필터 방식의 서브쿼리 수행 방법

  • 동일한 결과를 보장하는 조인문으로 변환 후 최적화한다. 이 방법을 ‘서브쿼리 Unnesting’ 혹은 ‘서브쿼리 Flatting’이라고 한다. 이렇게 쿼리 변환이 이루어지고 나면 일반 조인문 처럼 다양한 최적화 기법을 사용할 수 있다.
  • 서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화한다. 메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 최적화를 수행하며, 이 때 서브쿼리에 필터 오퍼레이션이 나타난다. 이 방법을 사용하면 쿼리문 전체의 최적을 달성하지 못할 때가 많다.

힌트

  • unnset: 서브쿼리를 Unnesting 함으로써 조인방식으로 최적화하도록 유도한다.
  • no_unnset: 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도한다.

뷰 Merging

뷰 쿼리 블록은 액세스 쿼리 블록(뷰를 참조하는 쿼리 블록)과의 머지(merge) 과정을 거쳐 다른 형태로 변환된다. 뷰를 머징해야 옵티마이저가 더 다양한 액세스 경로를 조사 대상으로 삼을 수 있다.

조건절 Pushing

조건절이 가능한 빨리 처리되도록 뷰 안으로 밀어 넣는다면, 뷰 안에서의 처리 일량을 최소화하게 됨은 물론 리턴되는 결과 건수를 줄임으로써 다음 단계에서 처리해야 할 일량을 줄일 수 있다.

  • 조건절 Pushdown
    • 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽으로 밀어넣는 것
  • 조건절 Pullup
    • 쿼리 블록 안에 있는 조건절을 쿼리 블록 밖으로 내오는 것
    • 그것을 다시 다른 쿼리 블록에 pushdown 하는데 사용함
  • 조인 조건 Pushdown
    • NL Join 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner 뷰 안으로 밀어넣는 것을 말함

조건절 이행

A=B이고 B=C이면 A=C이다 라는 추론을 통해 새로운 조건절을 내부적으로 생성해주는 쿼리변환이다.

  • e.deptno=10이고 e.deptno=d.deptno이므로 d.deptno=10으로 추론이 가능하다.
  • 이런 과정을 통해 조건절이 변환되어 join을 수행하기 전에 각 테이블에 필터링을 적용함으로써 조인되는 데이터양을 줄일 수 있다.
  • 추가로 추론을 통해 필터링을 할 때 인덱스를 사용을 추가로 고려할 수 있다.

불필요한 조인 제거

1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에도 1쪽 테이블을 참조하지 않는다면, 쿼리 수행 시 1쪽 테이블은 읽지 않아도 된다. 결과 집합에 영향을 미치지 않기 때문이다. 옵티마이저는 이를 이용해 M쪽 테이블만 읽도록 쿼리를 변환하는데 이를 ‘조인 제거’ 또는 ‘테이블 제거’라고 한다. 조인 제거 기능이 작동하려면 PK와 FK제약이 설정돼 있어야만 한다.

OR 조건을 Union으로 변환

OR 조건으로 필터링 되는 SQL은 Full Table Scan(혹은 Index Combine)이 동작한다. 각각에 생성된 인덱스를 사용하고 싶다면 union all 형태로 변환하여 사용하면 된다. 사용자가 쿼리를 직접 바꿔주지 않아도 옵티마이저가 이런 작업을 대신해주는 경우도 있다. 이를 ‘OR-Expansion’이라고 한다.

힌트

  • use_concat: OR-Expansion을 유도
  • no_expand: OR-Expansion을 방비

기타 쿼리 변환

  • 집합 연산을 조인으로 변환
  • 조인 칼럼에 IS NOT NULL 조건 추가
  • 필터 조건 추가
  • 조건절 비교 순서

출처: "SQL 전문가 가이드, 2013 Edition", 서강수, 한국데이터베이스진흥원, 2013