01. 옵티마이저

(1) 옵티마이저란?

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

 
옵티마이저에는 크게 두 가지가 있다.

• 규칙기반 옵티마이저(Rule-Based Optimizer, 이하 RBO)
• 비용기반 옵티마이저(Cost-Based Optimizer, 이하 CBO)

(2) 규칙기반 옵티마이저

규칙기반 옵티마이저(RBO)는 다른 말로 `휴리스틱(Heuristic)옵티마이저`라고 불리며, 미리 정해놓은 우선순위에 따라 액세스 경로(Access Path)를 평가하고 실행계획을 선택한다.
아래 표는 RBO가 사용하는 규칙(액세스 경로별 우선순위)인데, 인덱스 구조, 연산자, 조건절 형태가순위를 결정짓는 주요인임을 알 수 있다.

 
OLTP 환경의 중소형 데이터베이스 시스템이라면 RBO 규칙이 어느 정도 보편 타당성을 갖는다.
하지만 데이터량, 값의 수(number of distinct value), 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터같은 데이터 특성을 고려하지 않기 때문에 RBO는 대용량 데이터를 처리하는 데 있어 합리적이지 못할때가 많다.
예를 들어, 조건절 컬럼에 인덱스가 있으면 무조건 인덱스를 사용한다.
항상 인덱스를 신뢰하며, Full Table Scan과의 손익을 따지지 않는다.(인덱스를 경유하면서 액세스할 데이터량이 일정 수준 이상이면 오히려 Full Table Scan이 유리하다는 것은 일반적인 상식이지만 RBO는 그런 사실을 외면한다.)
또 다른 예로, 아래와 같은 문장을 수행할 때도 empno 컬럼에 인덱스가 있으면 무조건 그 인덱스를 이용해 sort order by 연산을 대체한다.
부분범위처리가 불가능한 상황이라면 Full Table Scan 하고 나서 정렬하는 편이 낳은데도 RBO 우선순위로는 인덱스 컬럼에 의한 order by(14위)가 Full Table Scan(15위)보다 한 단계 높아서 그런 선택을 하는 것이다.

SYS@ora10g>select /*+ rule */ * from scott.emp order by empno ;

--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP        |
|   2 |   INDEX FULL SCAN           | PK_EMP_IDX |
--------------------------------------------------
Note
-----
   - rule based optimizer used (consider using cbo)

RBO는 이처럼 예측 가능하고 일관성 있는 실행계획을 수립하며 사용자가 원하는 처리경로로 유도하기가 쉽다.
그에 반해 CBO는 같은 SQL이더라도 데이터 특성에 따라 실행계획이 달라지고 복잡한 비용 원리를 내포하고 있어 이를 정확히 이해하지 못한다면 제어가 쉽지 않다.

(3) 비용기반 옵티마이저

비용기반 옵티마이저는 말 그대로 비용을 기반으로 최적화를 수행한다.
여기서 `비용(Cost)`이란, 쿼리를 수행하는데 소요되는 일량 또는 시간을 뜻한다.
전통적인 I/O 비용 모델에서는 I/O 요청(Call) 횟수만을 비용으로 평가했지만, 최근 도입된 CPU 비용 모델에서는 CPU 연산 비용까지 감안한다.
그리고 수행 일량을 상대적인 시간 개념으로 환산해 비용을 평가한다.
CBO가 실행계획을 수립할 때 판단 기준이 되는 비용은 어디까지나 예상치다.
미리 구해놓은 테이블과 인덱스에 대한 여러 통계정보를 기초로 각 오퍼레이션 단계별 예상 비용을 산정하고, 이를 합산한 총 비용이 가장 낮은 실행계획 하나를 선택한다.
비용을 산정할 때 사용되는 오브젝트 통계 항목에는 레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 같은 것들이 잇다.
옵티마이저의 최적화 수행단계를 요약하면 다음과 같다.
1. 사용자가 던진 쿼리수행을 위해, 후보군이 될만한 실행계획을 찾는다.
2. 데이터 딕셔너리(Data Dictionary)에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
3. 각 실행계획의 비용을 비교해서 최저비용을 갖는 하나를 선택한다.

 
 

아래 그림은 CBO를 기준으로 SQL 처리 절차를 요약한 것이다.

 

