SQL :: 데이터베이스 옵티마이저(Optimizer)

Database Optimizer 란?

SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 DBMS 내부의 핵심 엔진

 

옵티마이저의 SQL 최적화 과정

1. 사용자가 던진 쿼리 수행을 위하여, 후보군이 될 만한 실행계획을 찾는다.

2. 데이터 딕셔너리(Data Dictionary)에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계 정보를 이용해 각 실행 계획의 예상 비용을 산정한다.

3. 각 실행계획을 비교하여 최저 비용을 갖는 하나를 선택한다.

 

옵티마이저의 필요성

- 작성한 SQL문을 어떤 식으로 실행하느냐에 따라 성능이 달라진다.

- 옵티마이저가 비효율적으로 실행 계획을 수립하면, SQL 개발자는 SQL을 개선해야 한다.

- 옵티마이저에게 실행 계획을 변경하도록 요청할 수 있는데 이때 힌트(HINT)/MSSQL, 옵션(OPTION)을 사용한다.

 

옵티마이저의 종류

항목	규칙 기반 옵티마이저	비용 기반 옵티마이저
개념	사전에 정의된 규칙 기반	최소 비용 계산하여 실행계획 수립
기준	실행 우선 순위(Ranking)	액세스 비용(Cost)
인덱스	인덱스 존재 시 가장 먼저 사용	Cost에 의한 결정
성능	사용자 SQL 작성 숙련도에 따라 달라짐	옵티마이저 예측 성능으로 진행
장점	실행 루트 예상 가능	통계 정보를 통한 현실 요소 적용
단점	예측되는 통계 정보 무시	예측 제어가 어려움

* MSSQL/ORACLE 은 기본 비용 기반 옵티마이저임

 

옵티마이저 엔진

옵티마이저	설명
Query Transformer	SQL문을 효율적으로 실행하기 위해 옵티마이저가 변환
Estimator	통계 정보를 사용하여 SQL 실행 비용을 계산함. 총 비용은 최적 실행 계획을 수립하기 위함이다.
Plan Generator	SQL을 실행할 실행 계획을 수립.

 

옵티마이저 엔진 실행 순위

* 규칙 기반 옵티마이저(RBO) 구현 시 참고

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