Optimizer

Optimizer
사용자가 요청한 SQL을 가장 효율적이고 빠르게 수행할 수 있는 최적(최저비용)의 처리경로를 선택해 주는 DBMS의 핵심 엔진디ㅏ.
구조화된 질의언어(SQL)로 사용자가 원하는 결과집합을 정의하면 이를 얻는 데 필요한 처리절차(프로시저)는 DBMS에 내장된 옵티마이저가 자동으로 생성해준다.


Optimizer가 특정한 기준으로 최적화된 Access경로를 탐색하는 방법 RBO와 CBO로 나눠지며 10g 부터는 RBO에 대한 지원이 중단됨

- RBO(규칙기반 옵티마이저 - Role Base Optimizer)
미리 정해놓은 우선순위에 따라 엑세스 경로를 평가하고 실행계획을 선택

RBO 우선순위
1 - Single Row by 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
5 - Clustered Join
6 - Hahs Cluster Key
7 - Indesed Cluster Key
8 - Composite Index
9 - Single-Column Index
10 - Bounded Range Search On Indexed Columns
11 - Unbounded Range Search On Indexed Columns
12 - Sore Merge Join
13 - MAX or MIN of Indexed Column
14 - ORDER BY on Indexed Column
15 - Full Table Search

 - CBO(비용기반 옵티마이저 - Cost Base Optimizer)
SQL을 수행함에 있어 필요한 일량 또는 시간을 뜻한다.
수행 일량을 상대적인 시간 개념으로 환산해 비용을 평가하여 비용이 가장 낮은 실행계획을 선택한다.
비용을 산정할때 사용되는 오브젝트의 통계 항목에는 레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 같은 것이 있다.

Optimizer Mode
RULE - RBO를 선택하고자 할 때 사용한다.

ALL_ROWS - 쿼리 최종 결과 집합을 끝까지 Fatch하는 것을 전제로, 시스템 리소스(I/O, CPU, 메모리 등)을 가장 적제 사용하는 실행계획을 선택

FIRST_ROWS - 전체 결과중 일부 Row만 Fetch 하다가 멈추는 것을 전제로, 가장 빠른응답속도를 낼 수 있는 실행계획을 선택한다. 사용자가 끝까지Fatch 한다면 오히려 더 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다.

FIRST_ROWS_N - 사용자가 처음 N개의 로우만 Fatch하는 것을 전제로, 가장 빠른응답속도를 낼 수 있는 실행계획을 선택한다. N으로 저장할 수 있는 값은 1, 10, 100, 1000 네가지 이며 사용자가 지정한 N개의 로우 이상을 Fetch 한다면 오히려 더 많은 리소르를 사용하고 전체 실행속도도 느려질 수 있다.
alter session set optimizer_mode = first_rows_100;
select /*+ first_rows(100) */ from table where  ... ;

CHOOSE - 엑세스되는 테이블 중 적어도 하나에 통계정보가 있다면 CBO, 그중에서도 ALL_ROWS 모드를 선택한다.

Optimizer Mode 선택
일반적인 first_rows는 OLTP, all_rows는 DW나 배치 프로그램 등에서 사용하는 옵티마이저 모드라고 알려져 있다.
하지만 요즘과 같은 웹 어플리케이션 환경에서는 OLTP이더라도 대게 all_rows가 올바른 선택이다.
어플리케이션에서 수행되는 쿼리 자체가 전체범위처를 요구하기 때문이다.
all_rows모드는 sql결과 집합을 모두 Fetch하기 때문에 가장 효율적인 실행계획을 옵티마이저에게 요구하는 것이고, first_rows는 그 중 일부만 Fetch하고 멈추는 것을 전제로 가장 효율적인 실행계획을 요구하는 옵티마이저 모드이다.
어플리케이션 특성상 확실히 first_rows가 적합하다는 판단이 서지 않으면 all_rows를 기본모드로 선택하고, 필요한 쿼리 또는 세션 레벨에서 first_rows 모드로 전환할 것을 권고 한다.

문서에 대하여
최초작성자 : 하봉래
최초작성일 : 2010년 09월 24일
이 문서는오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해버뱌ㅑ'를 참고하였습니다.

# 이 문서는 오라클클럽에서 작성하였습니다.
# 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
# 출처 : http://www.gurubee.net/pages/viewpage.action?pageId=6259368&