옵티마이저(Optimizer)
모든 일에는 계획을 세우기 마련입니다. 어떻게 행동하느냐에 따라 들이는 힘이나 시간도, 도출되는 결과도 제각각이기 때문이죠. DB에서도 마찬가지입니다! 개발자가 단순히 SQL을 작성하고 실행하면 일반적인 실행파일처럼 즉시 실행되는 것이 아니라 "이 쿼리문을 어떻게 하면 효율적으로 실행을 시킬까?"라 계산하며 여러 가지 실행계획을 세우게 됩니다. 이렇게 여러 실행계획을 세운 뒤 시스템 통계정보를 활용하여 각 실행계획의 예상 비용을 산정한 후 각 실행계획을 비교해서 최고의 효율을 가지고 있는 실행계획에 따라 쿼리를 수행하게 됩니다!
이러한 과정은 DBMS의 두뇌라 불리는 옵티마이저에서 실행됩니다. 옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진입니다.
그림에서 보다시피 옵티마이저는 두 개의 최적화 방향을 가지고 있습니다.
| 목록 | 규칙 기반 최적화(Rule-Based Optimizer) | 비용 기반 최적화(Cost-Base Optimizer) |
| 개념 | 옵티마이저에 내장된 우선순위에 따라 실행 계획 수립 | 여러 실행 계획 중 최소비용의 실행 계획을 채택 |
| 기준 | 실행우선 순위(Ranking) | 액세스 비용(Cost) |
| 인덱스 | 인덱스 존재 시 가장 우선시 사용 | 비용에 의한 결정 |
| 성능 | 사용자의 SQL 작성 숙련도에 따라 달림 | 옵티마이저의 계산(예측) 성능에 따라 달림 |
| 특징 | 내장된 우선순위 덕분에 대부분 실행 방법이 같다. | |
| 장점 | 판단이 매우 규칙적 실행 예상 가능 | 통계 정보를 통한 현실 요소 적용 |
| 단점 | 예측 통계정보 요소 무시 | 원하는 방식으로 유도가 어려움 |
| 사례 | AND 중심 양쪽 ‘=’ 시 Index Merge 사용 | AND 중심 양쪽 ‘=’ 시 분포도별 Index 선택 |
| 비고 | 지금은 사용 안함 | 많음 RDBMS가 채택하는 방식. |
1.RBO
미리 정해진 규칙(내장 우선순위)에 따라 실행 계획을 수립하는 방식입니다. 'Oracle 9i'버전까지는 통계 정보를 자동으로 갱신하지 않기 때문에 RBO의 성능이 더 좋았지만, 그 이후부터는 CBO보다 성능이 떨어지게 되었고 10 gR2 버전부터는 아예 사용을 못하는 방법입니다.
RBO는 융통성이 없기 때문에 SQL 작성이나 연산자 우선순위, 인덱스 생성 시 각별히 주의해야 합니다.
| LEVEL | 설명 |
| 1 | Single Row by Rowid (ROWID를 사용한 단일 행) |
| 2 | Single Row by Cluster Join (클러스터 조인에 의한 단일 행) |
| 3 | Single Row by Hash Cluster Key with Unique or Primary Key (유일하거나 기본키(Primary Key)를 가진 해시 클러스터 키에 의한 단일 행인 경우) |
| 4 | Single Row by Unique or Primary Key (유일하거나 기본키(Primary Key)에 의한 단일 행인 경우) |
| 5 | Clustered Join (클러스터 조인일 경우) |
| 6 | Hash Cluster Key (해시 클러스터 조인인 경우) |
| 7 | Indexed Cluster Key (인덱스 클러스터 조인인 경우) |
| 8 | Composite Index (복합 칼럼 인덱스인 경우) |
| 9 | Single-Column Indexes (단일 칼럼 인덱스인 경우) |
| 10 | Bounded Range Search on Indexed Columns (인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우) |
| 11 | Unbounded Range Search on Indexed Columns ( 인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우) |
| 12 | Sort-Merge Join (정렬-병합(Sort-Merge) 조인인 경우) |
| 13 | MAX or MIN of Indexed Column (인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우) |
| 14 | ORDER BY on Indexed Column (인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우) |
| 15 | Full Table Scan (전체 테이블을 스캔(FULL TABLE SCAN)하는 경우) |
옵티마이저의 실행계획을 유도할 수 있는 힌트나 HASH JOIN는 CBO 이후에 나온 개념이기 때문에 적용하지 못합니다.
(ex: 테이블에 데이터가 몇개밖에 없어도 인덱스가 있으면, FULL TABLE SCAN이 아닌 INDEX SCAN으로 작동.)
2. CBO
10gR2버전 부터 공식적으로 CBO만을 지원하게 되면서 CBO에 대한 연구가 활발히 이루어졌습니다.
CBO는 [테이블, 인덱스, 칼럼 등]의 다양한 객체 통계정보와, [I/O횟수, CPU, 메모리 사용량, 네트워크 전송량 등] 시스템 통계정보들을 이용해 최소 비용을 예측하기 때문에 정확한 통계정보를 최신화하는 것이 무엇보다 중요합니다.
CBO 모드 종류
최적의 비용을 계산하는 여러 가지 모드가 있습니다.
(1) CHOOSE
SQL이 실행되는 환경에서 통계 정보를 가져올 수 있으면 비용 기반 옵티마이저로 그렇지 않다면 규칙 기반 옵티마이저로 작동시키는 모드입니다. 현재는 잘 사용하지 않습니다.
(2) FIRST_ROWS
옵티마이저가 처리 결과 중 첫 건을 출력하는데 걸리는 시간을 최소화할 수 있는 실행 계획을 세우는 모드입니다.
(3) FIRST_ROWS_n
SQL의 실행 결과를 출력하는데까지 걸리는 응답속도를 최적화하는 모드입니다.
(4) ALL_ROWS
SQL 실행 결과 전체를 빠르게 처리하는데 최적화된 실행계획을 세우는 모드입니다. 마지막으로 출력될 행까지 최소한의 자원을 사용하여 최대한 빨리 가져오게 하며 오라클 10g 이후로는 이 모드가 기본값으로 설정되어 있습니다.