[SQLD] 29. 옵티마이저와 실행계획

제 1절 옵티마이저와 실행계획

1. 옵티마이저

  옵티마이저(Optimizer)는 사용자가 질의한 SQL문에 대해 최적의 실행방법을 결정하는 역할을 수행한다. 이러한 최적의 실행방법을 실행계획(Execution Plan)이라고 한다. 다양한 실행 방법 들 중에서 최적의 실행 방법을 결정하는 것이 바로 옵티마이저의 역할이다. 관계형 데이터베이스는 옵티마이저가 결정한 실행 방법대로 실행 엔진이 데이터를 처리하여 결과 데이터를 사용자에게 전달할 뿐이다. 

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

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

  현재 대부분의 관계형 데이터베이스는 비용기반 옵티마이저만을 제공한다. 규칙기반 옵티마이저의 규칙들을 알아두면 보편 타당성에 근거한 것이기 때문에 알아두면 도움이 된다.

가. 규칙기반 옵티마이저

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

- 인덱스유무(유일, 비유일, 단일, 복합 인덱스)

- 연산자(=, <, ...)

- 객체(힙 테이블, 클러스터 테이블)의 세 종류를 이용하여 SQL문을 실행

- 규칙기반 옵티마이저는 우선순위가 높은 규칙이 적은 일량으로 해당 작업을 수행하는 방법

규칙 기반 옵티마이저의 규칙

규칙1. Single row by rowid

- ROWID를 통해서 테이블에서 하나의 행을 엑세스하는 방식

- ROWID는 행이 포함된 데이터파일, 블록 등의 정보를 가지고 있음

- 하나의 행을 액세스 하는 가장 빠른 방법

규칙4. Single row by unique or primary key

- 유일 인덱스를 통해 하나의 행을 엑세스하는 방식

- 인덱스를 먼저 액세스하고 인덱스에 존재하는 ROWID를 추출하여 테이블의 행을 엑세스

규칙8. Composite index

- 복합인덱스에 동등(=) 조건으로 검색하는 경우

- 복합인덱스 사이의 우선순위 규칙

  1. 인덱스 구성 칼럼의 개수가 더 많은 것

  2. 모든 구성 칼럼에 대해 '='로 값이 주어진 것

규칙9. Single column index

- 단일 칼럼 인덱스에 '=' 조건으로 검색하는 경우

규칙 10. Bounded range search on indexed columns

- 인덱스가 생성되어 있는 칼럼에 양쪽 범위를 한저하는 형태로 검색하는 방식

- BETWEEN, LIKE 등

- A BETWEEN '10' AND '20' 또는 A LIKE '1%'

규칙 11. Unbounded range search on indexed columns

- 인덱스가 생성되어 있는 칼럼에 한쪽 범위만 한정하는 형태로 검색하는 방식

- >, <, <=, >= 등

규칙 15. Full table scan

- 전체 테이블을 엑세스 하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출

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

- 조인 칼럼에 대한 인덱스가 양쪽 테이블에 모두 존재할 때, 우선순위가 높은 테이블을 선택

- 조인 칼럼에만 인덱스가 존재하는 경우에는 인덱스가 없는 테이블을 먼저 선택하여 조인 수행

- 만약 조인 테이블의 우선순위가 동일하지않다면, FROM 절에 나열된 테이블의 역순으로 수행

- 양쪽 조인 칼럼에 모두 인덱스가 없는 경우 Sort Merge Join 사용

- 조인 칼럼에 인덱스가 존재한다면 NL Join 사용

나. 비용기반 옵티마이저

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

- SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식

- 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보등을 이용

- 비용기반 옵티마이저의 3가지 모듈 : 질의 변환기, 대안 계획 생성기, 비용 예측기 ★

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

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

- 비용 예측기는 대안 계획 생성기에 의해 생성된 대안 계획의 비용을 예측하는 모듈, 정확한 통계정보를 필요로 함

- 비용기반 옵티마이저는 규칙기반 옵티마이저와는 다르게 비용에 따라 인덱스 스캔, 또는 전체 테이블을 스캔할 수 있음

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

2. 실행계획

  실행계획(Execution Plan)은 요구한 사항을 처리하기 위한 절차와 방법을 의미한다. 실행계획을 생성한다는 것은 SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 작업이다. 옵티마이저는 다양한 처리 방법들 중에서 가장 효율적인 방법을 찾아준다. 즉, 옵티마이저는 최적의 실행 계획을 생성해준다.

  생성된 실행계획을 보는 방법은 데이터베이스 벤더마다 서로 다르다. Oracle의 실행계획 형태는 아래와 같다. 실행계획을 구성하는 요소에는 조인순서, 조인기법, 액세스기법, 최적화정보, 연산 등이 있다.

- 조인 순서 : EMP -> DEPT, NL Join 기법 사용, 인덱스 스캔 기법 사용, 각 최적화 정보 표시

- 논리적으로 가능한 조인 순서는 n!개 만큼 존재 (n은 FROM절에 존재하는 테이블 수)

- NL Join, Hash Join, Sort Merge Join 기법

- 인덱스 스캔, 전체 테이블 스캔 방법

- 최적화 정보 : Cost, Card, Bytes

- 최적화 정보는 통계 정보를 바탕으로 옵티마이저가 계산한 예상치

- 연산에는 조인기법, 엑세스 기법, 필터, 정렬, 집계, 뷰 등 다양한 종류가 존재

3. SQL 처리 흐름도

- SQL 처리 흐름도(Access Flow Diagram)란 SQL의 내부적인 처리 절차를 시각적으로 표현한 도표

- 실행계획을 시각화 해놓은 것

- Outer Table, Driving Table : 조인을 먼저 수행하는 테이블

- Inner Table, Lookup Table : 조인을 나중에 수행하는 테이블

- 랜덤방식 엑세스

- NL Join 수행

- TAB1(테이블 전체 스캔), TAB2(인덱스 스캔)

- 일량 표시 가능 (액세스 건수, 조인 시도 건수, 테이블 액세스 건수, 성공 건수에 표시 가능)