• 1. Query Transformer : 사용자가 던진 SQL을 그대로 최적화하는 것이 아니라 우선 최적화하기 쉬운 형태로 변환을 시도한다.
  물론 쿼리 변환 전후 결과가 동일함이 보장될 때만 그렇게 한다.
• 2. Estimator : 쿼리 오퍼레이션 각 단계의 선택도(Selectivity), 카디널리티(Cardinality), 비용(Cost)을 계산하고, 궁극적으로는 실행계획 전체에 대한 총 비용을 계산한다.
• 3. Plan Generator : 하나의 쿼리를 수행하는 데 있어, 후보군이 될만한 실행계획들을 생성해내는 역할을 한다.

(4) 옵티마이저 모드

옵티마이저 모드로 선택할 수 있는 값으로는 아래 5가지가 있고, 시스템 레벨, 세션 레벨, 쿼리 레벨에서 바꿀 수 있다.

• rule
• all_rows
• first_rows
• first_rows_n
• choose

alter system set optimizer_mode = all_rows ;  -- 시스템 레벨 변경
alter session set optimizer_mode = all_rows ; -- 세션 레벨 변경
select /*+ all_rows */ * from t where ... ;   -- 쿼리 레벨 변경

RULE

RBO 모드를 선택하고자 할 때 사용한다.

ALL_ROWS

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

FIRST_ROWS

전체 결과집합 중 일부 로우만 Fetch하다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.
사용자가 만약 끝까지 Fetch한다면 오히려 더 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다.
first_rows는 비용과 규칙(=휴리스틱)을 혼합한 형태의 옵티마이저 모드다.
얼마만큼을 Fetch할지 지정하지 않았으므로 정확한 비용을 예측할 수 없고, 따라서 옵티마이저는 내부적으로 정해진 규칙을 사용한다.

실제 사례를 보면서 first_rows 모드일 때의 CBO 작동원리를 이해해 보자.

SYS@ora10g>create table t_emp
  2  as 
  3  select * from scott.emp, (select rownum no from dual connect by level <= 1000)
  4  order by dbms_random.value ;

Table created.

SYS@ora10g>alter table t_emp add constraint t_emp_pk primary key(empno, no) ;

Table altered.

SYS@ora10g>begin
  2  dbms_stats.gather_table_stats(
  3  ownname => user
  4  , tabname => 't_emp'
  5  , method_opt => 'for columns sal') ;
  6  end;  
  7  /

PL/SQL procedure successfully completed.

SYS@ora10g>set autotrace traceonly exp
SYS@ora10g>select /*+ all_rows */ * from t_emp
  2  where sal >= 5000
  3  order by empno, no ;

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |   999 | 40959 |    25   (8)| 00:00:01 |
|   1 |  SORT ORDER BY     |       |   999 | 40959 |    25   (8)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T_EMP |   999 | 40959 |    24   (5)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SAL">=5000)

위에서는 all_rows 모드이므로 Table Full Scan하고 나서 소트 연산을 수행하는 실행계획이 수립되었다.
first_rows로 바꾸고 다시 수행해 보자.

SYS@ora10g>select /*+ first_rows */ * from t_emp
  2    where sal >= 5000
  3    order by empno, no ;

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   999 | 40959 | 14903   (1)| 00:02:59 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_EMP    |   999 | 40959 | 14903   (1)| 00:02:59 |
|   2 |   INDEX FULL SCAN           | T_EMP_PK | 15000 |       |    39   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL">=5000)

같은 SQL이지만 first_rows 모드로 바꾸자 order by 컬럼 순으로 정렬된 PK 인덱스를 사용하는 실행계획이 수립되었다.
이 실행계획을 수립하는 데에는 비용보다 규칙이 우선시되었다.

아래 쿼리 결과에서 보듯 최종 결과집합에 해당하는 레코드 비율은 7%밖에 되지 않는다.
따라서 첫 번째 Fetch 분량을 얻기까지 많은 인덱스 스캔과 테이블 액세스를 수반하므로 first_rows가 지향하는 최초 응답속도도 생각만큼 좋지 않을 수 있다.

SYS@ora10g>set autotrace off
SYS@ora10g>select count(*) all_emp
  2    , count(case when sal >= 5000 then 1 end) over_5000
  3    , round(count(case when sal >= 5000 then 1 end) / count(*) * 100) ratio
  4    from t_emp ;


   ALL_EMP  OVER_5000      RATIO
---------- ---------- ----------
     15000       1000          7

