3.1 SQL의 실행계획

3.1. SQL과 옵티마이져

  • Optimizer는 SQL문을 수행하기 위하여 가장 효과적인 방법을 선택한다.
  • 옵티마이져는 실행계획(Execution Plan)을 세우고 문장을 실행하기 전에 가장 효과적인 계획을 선택합니다.
  • 옵티마이저의 궁극적인 목표는 어느 길로 가면 처리 Driving Range를 가장 최소화 시키고보다 싼 처리비용으로 처리할 수 있도록 하는 것이다.
  • 이처럼 최적화를 위하여 실행계획에 영향을 미치는 요소는 인덱스클러스터옵티마이저 모드수립된 통계정보, SQL문장의 형태시스템 및 네트워크 상태옵티마이져 버전 등 종합적으로 감안되어 실행계획이 결정된다.

3.1.1. 옵티마이저와 우리의 역할

  • 옵티마이저는 존재하지 않는 길을 ‘개척해 주는 존재가 아니라 단지 이미 존재하는 길들 중에서 하나를 ‘선택해 주는 역할을 할 뿐이다.
  • 우리의 역할은 사전에 Optimizing Factor(인덱스 구성에 대한 전략과 적절한 SQL작성)를 제공하는 것이다.

 

3.1.2 옵티마이져와 형태

  • 옵티마이져에는 다음의 두 가지 형태가 있다.
    • 규칙기준 옵티마이저(RBO, Rule-based Optimizer)   -     인덱스클러스터링의 상태와 사용된 연산자의 형태에 따라 Ranking을 부여하고 가장 좋은 순위를 가지는 형태가 가장 최적이라고 판단하는 기준
    • 비용기준 옵티마이저(CBO, Cost-based Optimizer)  - 다양한 통계정보를 이용하여 실제로Cost를 계산해 보고 가장 최소의 비용이 드는 처리형태를 선택하는 기준
  • 옵티마이져 모드 설정

         OPTIMIZATION_MODE = COST

          OPTIMIZATION_MODE = RULE

 

3.1.2.1. 규칙기준 옵티마이져

§          RBO는 다음의 우선 순위에 따라 연산을 수행한다.

1) ROWID 1로우 엑세스
2) 
클러스터 조인에 의한 1로우 엑세스
3) Unique HASH Cluster
에 의한 1로우 엑세스
4) Unique INDEX
에 의한 1로우 엑세스
5) CLUSTER 
조인
6) Non Unique HASH Cluster Key
7) Non Unique Cluster Key
8) Non Unique 
결합 인덱스
9) Non Unique 
한 컬럼 인덱스
10) 
인덱스에 의한 범위 처리
11) 
인덱스에 의한 전체 범위 처리
12) Sort Merge Join
13) 
인덱스 컬럼의 MIN, MAX 처리
14) 
인덱스 컬럼의 ORDER BY
15) 
전체 테이블 스캔

 

수행단계

1) 수행 가능한 실행 계획을 구한다.

2) Rank Table에 따라서 순위를 결정한다.

3) 가장 높은 순위를 선택하여 수행한다. 

규칙기준 옵티마이저의 단점

§          통계정보라는 현실요소를 무시함으로 판단의 오차가 크게 발생한다.

§         Rows수 구별 못함 : TAB1 => 1,000,000 로우  , TAB2 => 100 로우 일 때 테이블 조인 시 TAB1 테이블과  TAB2 테이블의 로우 수를 알지 못하므로 어느쪽을 ‘전체테이블 스캔’ 하는 것이 유지한지 구별하지 못한다.

§         평균 분포도 구별 못함 :

§         만약 2종류의 값만 가지고 테이블 TAB1이 있을경우,

평균 분포도가 50%인 컬럼A로 가진 인덱스

평균 분포도가 0.01%인 컬럼B로 가진 인덱스

 

예:

SELECT * FROM emp
WHERE A=’10’

AND B LIKE ‘123%’;

비록 컬럼B로 구성된 인덱스가 처리범위가 훨씬 좁다 할지라도 무조건 ‘=’로 사용한 컬럼A를 사용하는 것이 유리하다 판단한다.
분포도를 고려하지 않으므로 인덱스 선정 시 판단 착오가 있을 수 있다.
RBO
 Equal(=) 연산을 Range연산보다 위에 둔다.

 

 

 

 규칙기준 옵티마이저의 장점

    • 매우 규칙적이고 분명하여 사용자가 정확히 예측 할 수 있다.
    • 우선순위에 근거한 판단과 보편적인 파악이 가능한 요소 이용 할 수 있다.
      • FROM 절 뒤에 나오는 테이블의 순서로 Join Driving 테이블 선정
      • WHERE 조건에서 어떤 인덱스가 사용될 지
    • 전략적인 인덱스를 구성할 수 있다면 이 규칙의 보편 타당성이 매우 높아짐
      • 전략적인 인덱스가 구성되어 있고적절한 SQL 사용하여 실무에서 그 결과를  TRACE로 출력해보면 예외가 발생하는 경우를 쉽게 확인할 수 있다.

