이화식 선생님의 새로쓴 대용량 데이터 베이스 1의 온라인 스터디 입니다.
매주 각 스터디 팀원들이 담당 분량을 정리해서 올리고 토론식으로 진행합니다.
스터디 포스팅은 팀원들에의해 이루어지지만 스터디 참여는 사이트 회원 모두가 가능합니다.

진행기간: 2009.07 ~ 2009.10. 종료

3.1 SQL의 실행계획 (1/3)

2009.07.18 15:09

운영자 조회 수:95411

3.1 SQL의 실행계획

3.1. SQL과 옵티마이져
  • 옵티마이저는 아주 복잡하게 되어 있지만 궁극적인 목표는 어느 길로 가면 처리 주관단위(Driving Range)를 가장 최소화 시킬 수 있고, 보다 싼 운반단가를 처리할 수 있도록 하는 것이다.
  • 이처럼 최적화를 위하여 실행계획에 영향을 미치는 요소는 인덱스와 클러스터 뿐만 아니라. 옵티마이저 모드, 수립된 통계정보, SQL문장의 형태, 시스템 및 네트워크 상태, 옵티마이져 버전 등 종합적으로 감안되어 실행계획이 결정된다.

3.1.1. 옵티마이저와 우리의 역할
  • 옵티마이저는 존재하지 않는 길을 ‘개척’해 주는 존재가 아니라 단지 이미 존재하는 길들 중에서 하나를 ‘선택’해 주는 역할을 할 뿐이다.
  • 다양한 형태의 액세스 요구를 만족시키기 위하여 옵티마이징 팩터가 필요하며 우리가 준비해야 할 사항은 인덱스 구성에 대한 전략과 적절한 SQL을 작성하는 것이다.

인용:
시스템 자원을 최소의 노력으로 최대의 효과를 얻도록 하는 것이 우리의 역할이다.



3.1.2 옵티마이저와 형태
  • 옵티마이저에는 다음의 두 가지 형태가 있다.
    • 규칙기준 옵티마이저(RBO)
    • 비용기준 옵티마이저(CBO)

3.1.2.1. 규칙기준 옵티마이져
1.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) 전체 테이블 스캔

가) 규칙기준 옵티마이저의 단점
  • 통계정보라는 현실요소를 무시함으로 판단의 오차가 발생한다.
  • 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만 보유
    • 처리방법들에 대한 비용을 산정 후, 가장 적은 비용이 들어가는 처리방법을 선택한다.
  • 통계정보
    • 테이블의 로우 수
    • 블록 수
    • 블록 당 평균 로우 수
    • 로우의 평균 길이
    • 컬럼별 상수 값의 종류
    • 분포도, 컬럼 내 NULL 값 수
    • 클러스터링 팩터
    • 인덱스의 깊이
    • 최대-최소값
    • 리프 블록수
    • 가동시스템의 I/O 나 CPU 사용정보

가) 비용기준 옵티마이저의 장점
  • 현실을 감안한 최적화

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


  • Histogram을 이용한 더욱 정확한 분포도 고려
  • 통계정보 관리를 통한 제어

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

옵티마이저를 깊이 이해하지 않고 SQL을 작성 하더라도 최소의 성능은 보장된다.


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

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


라) 통계정보 관리를 위한 제언
  • 효과적인 통계정보의 수집과 관리를 위해 DBMS_STATS 패키지 이용하자.
    • 우리는 원하는 시간대의 통계정보를 수집하여 저장해 두었다가 필요할 때 반영시켜 상황에 따라 실행계획을 다르게 적용할 수 있다.

인용:
OLTP환경인 주간과 배치처리 위주의 야간 운영시 서로 다른 시스템 통계정보를 제공 할 수 있다.