서영이네집 :: Cost Based Optimizer의 작동 원리

※ 이 글은 "실전 SQL 튜닝" 강의를 듣고 개인적인 의견과 함께 정리한 내용입니다.

CBO는 다음과 같은 순서를 거쳐서 수행 됩니다.

Parsing 단계
파싱 단계는 SQL 구문(syntax)과 의미검사(semantics)를 수행합니다. 예를 들어 SQL 구문이 정확한지 검사하고 참조된 테이블에 대해 사용자의 접근권한을 검사합니다. SQL은 파싱트리(parsing tree) 형태로 변형되어 옵티마이저에게 넘겨집니다.
옵티마이저(Qeury Optimizer)
위 그림에서 점선형태의 사각형으로 표시된 부분이 옵티마이저의 주요 구성요소입니다. 각 구성요소의 역할은 아래와 같습니다.
질의 변환(Query Rewriter 또는 Transformer)
질의 변환 단계는 파싱트리를 받아들여서 질의 변환을 수행합니다. 질의 변환은 사용자가 작성한 SQL문을 더 나은 실행계획을 찾을 수 있는 SQL문으로 변환합니다.
실행계획 생성(Plan Generator)와 Estimator
오라클 옵티마이저는 실행계획 생성과 비용산정 모듈을 수행하기 전에 질의에서 사용된 모든 테이블들과 각 테이블에서 정의된 인덱스들에 관해 기본적인 통계정보들(테이블 블럭수, 로우의 평균길이, 인덱스의 높이, 인덱스 리프 블록의 수 등)과 각 테이블에 대한 다양한 엑세스 경로(Full table scan, Index scan 등)에 대한 비용정보를 미리 산출하여 Cost Model을 만듭니다. 이러한 일련의 정보를 바탕으로 옵티마이저는 최적의 실행계획을 생성하여 'Best Query Plan'을 작성합니다.

CBO는 위와 같이 작동하기 때문에 RBO(Rule Based Optimizer)에는 없는 CBO에서만 가능한 기능들이 있습니다

실행계획 중 cost가 가장 적은 실행계획을 선택
컬럼별 데이터 분포에 대한 통계정보(=히스토그램) 사용
복잡한 관계표현에서 때로 잘못된 실행계획을 수립할 수도 있는데 이런 경우 힌트를 사용하여 수정하도록 함
오브젝트에 대한 통계정보를 기준으로 실행계획을 작성하므로 통계정보가 제대로 생성되어 있지 않은경우, 응용프로그램의 성능에 악영향을 미치게 되므로 주의
Cost engine은 I/O cost, Network cost, CPU cost 등도 고려하도록 설계되어 있음

※ 통계정보 : 테이블의 데이터 건수, 평균 실이, 컬럼 별 distinct 값의 수, 인덱스 leaf node의 depth 등이 저장

크리에이티브 커먼즈 라이센스

서영이네집