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