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) |
----------------------------------------------------------------
문서에 대하여
