[SQL] Optimizer 옵티마이저 란 ?

옵티마이저(Optimizer)

옵티마이저란 가장 효율적인 방법을 통해 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다.

사용자가 구조화된 질의어(SQL)로 결과 집합을 요구하면, 이를 생성하는데 필요한 처리경로는 DBMS에 내장된 옵티마이저가 자동으로

생성해준다. 옵티마이저가 생성한 SQL 처리경로를 실행계획(Excution Plan) 이라고 한다.

 

실행계획(Excution Plan)

동일한 결과가 나오는 SQL 이라도 어떻게 실행하는지에 따라서 성능이 달라질 수 있다.

옵티마이저는 데이터 딕셔너리(Data Dictionary)에 있는 오브젝트 통계,시스템 통계 등의 정보를 사용해서 예상되는

비용을 평가하여 여러 실행계획(Excution Plan) 을 작성한다

옵티마이저는 이들의 비용을 연산해 가장 낮은 비용을 가진 실행계획을 선택한다

 

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

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

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

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

옵티마이저의 종류

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

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

 

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

규칙기반 옵티마이저는 실행 속도가 빠른순으로 규칙을 세워두고 우선순위가 높은 방법을 선택하는 옵티마이저 이다.

여기서 말하는 규칙이란 액세스 경로별 우선순위로서 인덱스의 구조, 연산, 조건절 형태가 순위를 결정짓는 주 요인이다.

규칙의 순서는 표와 같다.

우선순위	설명
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)하는 경우

 

비용 기반 옵티마이저(CBO : Cost - Based Optimizer)

비용 기반 옵티마이저란 말 그대로 비용을 기반으로 최적화를 수행하는 방법이다.

여기서 말하는 '비용'이란 쿼리를 수행하는데 소요되는 일의 량 또는 시간을 뜻한다. CBO는 실행 계획을 최대 2000개 까지 세운 뒤 비용이

최소한으로 나온 실행 계획을 수행 하게 된다. 이때 실행계획을 수립할 때 판단 기준이 되는 비용은 실제 비용이 아니라 예상치 이다.

따라서 CBO는 비용을 예측하기 위해 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보 및 시스템 통계정보(CPU 속도, 디스크 I/O 속도 등)

을 이용한다.

 

 

Reperence

https://code-lab1.tistory.com/137