3.1.2.2. 비용기준 옵티마이저(CBO)

  • 비용기준 옵티마이저
    • 관계형 데이터베이스가 추구하는 이상형
    • 대부분의 관계형 데이터베이스 제품은 CBO만 보유
    • 처리방법들에 대한 비용을 산정 후가장 적은 비용이 들어가는 처리방법을 선택한다.
    • 통계정보
      • 테이블의 Rows 
      • 블록 수
      • 블록 당 평균 Rows 
      • Rows의 평균 길이
      • 컬럼별 상수 값의 종류
      • 분포도컬럼 내 NULL 값 수
      • 클러스터링 팩터
      • 인덱스의 Depth, Level 
      • 최대-최소값
      • Leaf 블록수
      • 가동시스템의 I/O  CPU 사용정보

               수행단계

                1) Optimizer는 Access Path와 Hint등을 이용하여 가능한 실행계획들을 수립

                2) 통계정보를 근거로 각 실행계획의 비용을 산정

                3) 가장 적은 비용의 SQL를 선택

 

 

 

비용기준 옵티마이저의 장점

§         현실을 감안한 최적화 (분포도)

컬럼 내부 분포도를 고려한 실행 계획 수립. RBO에서 보았던 Equal(=) 연산이 Range 연산보다 우위에 있다는 부분이 CBO에서는 통하지 않는다.
분포도가 ‘좋다라는 의미는 컬럼값의 종류가 많다는 것이다이는 곧 조건을 만족하는 처리 범위가 좁다는 것을 의미하므로 일의 양을 결제하는 가장 중요한 요소이다

 

§         통계정보 관리를 통한 제어(어떻게 통계정보를 관리할 것인가?)

적절한 통계정보를 관리하는 것은 옵티마이저가 보다 양호한 실행계획을 수립할 수 있도록 하는데 중요한 역할을 한다.
가장 좋은 방법은 DBMS가 제공하는 ‘테이블 모니터링 기능을 이용하는 것이다이들은 테이블의INSERT,UPDATE,DELETE에 대한 발생정보를 개략적으로 모니터링 해 준다.(DBA/ALL/USER_TAB_MODIFICATIONS로 확인 가능)

 

통계정보 수집하는 방법수집 주기 결정해야 한다 (GATHER_DATABASE_STAT)

 

비용기준 옵티마이저의 단점

  • 실행계획 예측이 곤란
    • 테이블의 컬럼의 통계 정보를 알고 있어도 예측대로 실행 계획이 나오리라는 보장이 없다.
    • Bind변수를 사용하느냐 Literal 변수를 사용하느냐에 따라서도 달라질 수 있고 시스템 상태에 따라서도 달라질 수 있다.
    • 통계 정보 갱신 전후로 다른 실행 계획을 가질 수 있다.
    • 버전에 따른 변화
      • DBMS 제품이나 버전에 따라 결과가 많은 차이가 발생한다.
      • DBMS 업그레이드 등을 통해 바뀌어진 옵티마이저 특성 때문에 실행 계획이 바뀌어 질 수 있다.
      • 이는 더 좋은 방향으로 갈 수 있으나부분범위처리 쿼리가 전체범위 처리 쿼리로 바뀌는 등의 부작용을 일으키기도 한다.
      • 실행계획 제어가 곤란
        • RBO 처럼 마음대로 실행계획을 제어 할 수 없다.
        • Hint와 같은 보조수단을 사용해야 제어 가능하다.


옵티마이저의 발전 방향

테이블들은 실로 다양한 특성을 가지고 있으므로 이제 일일이 SQL에 관심을 가지기 보다는 테이블이나 인덱스 단위로 관리를 하여 보다 높은 확률의 최적화를 유도해 가는 것이 지향해야할 방향이자 전략이다.


통계정보 관리를 위한 제언

  • 효과적인 통계정보의 수집과 관리를 위해 DBMS_STATS 패키지 이용하자.
  • 우리는 원하는 시간대의 통계정보를 수집하여 저장해 두었다가 필요할 때 반영시켜 상황에 따라 실행계획을 다르게 적용할 수 있다.
  • 견본 데이터는 5%이하로 선정하는 것이 바람직하다.

DBMS_STATS.ESTIMATE_PERCENT

DBMS_STATS.AUTO_SAMPLE_SIZE

DBMS_STATS.GATHER_SYSTEM_STATS

DBMS_STATS.IMPORT_SYSTEM_STATS

DBMS_STATS.RESTORE_SYSTEM_STATS

DBMS_STATS.PURGE_STATS

 참고) ANALYZE 명령을 직접 사용하여 수집한 경우관리할 수 없으므로 가능하면 패키지 사용을 권고