[SQL 튜닝] 옵티마이저(optimizer)

옵티마이저란?

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

개발자가 SQL을 작성하고 실행하면 즉시 실행되는 것이 아니라 옵티마이저에서 해당 쿼리문을 어떻게 실행시킬지 실행계획을 세우고

쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떻게 분포 되어있는지 참조하고 데이터를 기반으로 최적의 실행 계획을 처리해준다.

 

 

옵티마이저 종류

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

 

규칙 기반 옵티마이저 

실행 속도가 빠른 수능로 규칙을 먼저 세워두고 우선순위가 앞서는 방법을 채택하는 것이다. 

과거에 옵티마이저의 비용 예측하는 능력이 좋지 않아 해당 방식을 사용했었다. 

우선순위는 다음과 같다. 

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

 

 

비용 기반 옵티마이저 

최근 많이 사용하고 있는 방식으로 

옵티마이저에서 실행 계획을 세운 뒤 비용이 최소한으로 나온 실행 계획을 수행하는 것이다. 

비용을 예측하기 위해서는 규칙 기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보(CPU 속도, 디스크I/O 속도 등)를 이용하는데 통계 정보가 없으면 비효율적인 실행계획을 생성할 수 있어서 정확한 통계정보를 유지하는 것이 중요하다. 

 

모드	구현 방식
CHOOSE	현재는 잘 사용하지 않는 방식으로  SQL이 실행되는 환경에서 통계 정보를 가져올 수 있으면 비용 기반 옵티마이저,                                           통계 정보를 가져올 수 없으면 규칙 기반 옵티마이저
FIRST_ROWS	옵티마이저가 처리 결과 중 첫 건을 출력하는데 걸리는 시간을 최소화할 수 있는 실행 계획을 세우는 방식
FIRST_ROWS_n	SQL의 실행 결과를 출력하는데까지 걸리는 응답속도를 최적화 하는 방식
ALL_ROWS	SQL 실행 결과 전체를 빠르게 처리하는데 최적화된 실행계획을 세우는 방식