옵티마이저

 옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진을 뜻함. 컴퓨터의 두뇌가 CPU인 것 처럼 DBMS의 두뇌는 옵티마이저라고 할 수 있다. 

 개발자가 SQL을 작성하고 실행하면 소프트웨어 실행파일처럼 즉시 실행되는 것이 아니라 옵티마이저라는 곳에서 여러가지 실행계획을 세운다. 

옵티마이저의 종류

옵티마이저는 실행 계획을 세우는 방식에 따라 규칙 기반 옵티마이저와 비용 기반 옵티마이저로 나뉜다.

항목	규칙 기반 옵티마이저	비용 기반 옵티마이저
개념	사전에 정의된 규칙 기반	최소비용 계산 실행계획 수립
기준	실행우선 순위(Ranking)	액세스 비용(Cost)
인덱스	인덱스 존재 시 가장 우선시 사용	Cost에 의한 결정
성능	사용자 SQL 작성 숙련도	옵티마이저 예측 성능
장점	판단이 매우 규칙적 실행 예상 가능	통계 정보를 통한 현실 요소 적용
단점	예측 통계정보 요소 무시	최소 성능 보장 계획의 예측 제어 어려움
사례	AND 중심 양쪽 ‘=’ 시 Index Merge 사용	AND 중심 양쪽 ‘=’ 시 분포도별 Index 선택

 규칙 기반 옵티마이저(RBO - Rule Base Optimizer)

오라클 8 이하의 버전에서 기본으로 설정된 옵티마이저가 바로 규칙기반 옵티마이저다. 규칙 기반 옵티마이저는  말 그대로 실행 속도가 빠른 순으로 규칙을 먼저 세워두고 우선순위가 앞서는 방법을 채택하는 것이다. 과거에는 옵티마이저의 비용을 예측하는 능력이 그다지 좋지 않아 이러한 방식을 사용하였다. 규칙의 우선순위는 아래와 같다.

순위	설명
1	ROWID를 사용한 단일 행인 경우
2	클러스터 조인에 의한 단일 행인 경우
3	유일하거나 기본키(Primary Key)를 가진 해시 클러스터 키에 의한 단일 행인경우
4	유일하거나 기본키(Primary Key)에 의한 단일 행인 경우
5	클러스터 조인인 경우
6	해시 클러스터 조인인 경우
7	인덱스 클러스터 키인 경우
8	복합 칼럼 인덱스인 경우
9	단일 컬럼 인덱스인 경우
10	인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우
11	인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우
12	정렬-병합(Sort-Merge) 조인인 경우
13	인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우
14	인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우
15	전체 테이블을 스캔(FULL TABLE SCAN)하는 경우

 

비용 기반 옵티마이저

 최근에 많이 사용되고 있는 옵티마이저 방식이며 오라클 10 이후 버전부터는 공식적으로 비용 기반 옵티마이저만 사용한다. 비용 기반 옵티마이저는 옵티마이저에서 실행 계획을 세운 뒤 (최대 2천개까지) 비용이 최소한으로 나온 실행 계획을 수행한다. 비용기반 옵티마이저는 이뵹을 예측하기 위해서 규칙 기반 옵티마이저가 사용하지 않는 테이블, 인텍스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보를 이용한다. 통계정보가 없는 경우 비효율적인 샐행계획을 생성할 수 있으므로, 정확한 통계정보를 유지하는 것이 중요하다. 

 

비용 기반 옵티마이저 모드

비용 기반 옵티마이저는 여러 가지 모드가 있다. 이 모드에 따라 최적의 비용을 구하는 방식이 조금씩 달라진다.

choose: 현재는 잘 사용하지 않지만 SQL이 실행되는 환경에서 통계 정보를 가져올 수 있으면 비용 기반 옵티마이저로 아니라면 규칙 기반 옵티마이저로 작동시키는 모드.

 

first_rows: 옵티마이저가 처리 결과 중 첫 건을 출력하는데 걸리는 시간을 최소화 할 수 있는 실행 계획을 세우는 모드.

 

first_rows_n: SQL의 실행 결과를 출력하는데 까지 걸리는 응답속도를 최적화하는 모드.

 

all_rows: SQL 실행 결과 전체를 빠르게 처리하는데 최적화 된 실행계획을 세우는 모드이다. 마지막으로 출력될 행까지 최소한의 자원을 사용하여 최대한 빨리 가져오게 하며 오라클 10g 이후로는 이 모드가 기본값으로 설정.

 

옵티마이저 동작 방식

Parser : SQL문장을 분석하여 문법 검사와 구성요소를 파악하고 이를 파싱 해서 파싱 트리를 만듭니다.

Query Transformer : 파싱된 SQL을 보고 같은 결과를 도출하되, 좀 더 나은 실행 계획을 갖는 SQL로 변환이 가능한지를 판단하여 변환 작업을 수행한다. 

Estimator : 시스템 통계정보를 딕셔너리로부터 수집하여 SQL을 실행할 때 소요되는 총비용을 계산한다.

Plan Generator : Estimator를 통해 계산된 값들을 토대로 후보군이 되는 실행계획을 도출한다. 

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

SQL Engine : SQL을 실행한다.