오라클 옵티마이저 [대용량 데이터 베이스 튜닝 및 설계 3일차]

오라클 옵티마이저 [대용량 데이터 베이스 튜닝 및 설계 3일차]

 

(Optimizer)

 

 

옵티마이저란?

---

 

 

정의

 

사용자가 요청한 SQL을 가장 효율적이고 빠르게 수행할 수 있는 최적(최저비용)의 처리경로를 선택해주는 DBMS의 핵심 엔진

 

 

역할

조화된 질의언어(SQL)로 사용자가 원하는 결과 집합을 정의하면 이를 얻는데 필요한 처리절차(Procedure)는 DBMS에 내장된 옵티마이저가 자동으로 생성

 

종류

 Rule-Based Optimizer (10g부터 지원 중단)

 Cost-Based Optimizer

 

특징

 

미리 정해놓은 우선순위에 따라 액세스 경로를 평가하고 실행계획 선택

인덱스 구조, 연산자, 조건절 형태가 순위를 결정하는 주요인

 

 

 

(Rule-Based Optimizer)

 

 

Rule-Based Optimizer

---

 

 

데이터 특성 무시하므로 대용량 데이터 처리시 불합리 할 경우가 많다

예) 조건절 컬럼에 Index가 있으면 무조건 Index 사용하며, Full Table Scan과의 손익을 따지지 않는다

예측 가능하고 일관성 있는 실행계획을 수립하며 사용자가 원하는 처리경로로 유도하기가 쉽다 

 

 

 

 

(Cost-Based Optimizer)

 

Cost-Based Optimizer

---

 

 

비용(쿼리를 수행하는데 소요되는 일량 또는 시간 )을 기반으로 최적화를 수행

전통적인 I/O 비용 모델에서는 I/O 요청(Call) 횟수만을 비용으로 평가했지만, 최근 도입된 CPU 비용 모델에서는 CPU 연산 비용까지 감안

수행 일량을 상대적인 시간 개념으로 환산해 비용을 평가

CBO가 실행계획을 수립할 때 판단 기준이 되는 비용은 예상치

미리 구해놓은 여러 통계정보를 기초로 각 오퍼레이션 단계별 예상 비용을 산정하고, 이를 합산한 총 비용이 가장 낮은 실행계획 하나를 선택

 

 

 

옵티마이저 모드

---

 

 

옵티마이저 모드로 선택할 수 있는 값은  5가지

 

 

rule

  Rule-Based Optimizer(RBO) 모드를 선택하고자 할 때 사용 

all_rows 
  쿼리 최종 결과 집합을 끝까지 Fetch 하는 것을 전제로 시스템 리소스(I/O, CPU, Memory 등)를 가장 적게 사용하는 실행계획 선택
  DML 문장 : 모두 ALL_ROWS 모드
  SELECT 문장 : UNION, MINUS 등 집합 연산자, FOR UPDATE절 사용 시 ALL_ROWS 모드
  PL/SQL  : 힌트 사용, 기본 모드가 RBO 모드인 경우를 제외하면 항상 ALL_ROWS 모드로 동작 

first_rows

  전체 결과집합 중 일부 로우만 Fetch하다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획 선택
  끝까지 Fetch 시 더 많은 리소스 사용으로 전체 수행속도가 느려질 수 있음
  비용과 규칙을 혼합한 형태의 옵티마이저 모드 (∵ 통계정보를 활용하여 RBO 규칙 적용)
  예 : ORDER BY 컬럼에 인덱스가 있으면 비용과 상관없이 무조건 인덱스를 이용해 sort order by

first_rows_n

  n개의 로우만 fetch하는것을 전제로 가장 빠른 응답 속도를 낼수 있는 실행 계획을 선택
  n으로 지정할수 잇는 값은 1, 10, 100, 1000
  사용자가 n개 로우 이상을 Fetch하면 오히려 더많은 리소스를 사용하여 더 느려질수 있음
  first_rows와 달리 first_rows_n은 완전한 CBO 모드로 동작

choose

  액세스 되는 테이블 중 적어도 하나에 통계정보가 있다면 CBO (all_rows) 모드 선택
  액세스 되는 어느 테이블에도 통계정보가 업으면 RBO 선택
  9i 까지는 CHOOSE가 기존설정 10g 부터 all_rows가 기본모드
  10g부터 rbo를 공식지원하지 않고 동적 샘플링 기본레벨이 2로적용

 

 

시스템 레벨, 세션 레벨, 쿼리 레벨에서 바꿀 수 있다.

 

alter system set optimizer_mode = all_rows ;   -- 시스템 레벨 변경
alter session set optimizer_mode = all_rows ; -- 세션 레벨 변경
select /*+ all_rows */ * from t where ... ;    -- 쿼리 레벨 변경

 

 

 

옵티마이저 모드 선택

---

 

FIRST_ROWS

   일반적인 OLTP 환경에 적합

   SQL 결과 집합중 일부만 Fetch하고 멈추는 것을 전제로 가장 효율적인 실행계획을 요구

   2-Tier 환경의 클라이언트/서버 구조에 적합

   전체 결과 집합이 아무리 많아도 사용자가 스크롤을 통해 일부만 Fetch하다가 멈출수 있음

   전체 Fetch하거나 다른 쿼리를 수행하기 전까지 SQL커서는 오픈된 상태를 유치

 

 

ALL_ROWS

   DW나 배치 프로그램

   sql 결과 집합을 모두 Fetch하기에 가장 효율적인 실행계획을 옵티마이저에게 요구

   OLTP중 웹 애플리케이션 등

   3-Tier구조는 클라이언트와 서버 간 연결을 지속하지 않는 환경이며 오픈 커서를 계속 유지 할수 없음

   rownum으로 결과집합을 10건 내지 20건으로 제한, 집합자체를 소량으로 정의

   애플리케이션 특성상 FIRST_ROWS가 적합하다는 판단이 서지 않으면 ALL_ROWS를 기본모드로 선택

   필요시 쿼리또는 세션레벨에서 FIRST_ROWS 모드로 전환할 것을 권고