옵티마이저 (by niceboyous) [2013.01.29]
h1.01 옵티마이저
h3.(1) 옵티마이저란?
| 옵티마이저(Optimizer)는 시용자가 요청한 SQL을 가장 효율적이고 빠르게 수행할 수 있는 최적(최저비용)의 처리경로를 선택해 주는 DBMS의 핵심엔진 구조화된 질의언어 (SOL)로 사용자가 원하는 결과집합을 정의하면 이를 얻는 데 펼요한 처리절차(프로시저)는 DBMS에 내장된 옵티마이저가 자동으로 생성 |
|---|
종류
규칙기반옵티마이저 (Rule-Based Optimizer, 이하 RBO)
비용기반옵티마이저 (Cost-Based Optimizer, 이하 CBO)
역사가 오래된 오라클은 RBO에서 출발하였으나 다른 상용 RDBMS는 탄생 초기부터 CBO를 채택
오라클도 10g부터 RBO에 대한 지원을 중단 ==> CBO만 지원
h3.(2) 규칙기반 옵티마이저 (RBO)
'휴리스틱 (Heuristic) 옵티마이저' 라고 불리며， 미리 정해 놓은 우선순위에 따라 액세스 경로(Access Path)를 평가하고 실행계획을 선택
주 : 휴리스틱[ heuristic ] 네이버 지식백과 (컴퓨터인터넷IT용어대사전
휴리스틱(heuristic)은 그리스어로 「발견하다(to find)」란 의미가 있다. 문제의 답을 시행 착오적인 방법을 사용하여 구하는 것. 즉, 알고리즘이 확립되지 않았을 때 사용되는 문제 해결의 한 방법으로 도형 인식, 학습 과정, 자기 형성 등의 기능을 이용하여 답을 구하는 방법.
OLTP 환경의 중소형 데이터베이 스 시스템이라면 RBO 규칙이 어느 정도 보편 타당성을 갖는다. ==> 적합할수 있다.쿼리의 복잡도가 적고 직관적 상황등
하지만 데이터량， 값의 수(number 01 dislincl value), 컬럼 값 분포， 인텍스 높이， 클러스터링 팩터
같은 데이터 특성을 고려하지 않기 때문에 RBO는 대용량 데이터를 처리하는 데 있어 합리적이지 못할 때가 많다.
| 예1. 인덱스가 달려있고 조건절이 있다면 대용량이라도 풀스캔이 유리한가? {code:sql} |
|---|
||
||예2. empno컬럼에 인텍스가 있으면 무조건 그 인덱스를 이용해 sort order by 연산의 FULL SCAN 대체시 효율은?
{code:sql}
select /*+ rule * / * from emp order by empno
||
RBO는 예측 가능, 일관성 사용자가 원하는 처리경로로 유도하기 쉬운장점
CBO는 같은 SQL이더라도 데이터 특성에 따라 실행계획이 달라지고 복잡한 비용 원리를 내포하고 있어 이를 정확히 이해하지 못한다면 제어가 쉽지 않은 단점.
초대용량 데이터베이스 환경에서 더는 RBO가 대안이 될 수 없고, 오라클에서 더이상 지원안하니 CBO의 활용능력을 키우자
h3.(3) 비용기반 옵티마이저
비용을 기반으로 최적화를 수행.
비용(Cost) - 쿼리를 수행하는데 소요되는 일량 또는 시간을 뜻함.
| I/O 비용 모델 : I/O 요청 (Call) 횟수만으로 비용을 평가. CPU 비용 모델 : CPU 비용 모델에서는 CPU 연산 비용까지감안하여 수행 일량을 상대적인 시간 개념으로 환산해 비용을 평가 |
|---|
CBO의 비용은 예상치인데 미리 구해놓은 테이블과 인텍스에 대한 여러 통계정보를 기초로 각 오퍼레이션 단계별 예상 비용을 산정하고，
이를 합산한 총비용이 가장 낮은 실행계획 하나를 선택
| 오브젝트 통계정보 : 레코드 개수,블록 개수,평균 행 길이,컬럼 값의 수,컬럼 값 분포,인덱스 높이,클러스터링 팩터 시스템 통계정보 : 하드웨어적 특성을 반영 (CPU 속도， 디스크 I/O 속도 등) |
|---|
옵티마이저의 최적화 수행단계를 요약
1. 사용자가 던진 쿼리수행을 위해 후보군이 될만한 실행계획을 찾음
2. 데이터 딕셔너리(DATA DICTIONARY)에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용 산정
3. 각 실행계획의 비용을 비교해서 최저비용을 갖는 하나를 선택
h4.동적 샘플링(Dynamic Sampling)
통계정보가 없거나 너무 오래돼 신뢰할 수 없을 때 용티마이저가 동적으로 샘플링을 수행하도록 할수있다.
optimizer_dynamic_sampling 파라미터로 통적 샘플링 레벨을 조정하며. 9i에서 기본 레벨이 1이던 것이 10g에서 2로 상향 조정. 따라서 lOg에서는 쿼리 최적화 시 통계정보 없는 태이블을 발견하면 무조건 동적 샘플링을 수행
레벨을 0으로 설정해 동적 샘플령이 일어나지 않게 할 수 있으며 .9i 기본 값인 1로 설정할 때는 아래 조건을 모두 만족할 때만 동적 샘플링
(1) 통계정보가 수집되지 않은 태이블이 적어도 하나 이상 었고，
(2) 그 테이블이 다른 태이블과 조인되거나 서브쿼리 또는 Non-mergeable VieW(4장서 자세히 다룸) 에 포함되고，
(3) 그 테이블에 인텍스가 하나도 없고，
(4) 그 테이블에 할당된 블록 수가 32개(동적 샘플링을 위한 표본 블록 수의 기본 값)보다 많을 때
동적 샘플링으로 얻은 통계정보는 데이터 닥셔너리에 영구 저장되지 않는다.
통계정보가 올바르지 않은 테이블을 참조하는 쿼라는 하드 파싱할 때마다 동적 샘플링을 위한 Recursive SQL이 추가로 수행되므로 성능이 좋을리 없다.
따라서 DB 관리지는 이런 현상이 발생하지 않도록 통계정보를 관리 해주어야한다.
참고로， 통적 샘플링이 일어날 때면 SQL 트레이스에서 아래와 같은 주석을 포함한 Recursive SQL을 발견할 수 있다.
SELECT /* OPT DYN SAMP * / ... From ...
| 예. 동적 샘플링 발생에 대한 트레이스 비교 테스트 {code:sql} SQL> select last_analyzed from dba_tables where owner = 'HR' and table_name = 'EMPLOYEES'; |
|---|
LAST_ANA
13/04/20
SQL> exec dbms_stats.delete_table_stats('HR','EMPLOYEES');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> set autot trace explain statistics
SQL> select * from hr.employees;
107 개의 행이 선택되었습니다.
Execution Plan
--
Plan hash value: 1445457117
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---
| 0 | SELECT STATEMENT | 107 | 14231 | 3 (0) | 00:00:01 | |
| 1 | TABLE ACCESS FULL | EMPLOYEES | 107 | 14231 | 3 (0) | 00:00:01 |
---
Note
-
- dynamic sampling used for this statement (level=2)
Statistics
--
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
10440 bytes sent via SQL*Net to client
597 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
SQL> set autot off
SQL> exec dbms_stats.gather_table_stats('HR','EMPLOYEES');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select last_analyzed from dba_tables where owner = 'HR' and table_name = 'EMPLOYEES';
LAST_ANA
13/04/20
SQL> set autot trace explain statistics
SQL> select * from hr.employees;
107 개의 행이 선택되었습니다.
Execution Plan
--
Plan hash value: 1445457117
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---
| 0 | SELECT STATEMENT | 107 | 7383 | 3 (0) | 00:00:01 | |
| 1 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7383 | 3 (0) | 00:00:01 |
---
Statistics
--
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
10440 bytes sent via SQL*Net to client
597 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
SQL> set autot off
||
QUERY TRANSFORMER : 최적화하기 쉬운 형태로 변환을 시도.
ESTIMATOR : 선택도(Selectivity)， 카디널리티 (Cardinality)， 비용(Cost)을 계산. 궁극적으로는 실행계획 전체에 대한 총 비용을 계산.
PLAN GENERATOR : 후보군이 될만한 실행계획들을 생성
스스로 학습하는 용티마이저 (SELF-LEARNING OPTIMIZER)
오브젝트 통계와 시스댐 통계로부터 산정한 '예상' 비용만으로 실행계획을 수립했지만 앞으로는 예상치가 빗나갔을 때 이들 런타임 수행 통계를 보고 실행계획을 조정할 움직임.
1.ADAPTIVE CURSOR SHARING
2.DYNAMIC RUNTIME OPTIMIZATIONS
(4) 옵티마이저 모드
5가지 3레벨
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 ... 쿼리 레벨 변경
예> 테스트
ALl_ROWS
쿼리 최종 결과집합을 끝까지 Fetch하는 것을 전제로,시스램 리소스(I/0， CPU, 메모리 등)를 가장 적게 시용하는 실행계획을 선택
FIRST_ROWS
전체 결과집합 중 일부 로우만 Fetch하다가 멈추는 것을 전제로 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택.
사용자가 만약 끝까지 Fetch한다면 오히려 더 많은 리소스를 시용하고 전체 수행 속도도 느려질 수 있다.
비용과 규칙 (=휴리스틱)을 혼합한 형태의 옵티마이저 모드.
FIRST_ROWS_N
사용자가 처음 n개 로우만 Fetch하는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획
n으로 지정할 수 있는 값은 1,10,100,1000 네 가지며,사용자가 지정한 n개 로우 이상을 Fetch한다면 오히려 더 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다.
first_rows와 달리 first_rows_n은 완전한 CBO 모드로 작동-
예를 들어， first_rows_l00 이면 100개 로우를 가장 빨리 리턴할 수 있는 최저비용의 실행계획을 선택하며，
Table Full Scan 비용이 오히려 낮다면 그것을 선택한다.
|| TEST
{code:sql}
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 => 'for columns sal');
6 end;
7 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> set autotrace traceonly exp;
SQL> select /*+ all_rows */ * from t_emp
2 where sal >= 5000
3 order by empno, no ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1175086354
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 42000 | 27 (4)| 00:00:01 |
| 1 | SORT ORDER BY | | 1000 | 42000 | 27 (4)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T_EMP | 1000 | 42000 | 26 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAL">=5000)
SQL> select /*+ first_rows */ * from t_emp
2 where sal >= 5000
3 order by empno, no ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1185322641
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 42000 | 13889 (1)| 00:02:47 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_EMP | 1000 | 42000 | 13889 (1)| 00:02:47 |
| 2 | INDEX FULL SCAN | T_EMP_PK | 14000 | | 36 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL">=5000)
SQL> set autotrace off;
이 실행계획을 수립하는 데에는 비용보다 규칙이 우선시되었다.
Table Full Scan 비용은 26이고 Index Full Scan 비용은 13.900이지만 사용자가 Fetch 도중에
멈출 것이란 가정 하에 이런 실행계획을 수립한 것이다.
SQL> 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
---------- ---------- ----------
14000 1000 7
첫 번째 Fetch 분량을 얻기까지 많은 인텍스 스캔과 테이블 액세스를 수반하므로
firstJows가 지향하는 최초 응답속도도 생각만큼 좋지 않을 수 있다. sal 조건을 만족하는 레코
드가 앞쪽에 몰려 있거나 (empno, no 값이 작을수록 sal 값이 큰 경우를 말함) ArraySize가 아주 작을 때
만 이점이 있을 것이다. 그럼에도 옵티마이저는 무조건 Index Full Scan을 선택
컬럼 히스토그램을 생성했으므로 'sal )= 5001J 인 사원이 없다는 사실만큼은 옹티마
이저도 알 수 있다. 따라서 이 조건절을 사용하면 실행계획도 아래와 같이 바뀐다.
SQL> set autotrace traceonly exp;
SQL> select /*+ first_rows */ * from t_emp
2 where sal >= 5001
3 order by empno, no ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1175086354
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 21000 | 27 (4)| 00:00:01 |
| 1 | SORT ORDER BY | | 500 | 21000 | 27 (4)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T_EMP | 500 | 21000 | 26 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAL">=5001)
같은조건을주고 RBO 모드로 실행해 보면 아래와같이
여전히 Index Full Scan을 선택 ==>테이블 스캔과 인덱스 스캔의 우선순위 차이
SQL> select /*+ rule */ * from t_emp
2 where sal >= 5001
3 order by empno, no ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1185322641
------------------------------------------------
| 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)
SQL> set autotrace off;
SQL> 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
---------- ---------- ----------
14000 6000 43
SQL> set autotrace traceonly exp;
SQL> select /*+ first_rows(10) */ * from t_emp
2 where sal >= 2000
3 order by empno, no ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1175086354
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5999 | 246K| 28 (8)| 00:00:01 |
| 1 | SORT ORDER BY | | 5999 | 246K| 28 (8)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T_EMP | 5999 | 246K| 26 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAL">=2000)
SQL> select /*+ first_rows(100) */ * from t_emp
2 where sal >= 2000
3 order by empno, no ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1175086354
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5999 | 246K| 28 (8)| 00:00:01 |
| 1 | SORT ORDER BY | | 5999 | 246K| 28 (8)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T_EMP | 5999 | 246K| 26 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAL">=2000)
SQL> select /*+ first_rows(10) */ * from t_emp
2 where sal >= 1000
3 order by empno, no ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1185322641
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 420 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_EMP | 12000 | 492K| 14 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | T_EMP_PK | 12 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL">=1000)
SQL> select /*+ first_rows(100) */ * from t_emp
2 where sal >= 1000
3 order by empno, no ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1175086354
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12000 | 492K| 28 (8)| 00:00:01 |
| 1 | SORT ORDER BY | | 12000 | 492K| 28 (8)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T_EMP | 12000 | 492K| 26 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAL">=1000)
||
CHOOSE
액세스되는 테이블 중 적어도 하나에 통계정보가 있다면 CBO 그중에서도 all_rows 모드를 선택한다.
어느 테이블에도 통계정보가 없으면 RBO를 선택한다.
9i까지는 choose가 기본 설정이 었으나 lOg부터는 all_rows가 기본 옵티마이저 모드로 설정된다.
1Og부터 RBO를 공식적으로 지원하지 않게 된 탓이며，동적 샘플령 기본 레벨이 2로 바뀐 것과도 무관하지 않다.
즉，통계정보 없는 태이블을 발견하면 무조건 동적 샘플링이 일어나기 때문에 RBO로작동할 일이 없어진 것이다.
애플리케이션 특성상 확실히 first rows가 적합하다는 판단이 서지 않는다면 all_rows를 기본 모드로 선택하고
필요한 쿼리 또는 세션 레벨에서 first_rows 모드로 전환할 것을 권고.
Selinger 스타일의 옵티마이저 Selinger 스타일의 옵티마이저 현재의 모든 상용 관계형 DBMS의 옵티마이저는 IBM DB2의 모태인 System-R 프로토타입 시스템을 개발할 당시에 고안된 아키텍처에 기반하고 있다. 이 아키텍처를 주도적으로 제안한 IBM의 여성 전산학자 Pat. Selinger의 이름을 따서,' Selinger 스타일 옵티마이저'라 부른다. 참고로 Pat. Selinger의 논문은 아직까지도 데이타베이스 분야에서 가장 많이 인용 되는 논문 중의 하나이고, Pat. Selinger는 이 한 편의 논문으로 데이타베이스 연 구 분야에서 슈퍼스타의 반열에 올라섰다. 이 Selinger 스타일 옵티마이저 아키텍처의 가장 큰 두 가지 특징은 1) 동적 프로그 래밍 기반에 의한 실행 계획 탐색(Search Space Pruning based on Dynamic Programming)과 2) 비용 기반 최적화(Cost-Based Optimization)인데, 각각 위 에서 나열한 옵티마이저 핵심 기능의 첫째, 둘째 기능에 해당된다. 구글님의 번역 http://www-03.ibm.com/ibm/history/witexhibit/wit_fellows_selinger.html 패트 셀린은 IBM 연구원 및 데이터 관리 아키텍처 및 기술 담당 부사장이다. 1994 년, 그녀는 관계형 데이터베이스에있는 그녀의 뛰어난 기술 작업과 리더십, IBM 연구원, IBM에서 상위 50 기술 전문가에게만 부여 명예의 제목을 받았다. 박사 셀린는 세계 최초의 관계형 데이터베이스 시스템을 생산하고 매우 성공적인 DB2 데이터베이스 제품군에 대한 기본 구조를 확립 IBM 리서치 팀의 주요 일원이었다. 관계형 데이터베이스에 대한 비용 기반 쿼리 최적화에 박사 셀린의 혁신적인 작품은 이제 거의 모든 관계형 데이터베이스 공급 업체에 채택되어, 지금은 거의 모든 대학 데이터베이스 코스로 진행된다. 박사 셀린는 IBM 알마 덴 연구소 컴퓨터 공학 부서를 운영하고 설립 그의 임무 IBM의 데이터베이스 제품에 기술을 촉진하는 데이터베이스 기술 연구소 관리.DBTI 조직은 발명 및 Linux, Unix 및 Windows 용 IMS 및 DB2, OS/390 용 DB2의 기본 기술을 제공하기 위해 IBM 개발 협력. 박사 셀린의 현재 책임 정형 및 비정형 데이터 모두 소스의 모든 유형 간의 정보 통합의 다음 세대를 운전 수 있습니다. 그녀는 1999 년에 공학 학회 (National Academy of Engineering) 회원으로 선출되었다. http://researcher.ibm.com/researcher/view.php?person=us-patseli http://www.witi.com/center/witimuseum/halloffame/2004/pselinger.php |
|---|
참고 자료
Oracle Technical Note - 오라클 옵티마이저의 기본 원리(이상원 (주)엑셈 연구소장 겸 성균관대학교 교수)
SQL Tuning Workshop_v1.0_20121206_HyoungjoKim