옵티마이저(Optimizer)
가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진
실행계획을 세운 뒤 시스템 통계정보를 활용하여 각 실행계획의 예상 비용을 산정한 후
각 실행계획을 비교해서 최고의 효율을 가지고 있는 실행계획을 판별한 후
그 실행계획에 따라 쿼리를 수행하게 한다.
종류
실행 계획을 세우는 방식에 따라
규칙 기반 옵티마이저와비용 기반 옵티마이저로 나뉜다.
규칙 기반 옵티마이저(RBO)
오라클8 이하의 버전에서 기본으로 설정된 옵티마이저가 바로 규칙 기반 옵티마이저이다.
규칙 기반 옵티마이저는 말 그대로 실행 속도가 빠른 순으로 규칙을 먼저 세워두고
우선순위가 앞서는 방법을 채택하는 것이다.과거에는 옵티마이저의 비용을 예측하는 능력이 그다지 좋지 않아 이러한 방식을 사용하였습니다.
규칙의 우선순위는 아래와 같다.
우선순위에 순위가 매겨져 있기 때문에
옵티마이저에서 실행계획이 세워지는 것을 미리 예측할 수 있는 장점이 있으며원하는 대로 실행 계획이 세워지도록 유도할 수도 있다.
단 독이 될수도 있다.
예를 들어, 테이블에 데이터가 몇 개 없을 경우 전체 조회를 했을 경우
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을 실행합니다.
옵티마이저를 최적으로 활용하기 위해 다루어야 할 내용들
통계 정보
비용 기반 옵티마이저에서는 실행 계획 도출 시 DBMS에서 제공하는 통계 정보를 사용한다.
이러한 통계정보는 꾸준히 갱신되고 있는 것이 좋다.
통계 정보의 생성 주기 및 수행 시간을 스케줄러에 일괄적으로 등록하여
일정 주기마다 통계 정보를 수집하고 종료하도록 자동 통계 정보를 생성할 수도 있고
사용자가 직접 수동으로 통계 정보를 생성할수도 있다.
<
DBMS_STATS 패키지를 사용하면
데이터베이스, 스키마 및 계정, 테이블 인덱스 단위로 구분하여 통계 정보를 수집할 수 있다.
옵티마이저에 영향을 줄 수 있는 파라미터
옵티마이저가 최적의 실행 계획을 결정할 때 파라미터의 값도 매우 중요한 영향을 미친다.
예를 들어 OPTIMIZER_MODE는 옵티마이저의 모드를 결정하는 파라미터인데
중간에 옵티마이저 모드가 변경된다면 실행계획이 꼬일 수도 있다.이와 같이 옵티마이저에 직접적으로 영향을 줄 수 있는 파라미터들은
개발하고자 하는 방향으로 설계단계에서 최적화로 설정하고 바꾸지 않는 것이 좋다.
참고