규칙의 영향을 받긴 하지만 first_rows도 CBO 모드이기 때문에 통계정보를 활용한다.
예를 들어, 위에서 컬럼 히스토그램을 생성했으므로 sal >= 5001인 사원이 없다는 사실만큼은 옵티마이저도 알 수 있다.
따라서 이 조건절을 사용하면 실행계획도 아래와 같이 바뀐다.

SYS@ora10g>set autotrace traceonly exp
SYS@ora10g>select /*+ first_rows */ * from t_emp
  2  where sal >= 5001
  3  order by empno, no ;

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    41 |    25   (8)| 00:00:01 |
|   1 |  SORT ORDER BY     |       |     1 |    41 |    25   (8)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T_EMP |     1 |    41 |    24   (5)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SAL">=5001)

RBO와의 차이점이 바로 여기에 있다.
같은 조건을 주고 RBO 모드로 실행해 보면 아래와 같이 여전히 Index Full Scan을 선택한다.

SYS@ora10g>select /*+ rule */ * from t_emp
  2  where sal >= 5001
  3  order by empno, no ;

------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_EMP    |
|   2 |   INDEX FULL SCAN           | T_EMP_PK |
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL">=5001)

Note
-----
   - rule based optimizer used (consider using cbo)

FIRST_ROWS_N

사용자가 처음 n개 로우만 Fetch하는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.
n으로 지정할 수 있는 값은 1, 10, 100, 1000 네 가지며, 사용자가 지정한 n개 로우 이상을 Fetch한다면 오히려 더 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다.

alter session set optimizer_mode = first_rows_100 ;
select /*+ first_rows(100) */ * from t where ... ;

힌트를 사용할 때는 괄호 안에 0보다 큰 어떤 정수 값이라도 입력 가능하므로 파라미터를 이용할 때보다 더 정밀하게 제어할 수 있다.
first_rows와 달리 first_rows_n응 완전한 CBO 모드로 작동한다.
예를 들어, first_rows_100이면 100개 로우를 가장 빨리 리턴할 수 있는 최저비용의 실행계획을 선택하며, Table Full Scan비용이 오히려 낮다면 그것을 선택한다.

SYS@ora10g>select count(*) all_emp
  2  , count(case when sal >= 2000 then 1 end) over_2000
  3  , round(count(case when sal >= 2000 then 1 end) / count(*) * 100) ratio
  4  from t_emp ;


   ALL_EMP  OVER_2000      RATIO
---------- ---------- ----------
     15000       6000         40

위 쿼리 결과에서 보듯 sal >= 2000인 사원은 6,000명으로서 전체 중 43%를 차지하므로 일정량 이상을 Fetch하는 순간 오히려 Table Full Scan 보다 비용이 커진다.

SYS@ora10g>select /*+ first_rows(10) */ * from t_emp
  where sal >= 2000
  2    3    order by empno, no ;

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    10 |   410 |    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_EMP    |  6000 |   240K|    27   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | T_EMP_PK |    26 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL">=2000)

SYS@ora10g>select /*+ first_rows(100) */ * from t_emp
  where sal >= 2000
  2    3    order by empno, no ;

------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  6000 |   240K|       |    91   (3)| 00:00:02 |
|   1 |  SORT ORDER BY     |       |  6000 |   240K|   776K|    91   (3)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| T_EMP |  6000 |   240K|       |    24   (5)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SAL">=2000)

first_rows(10) 일 때는 Index Full Scan 하다가 first_rows(100) 일 때 Table Full Scan으로 바뀌었다.
비용을 고려해 실행계획을 선택했음을 알 수 있다.

CHOOSE

액세스되는 테이블 중 적어도 하나에 통계정보가 있다면 CBO, 그중에서도 all_rows 모드를 선택한다.
어느 테이블에도 통계정보가 없으면 RBO를 선택한다.
9i까지는 choose가 기본 설정이었으나 10g부터는 all_rows가 기본 옵티마이저 모드로 설정된다.
10g부터 RBO를 공식적으로 지원하지 않게 된 탓이며, 동적 샘플링 기본 레벨이 2로 바뀐 것과도 무관하지 않다.
즉, 통계정보 없는 테이블을 발견하면 무조건 동적 샘플링이 일어나기 때문에 RBO로 작동할 일이 없어진 것이다.

옵티마이저 모드 선택

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

문서에 대하여

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