옵티마이저(홍진택 주무관님)

[데이터베이스 목차]

 

I.  최적 SQL 수행 위한, 옵티마이저(Optimzer)의 정의

   가. 옵티마이저 정의

        - 사용자 SQL질의의 처리 가능 실행계획 탐색, 비용 산정, 최적의 실행 계획 수립하는 DBMS 핵심 엔진

 

   나. 옵티마이저 주요 기능

기능	설명
실행 계획 탐색 (Search Space Enumeration)	- 주어진 SQL 질의를 처리할 수 있는 실행 계획들을 나열(P1, .... Pn)
비용 산정 (Cost Estimation)	- 각 실행 계획의 예상 비용을 계산하여 가장 비용이 적게 드는 실행 계획 Pi를 선택해서 SQL을 실행하고 결과를 사용자에게 제공

   다. 옵티마이저 처리 절차

질의 처리 단계	주요 기능	설명
1). Query Rewrite	질의 변환기	- 옵티마이저가 더욱 효과적인 QEP를 찾기 위하여 더 효과적인 플랜이 있는지 그 가능성을 확인하는 과정
2). Query Optmization	비용 산정기	- 질의에 대한 엑세스 경로를 결정(규칙 기반 / 비용 기반 옵티마이저 사용)
3). QEP Generation	실행 계획 생성기	- 질의 실행 계획(Query Execution Plan)    : 질의를 실행하는데 필요한 상세한 정보를 생성.

 

II. 규칙 기반 옵티마이저, RBO(Rule Based Optimzer)  

항목	설명
개념	- 인덱스 구조나 비교연산자에 따른 순위여부를 기준으로 최적의 경로를 설정하는 규칙 기반 옵티마이저
특징	- 판단이 규칙적이고 분명하여 사용자가 정확히 예측    통계정보라는 현실요소를 무시하여 판단의 오차가 크게 날 수도 있음.
우선순위	Path 1: Single Row by ROWID Path 2: Single Row by Cluster Join Path 3: Single Row by hash Cluster Key with Unique or Primary Key Path 4: Single Row by Unique or Primary Key Path 5: Clustered Join Path 6: Hash Cluster Key Path 7: Indexed Cluster Key Path 8: Composite Index Path 9: Single-Column Indexes Path 10: Bounded Range Search on Indexed Columns Path 11: Unbounded Range Search on Indexed Columns Path 12: Sort_Merge Join Path 13: MAX or MIN of Indexed Column Path 14: ORDER BY on Indexed Column Path 15: Full Table Scan
예 시	Emp 테이블에 ‘A’ 인덱스가 “deptno”로 구성, ‘B’ 인덱스가 “deptno+empno”로 구성시 다음의 SQL은 Bounded Range Search(Between)이므로 ‘A” 인덱스를 사용. 즉 ’A’인덱스는 Rank 9이고 ‘B’인덱스는 Rank 10 조건이 됨 SELECT /*+ Rule */ from emp where deptno = 10 and empno between 7888 and 8888;

III. 비용 기반 옵티마이저, CBO(Cost Based Optimzer) 개념, 처리과정 

가. 비용 기반 옵티마이저 개념

항목	설명
개념	- 처리 방법들에 대한 비용을 산정해 보고 그 중, 가장 적은 비용이 들어가는 처리방법을 선택하는 옵티마이저
특징	- 현실을 감안한 판단, 통계 정보의 관리를 통해 최적화 제어, 옵티마이저를 깊이 이해하고 있지 않더라도 최소 성능을 보장하는 실행 계획을 미리 예측
원리	시스템 통계정보(CPU, 디스크 엑세스 타임을 이용하여 처리시간으로 환산한 방식)을 사용 SQL문장에 대한 여러가지 경우의 수 별로 처리시간에 비례한 비용을 산출해 내고 이들 비용에서 가장 작은 비용을 갖는 플랜을 결정
예 시	SELECT * FROM dept WHERE deptno= 10의 SQL 에서 dept테이블은 deptno에 대한 인덱스가 있 고 테이블은 전체 10블록으로 구성되어 있으며 풀 테이블 스캔일 경우 I/O 단위를 결정하는 파 라미터가 db_file_multiblock_read_count=8 이라면 RBO는 무조건 인덱스를 타는 플랜을 결정하지만 CBO는 deptno의 인덱스를 이용해 실행하면 3회의 I/O가 발생한다고 가정하고 풀 테이블 스 캔의 경우 2회(8블고+2블고)의 I/O가 발생한다면 인덱스가 있음에도 풀테이블 스캔을 선택함

나. 비용 기반 옵티마이저 처리 과정

    - CBO는 옵티마이저의 통계 정보를 활용한 비용(Cost) 계산을 통해 최적 실행계획 도출

 

다. CBO 기반 SQL 최적화 수행 과정 구성요소

수행 과정	구성 요소	설명
1). SQL 파싱 과정	SQL Parser	- Parsing Tree 생성, SQL 문장 개별 구성요소 분석, - 문번적 오류(Syntax), 의미상 오류(Semantic) 확인
2). Optimization 과정	2-1). Query Transformer(쿼리 변환)	- Parsed SQL 표준(Canonical) 형태 변환  ex) 서브쿼리, 뷰 -> Join 형태로 변경
	2-2). Estimator(비용산출)	- Dictionary 통계활용, 비용(Cost) 산출 - 선택도(Selectivity), 카디널리티(Cardinality) 계산
	2.3). Plan Generator(실행 계획 생성)	- 실행 계획 후보군 산출 - Query + Estimates 기반 최종 실행 계획 선정
3). Row-Source Generation과정	Row-Source Generation	- 옵티마이저 실행계획 포맷팅  - SQL 엔진 실행가능 코드(or 프로시저) 변환
4). Execution 과정	SQL Engine	- Row-Source 형태의 실행계획 실행  - 쿼리 실행 결과 제공

- CBO 참조 통계정보는 테이블 통계, 인덱스 통계, 컬럼 통계, 시스템 통계 존재함