옵티마이저

I. 옵티마이저

-. 사용자 요청 SQL문 처리를 위한 실행계획을 탐색하고, 각 실행 계획에 대한 비용을 추정하여 최적의 실행계획을 수립하는 DBMS의 성능튜닝엔진

-. 주요기능: 실행 계획탐색, 비용 산정

 

II. 옵티마이저 처리 절차 및 기능

가. 옵티마이저 처리 절차 

나. 옵티마이저 기능

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

III. RBO, CBO, Hint

가. RBO

구분	설명
개념	사전에 실행할SQL문의 순서를 규칙으로 설정하여 그 규칙에 따라SQL문 실행 순서를 결정하는 옵티마이저 조건문의 syntax에 따라 조건범위(Selectivity)를 미리 결정해 놓고 이를 기반으로 실행계획을 수립  인덱스구조나 비교연산자에 따른 우선순위를 기준으로 최적의 경로를설정하는 옵티마이저
특징	판단이 매우 규칙적이고 분명하여 사용자가 정확히 예측
RBO의 Rule (우선순위)	번호가 낮을수록 좋은 성능을 내는 조건이기 때문에 우선순위가 높게 부여됨 01. ROWID에 의한 단일 행 액세스 02. Cluster Join에 의한 단일 행 액세스 03. Unique-Key, Primary-Key를 사용한HASH Cluster Key에 의한 단일 행 액세스 04. Unique-Key, Primary-Key에 의해 생성된Unique INDEX에 의한 단일 행 액세스 05. CLUSTER 조인 06. Non Unique HASH Cluster Key 07. Non Unique Cluster Key(인덱스화된Cluster-Key) 08. NON UNIQUE 결합 인덱스 09. 단일 칼럼 인덱스에 의한 동등 검색 10. 인덱스가 구축된 칼럼에 대한 제한된 범위 검색(BETWEEN, LIKE, < AND >, = 표현식) 11. 인덱스가 구축된 칼럼에 대한 무제한 범위의 검색(>=, =<표현식) 12. 정렬-병합 조인 13. 인덱스가 구축된 칼럼에 대한MAX, MIN 14. 인덱스가 구축된 칼럼에 대한ORDER BY 15. 전체 테이블 스캔
예시	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;

나. CBO

구분	설명
개념	해당 데이터베이스의 통계정보를 수집하여 통계 정보를 기준으로SQL의 실행 계획을 생성하는 옵티마이저 처리방법들에 대한 비용을 산정해보고 그 중에서 가장 적은 비용이 들어가는 처리방법을 선택하는 옵티마이저
특징	현실을 감안한 판단, 통계정보의 관리를 통한 최적화 제어, 옵티마이저를 깊이 이해하고 있지 않더라도 최소 성능 보장 실행계획을 미리 예측, 제어가 어려움
원리	여러가지 경우의 처리시간에 비례한 비용을 산출해 내고, 이들 비용에서 가장 작은 비용을 갖는 플랜을 결정 실행계획에 대한 소요시간을 구하기 위해서 데이터 분포도 및 테이블, 인덱스와 클러스터의 저장 공간의 특징 등의 통계 정보를 이용하여 비용을 계산하여 선택하는 방법 특정 실행계획을 통한SQL 명령문을 실행하는데 필요한 메모리, 디스크I/O, CPU 시간을 계산
예시	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가 발생한다면 인덱스가 있음에도 풀테이블 스캔을 선택함

다. Hint

구분	설명
개념	select, update, delete 문 사용 시 옵티마이져의 파싱을 원하는 쪽으로 유도할 때 사용
특징	연산을 위한 비용 기반(Cost-Based) 접근 방식을 선택하여 접근 방식 지정 인덱스(Index)보다 더 효과적인 스캔 방법 조인(Join) 순서, 병렬연산 등급, 조인(Join) 연산 등
힌트 (Oracle DBMS  기준)	RULE : 규칙기반(RULE_BASED) 옵티마이저 사용 FIRST_ROWS : 첫째 레코드의 추출 시간을 최소화 할 목적으로 최적화 ALL_ROWS : 모든 레코드를 모두 처리하는 시간의 최소화를 목적으로 최적화 FULL : 지정된 테이블에 대한 전체스캔 USE_NL : 먼저 특정 테이블의 로우를 액세스하고 그 값에 해당하는 다른 테이블의 로우를 찾는 작업을 해당 범위까지 실행하는 조인 CLUSTER : 지정된 테이블에 대한 클러스터 스캔 HASH : 지정된 테이블에 대한 해시스캔 INDEX_ASC : 내림차순으로 인덱스 스캔 HASH-AJ : 부정형의 조인을HASH JOIN 방식 INDEX_DESC : 오름차순으로 인덱스 스캔 AND_EQUALS : 여러 개의 인덱스를 머지(MERGE)하여 사용 ORDERED : FROM절에 기술된 테이블순으로 조인 ROWID : 지정된 테이블에 대한ROWID에의한테이블스캔 USE_MERGE : 먼저 각각의 테이블의 처리범위를 스캔하여SORT한후서로MERGE하면서조인하는방식 PARALLEL : 병렬처리 프로세서의 개수를 지정 CACHE : 테이블의 행들을 메모리 내에 상주 NOCACHE : 메모리 내에 상주시킨 옵션의 해제 MERGE-AJ : 부정형의 조인을SORT MERGE 방식

라. RBO, CBO 비교

구분	RBO	CBO
핵심	사전에 정의된 Rule 기반 계획	최소 비용 계산 실행 계획 수립
기준	실행 우선 순위(Ranking)	액세스 비용(Cost)
성능	SQL 작성 숙련도	Optimizer 예측 성능
특징	실행계획 예측 용이	저장된 통계 정보 활용
고려사항	저효율, 사용자의 규칙 이해도 종속	예측 복잡, 비용 산출 공식 정확성 모호