Query Planner
쿼리 플랜(query plan) 또는 쿼리 실행 계획(query execution plan)은 SQL 관계형 데이터베이스 관리 시스템의 데이터 접근에 사용되는 순서가 있는 단계별 집합이다.
SQL이 선언형이기 때문에 주어진 쿼리를 실행하기 위해 수많은 방법이 존재한는 것이 일반적이며 이에 따라 다양한 성능 차이를 보인다.
쿼리가 데이터베이스에 제출되면 쿼리 옵티마이저는 쿼리 실행을 위한 각기 다른 가능성이 있는 올바른 플랜 중 일부를 평가한 다음 최적의 옵션을 고려할 때 반환한다.
쿼리 옵티마이저가 불완전하기 때문에 데이터베이스 사용자와 관리자들은 더 나은 성능을 얻기 위해 옵티마이저가 만들어내느 플랜을 수동으로 검사하고 튜닝하는 경우가 있다.
Query Optimizer
옵티마이저(optimizer)는 SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리 경로를 생성해주는 DBMS 내부의 핵심 엔진이다.
옵티마이저가 생성한 SQL 처리 경로를 실행계획(Execution Plan)이라고 부른다.
옵티마이저의 SQL 최적화 과정(비용 기반 옵티마이저)을 요약하면 다음과 같다.
1. 사용자가 던진 쿼리수행을 위해 후보군이 될만한 실행계획을 찾는다.
2. 데이터 딕셔너리(Data Dictionary)에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용하여 각 실행계획의 예상비용을 산정한다.
3. 각 실행계획을 비교해서 최저비용을 갖는 하나를 선택한다.
실행계획(Execution Plan)
실행계획이란 SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미한다.
실행계획을 생성한다는 것은 SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 작업이다.
동일한 SQL에 대해 결과를 낼 수 있는 다양한 처리 방법(실행계획)이 존재할 수 있지만 각 처리 방법마다 실행 시간(성능)은 서로 다를 수 있다.
옵티마이저는 다양한 처리 방법 중에서 가장 효율적인 방법을 찾아준다. 즉 옵티마이저는 최적의 실행 계획을 생성해 준다.
실행계획 정보의 구성요소
- 조인 기법
- 두 개의 테이블을 조인할 때 사용할 수 있는 방법
- NL Join, Hash Join, Sort Merge Join 등
- 액세스 기법
- 하나의 테이블을 액세스할 때 사용할 수 있는 방법
- 인덱스 스캔(Index Scan), 전체 테이블 스캔(Full Table Scan)
- 최적화 정보
- 옵티마이저가 실행계획의 각 단계마다 예상되는 비용 사항을 표시한 것
- Cost, Card, Bytes
- Cost : 상대적인 비용 정보
- Card : Cardinality의 약자로, 주어진 결과를 만족한 결과 집합 혹은 조인 조건을 만족한 결과 집한의 건수
- Bytes : 결과 집합이 차지하는 메모리 양을 바이트로 표시
- 비용 정보는 실제로 SQL을 실행하고 얻은 결과가 아닌, 통계 정보를 바탕으로 옵티마이저가 계산한 예상치이다.
- 만약 비용 사항이 실행계획에 표시되지 않았다면 규칙기반 최적화 방식으로 실행계획을 생성한 것이다.
- 연산
- 여러가지 조작을 통해서 원하는 결과를 얻어내는 일련의 작업
- 조인 기법, 액세스 기법, 필터, 정렬, 집계, 뷰 등 다양하 종류가 존재
- 예를 들어 SQL에서 정렬을 목적으로 ORDER BY를 수행했다면 정렬 연산이 표시
Optimizer Types
-
Rule-Based Optimizer(RBO)
: 규칙 기반 옵티마이저로, 다른 말로 휴리스틱(Heuristic) 옵티마이저라고 불린다.
미리 정해 놓은 규칙에 따라 액세스 경로를 평가하고 실행계획을 선택한다.
여기서 '규칙'이란 액세스 경로별 우선순위로서, 인덱스 구조, 연산자, 조건절 형태가 순위를 결정짓는 주요인이다. -
Cost-Based Optimizer(CBO)
: 비용기반 옵티마이저로, 말 그대로 비용을 기반으로 최적화를 수행한다.
여기서 '비용'이란 쿼리를 수행하는데 소요되는 일량 또는 시간을 뜻한다.
CBO가 실행계획을 수립할 때 판단 기준이 되는 비용은 언제까지나 예상치다.
미리 구해놓은 테이블과 인덱스에 대한 여러 통계정보를 기초로 각 오퍼레이션 단계별 예상 비용을 산정하고, 이를 합산한 총 비용이 가장 낮은 실행계획을 선택한다.
비용을 산정할 때 사용되는 오브젝트 통계 항목으로는 레코드 수, 블록 수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등이 있다.
Oracle은 RBO에서 출발하였으나, 다른 상용 RDBMS는 탄생 초기부터 CBO를 채택하였다.
옵티마이저 행동에 영향을 미치는 요소
- SQL과 연산자 형태
- 같은 결과라도 SQL 작성 형태/사용 연산자에 따라 옵티마이저가 다른 선택을 할 수 있고, 쿼리 성능에 영향을 미친다.
- 옵티마이징 팩터
- 같은 쿼리라도 인덱스, IOT, 클러스터링, 파티셔닝, MV 등의 구성에 따라 실행 계획과 성능이 달라진다.
- DBMS 제약 설정
- DBMS가 제공하는 PK, FK, Check, NOT NULL과 같은 제약 설정 기능을 이용하면 쿼리 최적화하는 데에 매우 중요한 정보를 제공한다.
- 옵티마이저 힌트
- 옵티마이저의 판단보다 사용자가 지정한 옵티마이저 힌트가 더 우선이다.
- 통계 정보
- 통계 정보가 옵티마이저에게 미치는 영향력은 절대적이다.
- 옵티마이저 관련 파라미터
- SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 같더라도 DBMS 버전을 업그레이드하면 옵티마이저가 다르게 동작할 수 있다.
- 이는 옵티마이저 관련 파라미터가 추가/변경 되면서 나타나는 현상이다.
- DBMS 버전과 종류
- 옵티마이저 관련 파라미터가 같더라도 버전에 따라 실행계획이 다를 수 있다.
- 또한 같은 SQL이라도 DBMS 종류에 따라 내부적으로 처리하는 방식이 다를 수 있다.
통계정보를 이용한 비용계산 원리
실행계획을 수립할 때 CBO는 SQL 문장에서 액세스 할 데이터 특성을 고려하기 위해 통계정보를 이용한다.
최적의 실행계획을 위해 통계정보가 항상 데이터 상태를 정확하게 반영하고 있어야 하는 이유이다.
DBMS 버전이 올라갈수록 자동 통계관리 방식으로 바뀌고 있지만 가끔 DB 관리자가 수동으로 수집관리를 해줘야할 때도 있다.
옵티마이저가 참조하는 통계정보 종류는 다음과 같이 4가지가 있다.
- 선택도(Selectivity)
- 카디널리티(Cardinality)
- 히스토그램
- 비용
선택도
선택도는 전체 대상 레코드 중 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율이다.
선택도를 가지고 카디널리티를 구하고, 다시 비용을 구해 인덱스 사용 여부와 조인 순서/방법 등을 결정한다.
선택도는 최적의 실행계획을 수립하는 데 있어 중요한 요인이다.
카디널리티
카디널리티는 특정 액세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수를 의미한다.
카디널리티 = 총 row 수 * 선택도
히스토그램
미리 저장된 히스토그램 정보가 있으면 옵티마이저는 그것을 사용해 더 정확하게 카디널리티를 구할 수 있다.
특히 분표가 균일하지 않은 컬럼을 조회할 때 효과를 발휘한다.
히스토그램에는 다음 두 가지 유형이 있다.
- 값별 빈도수(frequency number)를 저장하는 히스토그램
- 컬럼이 가진 값으 수가 적을 때 사용
- 컬럼 값의 수가 적기 때문에 각각 하나의 버킷을 할당하는 것이 가능 (값의 수 = 버킷 개수)
- 높이균형 히스토그램
- 컬럼이 가진 값의 수가 아주 많아 각각 하나의 버킷을 할당하기 어려울 때 사용
- 히스토그램 버킷을 값의 수보다 적게 할당하기 때문에 하나의 버킷이 여러 개 값을 담당
비용
CBO는 비용(cost)을 기반으로 최적화를 수행하고 실행계획을 생성한다.
여기서 '비용'이란 쿼리를 수행하는 데 소요되는 일량 또는 시간을 뜻하며 어디까지나 예상치이다.
옵티마이저 비용 모델에는 I/O 비용 모델과 CPU 비용 모델 두 가지가 있다.
- I/O 비용 모델
- 예상되는 I/O 요청(call) 횟수만을 쿼리 수행 비용으로 간주해 실행계획을 평가
- CPU 비용 모델
- I/O 비용 모델에 시간 개념을 더해 비용을 산정
Oracle Hint
SQL Server Hint
- 테이블 힌트 : WITH절
- 조인 힌트 : FROM절
- 쿼리 힌트 : OPTION절
