오라클 옵티마이저란 ?

옵티마이저란 사람으로 생각하면 두뇌에 해당하는 부분이다. 옵티마이러를 한마디로 말하면, SQL을 위한 최적의 실행계획을 생성하는 알고리즘이다.
옵티마이저의 종류 크게 두가지이다. 
Rule Base 옵티마이저와 CostBase 옵티마이저이다.

RuleBase 옵티마이저 : SQL Syntax 및 Index의 존재유무 등을 확인하여 실행계획을 생성한다.
Where 조건으로 공급된 커럼의 Index 존재 유무를 확인하여 Index가 존재한다면 해당 Index를 스캔하여 Table을 Access 하고 Index가 존재하지 않을경우 Table Full Scan을 수행하는 방식을 선택한다. 

RuleBase 옵티마이저의 우선순위 
1) ROWID를 통한 Table Access
2) Unique Index를 통한 Table Access
3) Index를 통한 Table Access
4) FullScan을 통한 Table Access

RuleBase 옵티마이저의 장점 : SQL을 작성한 개발자의 의도대로 실행 계획을 생성할 수 있다. 

Cost Base 옵티마이저 : 비용을 산정해서 실행 계획을 생성하는 옵티마이저 방식이다. 해당 Table에 Analyze를 수행하여 통계 데이터를 수집해 놓아야 사용 가느한 옵티마이저 방식이다. 
Cost Base 옵티마이저는 분포도 및 실제 데이터의 통계치를 가지고  실행 계획을 생성하기 때문에 데이터베이스 Migration 등의 작업 후에는 문제를 유발시킬 위험이 많다.

Cost Base 옵티마이저의 종류 :
First Rows : 어떻게 하면 대상 집합 중 첫 번째 Row를 빠르게 추출하냐는것이 목적이다.
예) SELECT * FROM EMP;
All Rows : 빠른 시간내에 원하는 모든 대상 집합을 추출할 수 있디록 실행계획을 생성하는것
JOIN에서 All Rows 방식은 Sort Merge  Join 또는 Hash Join으로 실행계획을 생성하게 된다.
예) SELECT COUNT(*) FROM EMP;


현재 사용하는 DB에 옵티마이저 모드 알기!!

SELECT NAME, VALUE, ISDEFAULT, ISMODIFIED, DESCRIPTION
FROM V$PARAMETER
WHERE NAME LIKE '%optimizer_mode%';

원문: Perfect 오라클 실전튜닝 (권순용님)