오라클 성능 고도화 원리와 해법 II (2010년)
옵티마이저 0 0 3,067

by 구루비 옵티마이저 OPTIMIZER [2010.04.16]


01. 옵티마이저

(1) 옵티마이저란?

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

     
  • 옵티마이저에는 크게 두 가지가 있다.
    • 규칙기반 옵티마이저(Rule-Based Optimizer, 이하 RBO)
    • 비용기반 옵티마이저(Cost-Based Optimizer, 이하 CBO)

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

  • 휴리스틱(Heuristic) 옵티마이저라고 불리며, 미리 정해놓은 우선순위에 따라 액세스 경로(Access Path)를 평가하고 실행계획을 선택한다.
  • RBO는 대용량 데이터를 처리하는 데 있어 합리적이지 못할 때가 많다.
    예를 들어, 조건절 컬럼에 인덱스가 있으면 인덱스를 사용한다. 항상 인덱스를 신뢰하며, Full Table Scan과의 손익을 따지지 않는다.
    또 다른 예로, 아래와 같은문장을 수행할 때도 empno 컬럼에 인덱스가 있으면 무조건 그 인덱스를 이용해 sort order by 연산을 대체한다.
    select /*\+ rule \*/ * from emp order by empno

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

  • 비용기반 옵티마이저는 말 그대로 비용을 기반으로 최적화를 수행한다.
  • 옵티마이저의 최적화 수행단계를 요약하면 다음과 같다.
    • 1. 사용자가 던진 쿼리수행을 위해, 후보군이 될만한 실행계획을 찾는다.
    • 2. 데이터 딕셔너리(Data Dictionary)에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
    • 3. 각 실행계획의 비용을 비교해서 최저비용을 갖는 하나를 선택한다.

(4) 옵티마이저 모드

  • rule : RBO 모드를 선택하고자 할때 사용한다.
  • all_rows : 쿼리 최종 결과집합을 끝까지 Fetch하는 것을 전제로, 시스템 리소스를 가장 적게 사용하는 실행계획을 선택한다.
  • first_rows : 전체 결과집합 중 일부 로우만 Fetch하다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.
  • first_rows_n : 사용자가 처음 n개 로우만 Fetch하는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.
  • choose : 액세스되는 테이블 중 적어도 하나에 통계정보가 있다면 CBO, 그중에서도 all_rows 모드를 선택한다. 어느 테이블에도 통계정보가 없다면 RBO를 선택한다.
  • 옵티마이저 모드 선택 : 일반적으로 first_rows는 OLTP 환경에서, all_rows는 DW나 배치 프로그램 등에서 사용하는 옵티마이저 모드라고 알려져 있다.
    하지만 요즘과 같은 웹 어플리케이션 환경에서는 OLTP이더라도 대개 all_rows가 올바른 선택이다. 애플리케이션에서 수행되는 쿼리 자체가 전체범위처리를 요구하기 때문이다.

SQL> 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; 

SQL> alter table t_emp add constraint t_emp_pk primary key(empno, no);
 
SQL> begin 
2 dbms_stats.gather_table_stats( 
3 ownname => user 
4 , tabname => 't_emp' 
5 , method_opt => 'forcolumns sal'); 
6 end; 
7 /   

SQL> set autotrace traceonly exp 
SQL> select /*+ all_rows */ * from t_emp 
2 where sal >= 5000 
3 order by empno, no; 
---------------------------------------------------------------- 
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------- 
|  0  | SELECT STATEMENT   |      |  1000 | 41000 |    26 (8)  | 
|  1  |  SORT ORDER BY     |      |  1000 | 41000 |    26 (8)  | 
|  2  |   TABLE ACCESS FULL|T_EMP |  1000 | 41000 |    25 (4)  | 
----------------------------------------------------------------


SQL> select /*+ first_rows */ * from t_emp 
2 where sal >= 5000 
3 order by empno, no; 
---------------------------------------------------------------------------- 
| Id  | Operation                    | Name    | Rows  | Bytes | Cost(%CPU)|
----------------------------------------------------------------------------
|  0  | SELECT STATEMENT             |         |  1000 | 41000 |  13900 (1)| 
|  1  |  TABLE ACCESS BY INDEX ROWID |T_EMP    |  1000 | 41000 |  13900 (1)| 
|  2  |   INDEX FULL SCAN            |T_EMP_PK | 14000 |       |     37 (3)| 
----------------------------------------------------------------------------


SQL> set autotrace traceonly exp 
SQL> select /*+ first_rows */ * from t_emp 
2 where sal >= 5001
3 order by empno, no; 
---------------------------------------------------------------- 
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------- 
|  0  | SELECT STATEMENT   |      |    1  |    41 |    26 (8)  | 
|  1  |  SORT ORDER BY     |      |    1  |    41 |    26 (8)  | 
|  2  |   TABLE ACCESS FULL|T_EMP |    1  |    41 |    25 (4)  | 
----------------------------------------------------------------


SQL> 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 |
------------------------------------------------


SQL> select /*+ first_rows(10) */ * from t_emp 
2 where sal >= 2000
3 order by empno, no; 
---------------------------------------------------------------------------- 
| Id  | Operation                    | Name    | Rows  | Bytes | Cost(%CPU)|
----------------------------------------------------------------------------
|  0  | SELECT STATEMENT             |         |    11 |   451 |     28 (0)| 
|  1  |  TABLE ACCESS BY INDEX ROWID |T_EMP    |  5999 |   240K|     28 (0)| 
|  2  |   INDEX FULL SCAN            |T_EMP_PK |    26 |       |      2 (0)| 
----------------------------------------------------------------------------


SQL> select /*+ first_rows(100) */ * from t_emp 
2 where sal >= 2000
3 order by empno, no; 
---------------------------------------------------------------- 
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------- 
|  0  | SELECT STATEMENT   |      | 5999  |   240K|    93 (4)  | 
|  1  |  SORT ORDER BY     |      | 5999  |   240K|    93 (4)  | 
|  2  |   TABLE ACCESS FULL|T_EMP | 5999  |   240K|    25 (4)  | 
----------------------------------------------------------------

문서에 대하여

"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3216

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입