옵티마이저(Optimizer)와 실행계획(Execution Plan)

옵티마이저란?

 

옵티마이저의 종류

1) 옵티마이저(Optimizer)는 SQL문의 요구사항을 처리하기 위한 최적의 실행 방법을 결정하는 역할을 한다.

→ 여기서 최적의 실행방법이란 것은 한 마디로 '실행계획'이라고 할 수 있다.

2) 옵티마이저는 두가지 방식(규칙기반, 비용기반)에 따라 구분된다.

3) 현재 대부분의 관계형 데이터베이스는 비용기반 옵티마이저(Cost Based Optimizer)만을 제공한다. 비록 규칙기반 옵티마이저를 제공하더라도 신규 기능들에 대해서는 더 이상 지원하지 않는다. 다만 하위 버전 호환성을 위해서만 규칙기반 옵티마이저가 남아 있을 뿐이다. 하지만 규칙기반 옵티마이저의 규칙은 보편 타당성에 근거한 것들이기 때문에, 이러한 규칙을 알고 있는 것은 옵티마이저의 최적화 작업을 이해하는데 도움이 된다.

 

 

규칙기반 옵티마이저(RBO, Rule Based Optimizer) 특징

1) 규칙기반 옵티마이저(Rule Based Optimizer)는 말그대로 규칙(우선순위)을 가지고 실행계획을 생성한다.

2) 규칙기반 옵티마이저에서는 'rowid를 통한 엑세스 방식'이 우선순위가 가장 높고 '전체 테이블 엑세스 방식'이 우선순위가 가장 낮다.

3) Index를 이용한 액세스 방식이 전체 테이블 엑세스 방식보다 우선순위 높다.

4) 조인 칼럼 인덱스의 존재 유무가 중요한 판단 기준이 된다. 아래 표는 조인 칼럼 인덱스의 유무에 따라 RBO가 선행 테이블을 선택하는 기준이다.

인덱스로 인한 실행계획 판단 기준	선행 테이블 결정 방법
조인 칼럼에 대한 Index가 양쪽 테이블에 모두 존재하는 경우	우선 순위가 높은 테이블을 선행 테이블(Driving Table 또는 Outer Table)로 선택한다.
한쪽 조인 칼럼에만 Index가 존재하는 경우	인덱스가 없는 테이블을 선행 테이블로 선택해서 조인을 수행한다.
양쪽 조인 칼럼에 모두 Index가 존재하지 않는 경우	① FROM 절의 뒤에 나열된 테이블을 선행 테이블로 선택.  ② 이때는 보통 Sort Merge Join을 사용.  ( ↔ 일반적으로 둘 중 하나라도 조인 칼럼에 인덱스가 존재한다면 일반적으로 NL Join을 사용한다)
조인 테이블의 우선 순위가 동일한 경우	FROM 절에 나열된 테이블의 역순으로 선행 테이블을 선택한다.

 

 

비용기반 옵티마이저(CBO, Cost Based Optimizer) 특징

1) 비용기반 옵티마이저는 SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다. 즉, 우선순위와는 상관없다.

 → 예를 들어, 아무리 Index를 이용한다고해도 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 전체 테이블 스캔을 실행계획으로 생성한다.

2)따라서, 규칙기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보 등을 이용한다. (비용기반 옵티마이저는 통계정보가 없는 경우, 정확한 비용 예측이 불가능하기 때문)

3) 동일한 SQL문이라도 DBMS정보, 설정 정보 등의 차이로 서로 다른 실행계획이 생성될 수 있다.

4) 다양한 한계들로 인해 실행계획의 예측 및 제어가 어렵다는 단점이 있다.

5) 비용기반 옵티마이저의 실행계획에는 반드시 비용사항이 표시된다. 안 돼있다면 그것은 규칙기반 옵티마이저(RBO).

 

 

옵티마이저가 생성한 실행계획 정보의 구성요소

실행계획을 보고 SQL이 어떻게 실행되는지 정확히 이해할 수 있다면 보다 향상된 SQL의 이해 및 활용이 가능하다. 실행계획을 구성하는 요소에는 조인 순서(Join Order), 조인 기법(Join Method), 액세스 기법(Access Method), 최적화 정보(Optimization Information), 연산(Operation) 등이 있다.

실행계획 정보의 구성요소

1) 조인 순서

조인 작업을 위해 먼저 A 테이블을 읽고 B 테이블을 읽는 작업을 수행한다면 조인 순서는 A → B.

 

2) 조인 기법(두 개의 테이블을 조인할 때 사용할 수 있는 방법) 종류

 ① NL Join(NESTED LOOPS) : 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인을 수행하는 기법.

 ② Sort Merge Join : 조인 칼럼을 기준으로 데이터를 정렬하여 조인을 수행하는 기법

 ③ Hash Join : 서로 동일한 해쉬 값을 갖는 것들 사이에서 실제 값이 같은지를 비교하면서 조인을 수행하는 기법.

 

3) 액세스 기법(하나의 테이블을 액세스할 때 사용할 수 있는 방법) 종류

 - 인덱스 스캔(Index Scan), 전체 테이블 스캔(Full Table Scan) 등.

 

4) 최적화 정보

 - 최적화 정보는 옵티마이저가 실행계획의 각 단계마다 예상되는 비용 사항을 표시한 것이다.

 - 실행계획에 비용 사항이 표시된다는 것은 비용기반 최적화 방식으로 실행계획을 생성했다는 것을 의미한다.

 - 최적화 정보에는 3가지 정보가 존재한다. → 실제로 SQL을 실행하고 얻은 결과가 아니라 통계 정보를 바탕으로 옵티마이저가 계산한 예상치이다.

  ① Cost: 상대적인 비용 정보.

  ② Card: Cardinality의 약자로서, 주어진 조건을 만족한 결과 집합 혹은 조인 조건을 만족한 결과 집합의 건수를 의미

  ③ Bytes: 결과 집합이 차지하는 메모리 양을 바이트로 표시한 것.