옵티마이저(규칙기반 옵티마이저, 비용기반 옵티마이저)

옵티마이저(Optimizer)

사용자가 질의한 SQL문에 대해 최적의 실행 방법(실행계획)을 결정하는 역할.

 

- 최적의 실행 방법 결정

: 어떤 방법으로 처리하는 것이 최소 일량으로 동일한 일을 처리할 수 있을지 결정하는 것.

옵티마이저가 최적의 실행 방법을 결정하는 방식에 따라 규칙기반 옵티마이저, 비용기반 옵티마이저로 구분할 수 있다.

 

1) 규칙기반 옵티마이저

규칙(우선 순위)을 가지고 실행계획을 생성.

→ 참조하는 정보: 인덱스 유무, 연산자의 종류, SQL에서 참조하는 객체의 종류 등

 

- Oracle 규칙기반 옵티마이저의 우선순위 규칙 - 순위의 숫자가 낮을 수록 높은 우선순위.

순위	액세스 기법
1	ROWID를 사용한 단일 행
2	클러스터 조인에 의한 단일 행
3	UNIQUE 또는 PRIMARY KEY를 가진 해시 클러스터 키에 의한 단일 행
4	UNIQUE 또는 PRIMARY KEY에 의한 단일 행
5	클러스터 조인
6	해시 클러스터 키
7	인덱스 클러스터 키
8	복합 칼럼 인덱스
9	단일 칼럼 인덱스
10	인덱스가 구성된 칼럼에서 제한된 범위 검색
11	인덱스가 구성된 칼럼에서 제한되지 않은 범위 검색
12	Sort merge 조인
13	인덱스가 구성된 칼럼에서 MAX 또는 MIN을 구하는 경우
14	인덱스가 구성된 칼럼에서 ORDER BY를 하는 경우
15	전체 테이블 스캔

인덱스를 이용한 엑세스 방식이 전체 테이블 엑세스 방식보다 우선 순위가 높다.

→ SQL문에서 이용 가능한 인덱스가 존재한다면, 전체 테이블 엑세스 방식 보다는 항상 인덱스를 사용하는 실행 계획이 우선이다.

결과적으로 규칙기반 옵티마이저는 우선순위가 높은 규칙이 적은 일량으로 해당 작업을 수행하는 방법이라고 판단하는 것이다.

2) 비용기반 옵티마이저

SQL문을 처리하는데 필요한 예상 소요시간 또는 자원 사용량이 가장 적은 실행계획을 선택.

→ 참조하는 정보: 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보 등

비용기반 옵티마이저는 비용을 예측하기 위해 규칙기반 옵티마이저가 사용하지 않는 정보들을 이용함.

 

- 비용기반 옵티마이저 구성 요소

질의 변환기
: 사용자가 작성한 SQL문을 처리하기 용이한 형태로 변환하는 모듈.
대안 계획 생성기
: 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈.
→ 연산 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등
비용 예측기
: 대안 계획의 비용을 예측하는 모듈.

+) 비용기반 옵티마이저는 통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 인해 동일 SQL문도 서로 다른 실행계획이 생성될 수 있다.

 

3) 규칙기반 옵티마이저 VS 비용기반 옵티마이저

- 규칙기반 옵티마이저: 인덱스를 사용할 수 있으면 인덱스를 사용하는 실행계획 생성.

- 비용기반 옵티마이저: 인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크면 전체 테이블 스캔을 수행하는 방법으로 실행계획 생성.