[SQL 최적화의 원리]옵티마이저(Optimizer)

옵티마이저(Optimizer)란?

• SQL 개발자가 SQL을 작성하여 실행할 때, 옵티마이저는 SQL을 어떻게 실행할 것인지를 계획하게 된다.
• SQL 실행 계획(Execution Plan)을 수립하고 SQL을 실행한다.
• 옵티마이저는 SQL의 실행 계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어이다.
• 동일한 결과가 나오는 SQL도 어떻게 실행하느냐에 따라서 성능이 달라지기에, SQL 성능에 옵티마이저는 아주 중요한 역할을 한다.

옵티마이저 특징

• 옵티마이저는 데이터 딕셔너리(Data Dictionary)에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용을 산정한다.
• 옵티마이저는 여러 개의 실행 계획 중에서 최저 비용을 가지고 있는 계획을 선택해서 SQL을 실행한다.

옵티마이저의 필요성

• SQL 개발자가 작성한 SQL문을 어떻게 실행하느냐에 따라 성능이 달라진다.

옵티마이저의 실행 방법

• 개발자가 SQL을 실행하면 파싱(Parsing)을 실행해서 SQL의 문법 검사 및 구문 분석을 수행한다.

• 구문 분석이 완료되면, 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립한다.

• 실행 수립이 완료되면, 최종적으로 SQL을 실행하고, 실행이 완료되면 데이터를 인출(Fetch)한다.

• 옵티마이저 엔진

옵티마이저	설명
Query Transformer	- SQL문을 효율적으로 실행하기 위해서 옵티마이저가 변환한다. - SQL이 변환되어도 그 결과는 동일하다.
Estimator	- 통계 정보를 사용해서 SQL 실행 비용을 계산한다 - 총 비용은 최적의 실행 계획을 수립하기 위해서이다.
Plan Generator	SQL을 실행할 실행 계획을 수립한다.

옵티마이저 엔진

• 규칙 기반 옵티마이저(Rule base Optimizer)는 실행 계획을 수립할 때, 15개의 우선 순위를 기준으로 실행 계획을 수립한다.
• 최신 Oracle 버전은 규칙 기반 옵티마이저보다 비용 기반 옵티마이저를 기본적으로 사용한다.
• /*+ RULE */를 사용해서 옵티마이저에서 규칙 기반 옵티마이저로 실행하도록 알려준다.

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

이 게시물은 "SQL 개발자 이론서+기출문제(이기적)"을 참고하여 작성되었습니다. :)