[DataBase] 옵티마이저(Optimizer)란 ?

옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다. 컴퓨터의 두뇌가 CPU인 것처럼 DBMS의 두뇌는 옵티마이저라고 할 수 있습니다. 개발자가 SQL을 작성하고 실행하면 소프트웨어 실행파일처럼 즉시 실행되는 것이 아니라 옵티마이저(Optimizer)라는 곳에서 "이 쿼리문을 어떻게 실행시키겠다"라는 여러 가지 실행계획을 세우게 됩니다. 이렇게 실행계획을 세운 뒤 시스템 통계정보를 활용하여 각 실행계획의 예상 비용을 산정한 후 각 실행계획을 비교해서 최고의 효율을 가지고 있는 실행계획을 판별한 후 그 실행계획에 따라 쿼리를 수행하게 되는 것입니다.

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

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

규칙 기반 옵티마이저

오라클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를 통해 계산된 값들을 토대로 후보군이 되는 실행계획을 도출합니다.
• Ros-Source Generator: 옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드나 프로시저 형태로 포맷팅 합니다.
• SQL Engine: SQL을 실행합니다.

이와 같이 옵티마이저는 SQL의 성능 향상에 있어 매우 중요한 영향을 미칩니다. 이러한 옵티마이저를 좀 더 효율적으로 사용해주기 위해서는 여러 가지 내용들을 고려해야합니다.

통계 정보

비용 기반 옵티마이저에서는 실행 계획 도출시 DBMS에서 제공하는 통계 정보를 사용합니다. 이러한 통계정보는 꾸준히 갱신되고 있는 것이 좋습니다. 통계 정보의 생성 주기 및 수행 시간을 스케줄러에 일괄적으로 등록하여 일정 주기마다 통계 정보를 수집하고 종료하도록 자동 통계 정보를 생성할 수도 있고 사용자가 직접 수동으로 통계 정보를 생성할 수도 있습니다. DBMS_STATS 패키지를 사용하면 데이터베이스, 스키마 및 계정, 테이블 인덱스 단위로 구분하여 통계 정보를 수집할 수 있습니다.

주요 통계 정보들

옵티마이저에 영향을 줄 수 있는 파라미터

옵티마이저가 최적의 실행 계획을 결정할 때 파라미터의 값도 매우 중요한 영향을 미칩니다. 예를 들어 OPTIMIZER_MODE는 옵티마이저의 모드를 결정하는 파라미터인데 중간에 옵티마이저 모드가 변경된다면 실행계획이 꼬일 수 있습니다. 이와 같이 옵티마이저에 직접적으로 영향을 줄 수 있는 파리미터들은 개발하고자 하는 방향으로 설계단계에서 최적화로 설정하고 바꾸지 않는 것이 좋습니다.