[DataBase] 옵티마이저(Optimizer)와 실행계획

옵티마이저란?

•옵티마이저(Optimizer)는 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행한다. 이러한 실행 방법을 실행계획(Execution Plan)이라고 한다.

 

•JAVA, C등과 같은 프로그램 언어와는 달리 SQL은 사용자의 요구사항만 기술할 뿐 처리과정에 대한 기술은 하지 않는다. 사용자 요구사항을 만족하는 결과를 추출할 수 있는 다양한 실행 방법들 중에서 최적의 실행 방법을 결정하는 것이 바로 옵티마이저의 역할이다.

 

•옵티마이저가 선택한 실행 방법의 적절성 여부는 질의의 수행 속도에 가장 큰 영향 미치게 된다.
최적의 실행 방법 결정이라는 것은 어떤 방법으로 처리하는 것이 최소 일량으로 동일한 일을
처리할 수 있을지 결정하는 것이다.

 

•옵티마이저는 실제로 SQL문을 처리해보지 않은 상태에서 최적의 실행 방법을 결정해야 하는 어려움이 있다.

 

옵티마이저의 종류

규칙기반 옵티마이저(Rule-Based Optimizer)

–Oracle 9i 까지 (RBO, CBO 혼합 사용)

–15개의 규칙 중 가장 높은 순위를 사용하여 execution plan을 생성

–실행계획의 예측이 가능하기 때문에 사용자가 원하는 처리 경로로 유도하기가 수월 함

–순위 2~14위까지는 인덱스를 활용하는 방법이다.

비용기반 옵티마이저(Cost-Based Optimizer)

–Oracle 10g 부터 CBO 사용

–여러 개의 execution plan 중 가장 비용이(cost)가 적은 execution plan을 선택

–비용 산정 요소는 테이블, 인덱스 등의 객체 통계정보와 CPU, 메모리 등의 시스템 통계정보 등을 활용

–정확한 통계정보를 유지하는 것이 비용기반 옵티마이저의 핵심 (정기적인 통계정보 정책 필요)

실행계획이란?

•실행계획(Execution Plan)이란 SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미한다. 실행계획을 생성한다는 것은 SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 작업이다.

 

•동일한 SQL에 대해 결과를 낼 수 있는 다양한 처리 방법(실행계획)이 존재할 수 있지만 각 처리 방법마다 실행 시간(성능)은 서로 다를 수 있다. 옵티마이저는 다양한 처리 방법들 중에서 최적의 실행계획을 예측해서 생성해 준다.

 

•생성된 실행계획을 보는 방법은 데이터베이스 벤더마다 서로 다르다. 실행계획에서 표시되는 내용 및 형태도 약간씩 차이는 있지만 실행계획이 SQL 처리를 위한 절차와 방법을 의미한다는 기본적인 사항은 모두 동일하다.

 

•실행계획을 보고 SQL이 어떻게 실행되는지 정확히 이해할 수 있다면 보다 향상된 SQL 이해 및 활용을 할 수 있다. (SQL 튜닝의 출발)

 

실행계획의 단계

•Explain Plan : SQL 수행 전 실행계획

•AutoTrace : SQL 수행 전 실행계획 + SQL 수행 후 결과 및 실행통계

•SQL Trace : SQL 수행 후 실행계획 + 통계

 

SQL의 처리 과정

•Query Transformer : Optimizer가 최적화하기 쉬운 형태로 쿼리 변환

•Estimator : 최적의 실행계획을 생성하기 위해 통계정보 사용하여 비용 계산

•Plan Generator : 후보군이 될만한 실행계획들을 생성