1. 옵티마이저

• 옵티마이저(Optimizer)
• 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할 수행
• 이러한 최적의 실행 방법을 실행계획(Execution Plan)이라 함 

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

 

• 옵티마이저가 최적의 실행 방법을 결정하는 방식
• 규칙기반 옵티마이저(RBO, Rule Based Optimizer) 
• 비용기반 옵티마이저(CBO, Cost Based Optimizer)

 

 

가. 규칙기반 옵티마이저

- 규칙기반 옵티마이저는 규칙(우선 순위)을 가지고 실행계획을 생성

- 실행계획을 생성하는 규칙을 이해하면 누구나 실행계획을 비교적 쉽게 예측 가능

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

 

순위	액세스 기법	설명
1	Single row by rowid	- ROWID는 행이 포함된 데이터 파일, 블록 등의 정보를 가지고 있기 때문에 바로 원하는 행 액세스 가능  - 하나의 행을 액세스하는 가장 빠른 방법
2	Single row by cluster join
3	Single row by hash cluster key with unique or primary key
4	Single row by unique or primary key	인덱스를 먼저 액세스하고 인덱스에 존재하는 ROWID를 추출하여 테이블의 행 액세스
5	Cluster join
6	Hash cluster key
7	Indexed cluster key
8	Composite index	복합 인덱스에 동등(‘=’ 연산자) 조건으로 검색
	* 복합 인덱스 사이의 우선 순위 규칙
9	Single column index	단일 칼럼 인덱스에 ‘=’ 조건으로 검색
10	Bounded range search on indexed columns	- 인덱스가 생성되어 있는 칼럼에 양쪽 범위를 한정하는 형태로 검색  - 해당 연산자 : BETWEEN, LIKE 등 ex) A 칼럼에 인덱스가 생성되어 있고, A BETWEEN ‘10’ AND ‘20’ 또는 A LIKE '1%' 형태로 검색
11	Unbounded range search on indexed columns	- 인덱스가 생성되어 있는 칼럼에 한쪽 범위만 한정하는 형태로 검색  - 해당 연산자 : >, >=, <, <= 등 ex) A 칼럼에 인덱스가 생성되어 있고, A > '10' 또는 A < '20' 형태로 검색
12	Sort merge join
13	MAX or MIN of indexed column
14	ORDER BY on indexed column
15	Full table scan	전체 테이블을 액세스하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출

 

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

       -      이용 가능한 인덱스가 존재한다면 전체 테이블 액세스 방식보다는 항상 인덱스를 사용하는 실행계획 생성

• 조인 순서를 결정 시 조인 칼럼 인덱스의 존재 유무가 중요한 판단의 기준

• 조인 칼럼에 대한 인덱스가 양쪽에 존재  :  우선 순위가 높은 테이블이 선행 테이블(Driving Table)

• 한쪽에만 인덱스 존재  :  인덱스가 없는 테이블이 선행 테이블 (NL Join 사용)

• 모두 인덱스가 존재하지 않을 경우  :  FROM 절의 뒤에 나열된 테이블이 선행 테이블로 선택 (Sort Merge Join 사용)

• 우선 순위가 동일  :  FROM 절에 나열된 테이블의 역순으로 선행 테이블 선택

 

• 규칙기반 옵티마이저의 최적화 과정 예시

 

* EMP_JOB : JOB        * EMP_SAL : SAL * PK_EMP : EMPNO (UNIQUE) 

 

 

 

나. 비용기반 옵티마이저

- 비용(예상되는 소요시간, 자원 사용량)이 가장 적은 실행계획을 선택하는 방식

- 규칙기반 옵티마이저의 단점을 극복하기 위해서 출현

- 다양한 객체 통계정보와 시스템 통계정보 등 이용- 통계정보  없을 경우  :  정확한 비용 예측이 불가능해 비효율적인 실행계획 생성

  (정확한 통계정보 유지하는 것은 비용기반 최적화에 중요한 요소)

[그림 Ⅱ-3-3] 비용기반 옵티마이저는 질의 변환기, 대안 계획 생성기, 비용 예측기 등의 모듈로 구성

* 질의 변환기   :   사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈

* 대안 계획 생성기   :   동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈

                                 - 대안 계획은 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해 생성

                                 - 대안 계획의 생성이 많아지면 최적화를 수행하는 시간이 그만큼 오래 걸린다. 

                                    대부분의 상용 옵티마이저들은 대안 계획의 수를 제약하는 다양한 방법 사용

                                    대안 계획들 중에서 최적의 대안 계획이 포함되지 않을 수도 있다. 

* 비용 예측기   :   생성된 대안 계획의 비용을 예측하는 모듈

                                 - 연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측이 정확해야 한다. 

                                 - 정확한 통계정보, 대안 계획을 구성하는 각 연산에 대한 비용 계산식이 정확 

규칙기반 옵티마이저

- 항상 인덱스를 사용할 수 있다면 전체 테이블 스캔 보다는 인덱스를 사용하는 실행계획을 생성한다고 했다. 

 

비용기반 옵티마이저 

- 인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 전체 테이블 스캔을 수행하는 방법으로 실행계획을 생성할 수도 있다. 

- 통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 인해 동일 SQL문도 서로 다른 실행계획이 생성될 수 있다. 

- 또한 비용기반 옵티마이저의 다양한 한계들로 인해 실행계획의 예측 및 제어가 어렵다.

 

2. 실행계획

- SQL에서 요구한 사항을 처리하기 위한 절차와 방법

- 다양한 처리 방법(실행계획)마다 실행 시간(성능)은 서로 다를 수 있다 (옵티마이저는 최적의 실행계획 생성)

 

- 실행계획 구성 요소

l  조인 순서(Join Order)

l  조인 기법(Join Method)

l  액세스 기법(Access Method)

l  최적화 정보(Optimization Information) : 실행계획의 각 단계마다 예상되는 비용 사항 표시

 * Cost : 상대적인 비용 정보

   Card : Cardinality의 약자. 결과집합의 건수

   Bytes : 결과집합이 차지하는 메모리 양의 바이트

 

* 비용정보 : 실제 SQL 실행하고 얻은 결과가 아니라 통계 정보 바탕으로 계산한 예상치 

l  연산(Operation) : 여러 조작을 통해 원하는 결과를 얻어내는 일련의 작업    

 

3. SQL 처리 흐름도

- SQL의 내부적인 처리 절차를 시각적으로 표현한 도표(실행계획을 시각화)

- 조인 순서, 액세스 기법과 조인 기법 등을 표현 가능

l  SELECT ...

    FROM TAB1 A, TAB2 B

    WHERE A.KEY = B.KEY

    AND A.COL1 = :condition1

    AND B.COL2 = :condition2 

    ;

 

                 * 액세스 건수 :  TAB1을 액세스한 건수

                                             A.COL1 관련 인덱스가 없어서 전체 테이블 스캔 수행을 의미(액세스 건수 = TAB1테이블 총 건수)

                 * 조인 시도 건수 : TAB1에서 읽은 건에 대해 A.COL1 = :condition1 조건을 만족한 건수

                 * 테이블 액세스 건수 : B.KEY 로 구성된 인덱스인 I01_TAB2 에서 즉, 조인 시도한 건들 중 B.KEY = A.KEY 조건을

                                                         만족한 건수

                                                         

                 * 성공 건수 : 결과 건수로 TAB2  에서 B.COL2 = :condition2 조건에 만족하는 건수