[DB] 데이터베이스 옵티마이저(Optimizer)에 대하여

옵티마이저(Optimizer)란?

옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진입니다. 컴퓨터의 두뇌가 CPU인 것처럼 DBMS의 두뇌는 옵티마이저라고 할 수 있습니다. 개발자가 SQL을 작성하고 실행하면 소프트웨어 실행파일처럼 즉시 실행되는 것이 아니라 옵티마이저(Optimizer)라는 곳에서 "이 쿼리문을 어떻게 실행시키겠다!"라는 여러 가지 실행계획을 세우게 됩니다. 이렇게 실행계획을 세운 뒤 시스템 통계정보를 활용하여 각 실행계획의 예상 비용을 산정한 후 각 실행계획을 비교해서 최고의 효율을 가지고 있는 실행계획을 판별한 후 그 실행계획에 따라 쿼리를 수행하게 되는 것입니다.  

 

 

 

 옵티마이저의 종류 

옵티마이저는 실행 계획을 세우는 방식에 따라 규칙 기반 옵티마이저와 비용 기반 옵티마이저로 나뉩니다.

항목	규칙 기반 옵티마이저	비용 기반 옵티마이저
개념	사전에 정의된 규칙 기반	최소비용 계산 실행계획 수립
기준	실행우선 순위(Ranking)	액세스 비용(Cost)
인덱스	인덱스 존재 시 가장 우선시 사용	Cost에 의한 결정
성능	사용자 SQL작성 숙련도	옵티마이저 예측 성능
장점	판단이 매우 규칙적 실행 예상 가능	통계 정보를 통한 현실 요소 적용
단점	예측 통계정보 요소 무시	최소 성능 보장 계획의 예측 제어 어려움
사례	AND 중심 양쪽 ‘=’ 시 Index Merge 사용	AND 중심 양쪽 ‘=’ 시 분포도별 Index 선택

 

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

오라클8 이하의 버전에서 기본으로 설정된 옵티마이저가 바로 규칙 기반 옵티마이저입니다. 규칙 기반 옵티마이저는 말 그대로 실행 속도가 빠른 순으로 규칙을 먼저 세워두고 우선순위가 앞서는 방법을 채택하는 것입니다. 과거에는 옵티마이저의 비용을 예측하는 능력이 그다지 좋지 않아 이러한 방식을 사용하였습니다. 규칙의 우선순위는 아래와 같습니다.

순위	설명
1	ROWID를 사용한 단일 행인 경우
2	클러스터 조인에 의한 단일 행인 경우
3	유일하거나 기본키(Primary Key)를 가진 해시 클러스터 키에 의한 단일 행인 경우
4	유일하거나 기본키(Primary Key)에 의한 단일 행인 경우
5	클러스터 조인인 경우
6	해시 클러스터 조인인 경우
7	인덱스 클러스터 키인 경우
8	복합 칼럼 인덱스인 경우
9	단일 칼럼 인덱스인 경우
10	인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우
11	인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우
12	정렬-병합(Sort-Merge) 조인인 경우
13	인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우
14	인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우
15	전체 테이블을 스캔(FULL TABLE SCAN)하는 경우

이 우선순위는 잘 공부해두시면 쿼리문을 효율적으로 작성하는데 큰 도움이 되니 꼭 한번 공부해보시길 바랍니다. 이렇게 규칙 기반 옵티마이저는 우선순위에 순위가 매겨져 있기 때문에 옵티마이저에서 실행계획이 세워지는 것을 미리 예측할 수 있는 장점이 있고 조금 응용하면 내가 원하는 대로 실행 계획이 세워지도록 유도할 수도(?) 있는 장점이 있습니다.

 

하지만 이게 독이 될수도 있는 게 예를 들어 테이블에 데이터가 몇 개 없을 경우 전체 조회를 했을 경우 FULL TABLE SCAN이 더 빠를 수 있는데 INDEX를 확인하고 타버린다던지 하는 비효율적인 실행 계획이 도출될 수 있습니다. 또한 옵티마이저의 실행계획을 유도할 수 있는 힌트와 HASH JOIN의 경우에는 규칙 기반 이후에 나온 개념들이므로 여기서는 사용하실 수 없다는 단점이 있습니다.

 

비용 기반 옵티마이저

최근에 많이 사용하고 있는 옵티마이저 방식이며 오라클 10 이후 버전부터는 공식적으로 비용 기반 옵티마이저만 사용을 하는 것으로 알고 있습니다. 비용 기반 옵티마이저는 옵티마이저에서 실행 계획을 세운 뒤(최대 2천개까지) 비용이 최소한으로 나온 실행 계획을 수행합니다. 비용기반 옵티마이저는 비용을 예측하기 위해서 규칙 기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보를 이용합니다. 통계정보가 없는 경우 비효율적인 실행계획을 생성할 수 있으므로, 정확한 통계정보를 유지하는 것이 중요합니다.

 

비용 기반 옵티마이저의 모드

비용 기반 옵티마이저는 여러 가지 모드가 있습니다. 이 모드에 따라 최적의 비용을 구하는 방식이 조금씩 달라집니다.

• CHOOSE : 현재는 잘 사용하지 않지만 SQL이 실행되는 환경에서 통계 정보를 가져올 수 있으면 비용 기반 옵티마이저로 그렇지 않다면 규칙 기반 옵티마이저로 작동시키는 모드입니다.
• FIRST_ROWS : 옵티마이저가 처리 결과 중 첫 건을 출력하는데 걸리는 시간을 최소화할 수 있는 실행 계획을 세우는 모드입니다.
• FIRST_ROWS_n : SQL의 실행 결과를 출력하는데까지 걸리는 응답속도를 최적화하는 모드입니다.  
• ALL_ROWS : SQL 실행 결과 전체를 빠르게 처리하는데 최적화 된 실행계획을 세우는 모드입니다. 마지막으로 출력될 행까지 최소한의 자원을 사용하여 최대한 빨리 가져오게 하며 오라클 10g 이후로는 이 모드가 기본값으로 설정되어 있습니다.

 

옵티마이저 동작 방식

• Parser : SQL문장을 분석하여 문법 검사와 구성요소를 파악하고 이를 파싱 해서 파싱 트리를 만듭니다.
• Query Transformer : 파싱된 SQL을 보고 같은 결과를 도출하되, 좀 더 나은 실행 계획을 갖는 SQL로 변환이 가능한지를 판단하여 변환 작업을 수행합니다. 
• Estimator : 시스템 통계정보를 딕셔너리로부터 수집하여 SQL을 실행할 때 소요되는 총비용을 계산합니다.
• Plan Generator : Estimator를 통해 계산된 값들을 토대로 후보군이 되는 실행계획을 도출합니다. 
• Row-Source Generator : 옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드나 프로시저 형태로 포맷팅 합니다.
• SQL Engine : SQL을 실행합니다.

 

 옵티마이저를 최적으로 활용하기 위해 다루어야 할 내용들 

이와 같이 옵티마이저는 SQL의 성능 향상에 있어 매우 중요한 영향을 미칩니다. 이러한 옵티마이저를 좀 더 효율적으로 사용해주기 위해서는 여러 가지 내용들을 고려해야 합니다.

 

통계 정보

앞서 잠깐 설명했지만 비용 기반 옵티마이저에서는 실행 계획 도출 시 DBMS에서 제공하는 통계 정보를 사용합니다. 이러한 통계정보는 꾸준히 갱신되고 있는 것이 좋습니다. 통계 정보의 생성 주기 및 수행 시간을 스케줄러에 일괄적으로 등록하여 일정 주기마다 통계 정보를 수집하고 종료하도록 자동 통계 정보를 생성할 수도 있고 사용자가 직접 수동으로 통계 정보를 생성할수도 있습니다. DBMS_STATS 패키지를 사용하면 데이터베이스, 스키마 및 계정, 테이블 인덱스 단위로 구분하여 통계 정보를 수집할 수 있습니다.

 

주요 통계 정보들

구분	세부 통계 정보
테이블	테이블의 전체 행의 갯수
	테이블이 차지하고 있는 전체 블록 갯수
	테이블의 행들이 가지고 있는 평균 길이
컬럼	컬럼 값의 종류
	컬럼 내부 NULL 값의 분포도
	컬럼 값의 평균 길이
	컬럼 내부 데이터 분포의 추정치
인덱스	LEAF BOLOCK 수 : 데이터를 보관하는 블록 수
	LEVELS : 인덱스 트리의 LEVEL 정보
	CLUSTERING FACTOR : 접근하고자 하는 데이터가 모여 있는 밀집도
시스템	I/O 성능 및 사용률
	CPU 성능 및 사용률

 

옵티마이저에 영향을 줄 수 있는 파라미터

옵티마이저가 최적의 실행 계획을 결정할 때 파라미터의 값도 매우 중요한 영향을 미칩니다. 예를 들어 OPTIMIZER_MODE는 옵티마이저의 모드를 결정하는 파라미터인데 중간에 옵티마이저 모드가 변경된다면 실행계획이 꼬일 수 있겠죠? 이와 같이 옵티마이저에 직접적으로 영향을 줄 수 있는 파라미터들은 개발하고자 하는 방향으로 설계단계에서 최적화로 설정하고 바꾸지 않는 것이 좋습니다.

 

옵티마이저는 결코 만능이 아니다.

옵티마이저의 성능은 점차 향상되고는 있지만 결코 만능이 아닙니다. 칼럼의 통계 정보만 가지고는 조건절에서 사용된 조건을 만족하는 데이터의 양이 어느정도인지 알 수가 없기에 비용 계산 결과가 정확하지가 않습니다. 그리고 비용산정시 쿼리문 단독으로 실행된다고 가정하기에 운영 서버와 같이 쿼리가 동시 실행된다면 실제 비용은 달라질 수 있기도 하고 히스토그램 버킷이 최대 254까지만 사용할 수 있어 254개 이상의 값을 갖는 컬럼의 경우 비용 예측 결과가 정확하지 않는 등의 옵티마이저에 여러가지 빈틈들이 많습니다. 절대 옵티마이저를 맹신하시면 안 됩니다. 쿼리 튜닝을 할 때에는 쿼리 문의 실행계획을 꼭 보시고 옵티마이저가 비효율적으로 작동하고 있다면 오라클의 힌트 같은 부가적인 장치를 통해 올바르게 작동될 수 있도록 유도하는 방법도 필요합니다. 예를 들어 SQL의 바인드 변수의 경우 바인드 변수에 입력될 값의 예측이 가능하다면 입력받을 값에 따라 적절한 힌트를 사용할 수 있겠습니다.