옵티마이저 이해하기 - 2

옵티마이저

SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS의 내부의 핵심엔진

사용자가 SQL로 결과집합을 요구하면 이를 생성하는데 필요한 처리경로는 옵티마이저가 자동으로 생성해준다.

1. 사용자가 던진 쿼리수행을 위해, 후보군이 될만한 실행계획을 찾는다.

2. 데이터 딕셔너리에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행 계획의 예상 비용을 산정한다.

3. 각 실행계획을 비교해서 최저비용을 갖는 실행계획을 선택한다.

옵티마이저의 종류

1. 규칙기반 옵티마이저(Rule- Based Optimizer, RBO) - 액세스 경로별 우선순위로서, 인덱스 구조, 연산자, 조건절형태가 순위를 결정짓는 요인이다.

2. 비용기반 옵티마이저(Cost-Based Optimizer, CBO) - 비용기반으로 최적화를 수행한다.

최적화목표

1. 전체 처리속도 최적화 - 시스템리소스(I/O, CPU, 메모리 등)를 가장 적게 사용하는 실행계획을 선택한다.

2. 최초 응답속도 최적화 - 일부만 읽다락 멈추는 것을 전제로, 가장빠른 속도를 낼 수 있는 실행계획을 선택한다.

옵티마이저에 영향을 미치는 요소

• SQL과 연산자형태

옵티마이징 팩터인덱스, IOT, 클러스터링, 파티셔닝, MV등을 어떻게 구성했는지에 따라 달라진다.

• DBMS제약

PK, FK, CHECK, NOT NULL등의 제약조건은 쿼리성능을 최적화하는데 중요한 정보 제공

• 옵티마이저 힌트
• 통계정보

CBO의 모든 판단 기준은 통계정보에서 나온다.

• 옵티마이저 관련 파라미터의 추가 또는 변경
• DBMS버전과 종류

옵티마이저의 한계

현재로선 해결하기 어려운 문제나, 통계정보 수집량과 최적화를 위해 허락된 시간때문에 옵티마이저의 한계가 드러남

• 옵티마이징 팩터의 부족
• 통계정보의 부정확성
• 바인드 변수 사용시 균등분포 가정

조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용을 계산한다.

• 비현실적인 가정 
• 규칙에 의존하는 CBO

부분적으로는 규칙에 의존한다.

• 하드웨어 성능옵티마이저는 기본적으로 옵티마이저 개발팀이 사용한 하드웨어 사양에 맞춰져있다.

통계정보를 이용한 비용계산 원리

옵티마이저가 참조하는 통계정보의 종류

데이터 딕셔너리에 미리 수집해 둔 통계정보가 옵티마이저에 의해 구체적으로 어떻게 활용될까.

선택도 

전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율을 말한다. 선택도를 가지고, 카디털리티를 구하고, 다시 비용을 구해 인덱스 사용여부, 조인 순서와 방법을 결정한다.

선택도 → 카디널리티 → 비용 → 액세스 방식, 조인 순서, 조인 방법 등 결정 히스토그램이 있으면 그것으로 선택도를 산정하며, 단일 칼럼에 대해서는 비교적 정확한 값을 구한다. 히스토그램이 없거나, 있더라도 조건절에 바인드 변수를 사용하면 옵티마이저는 데이터 분포가 균일하다고 가정한 상태에서 선택도를 구한다. 히스토그램 없이 등치(=) 조건에 대한 선택도를 구하는 공식은 다음과 같다.

카디널러티 

특정액세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수

행의 수 * 선택도

히스토그램 - 미리 저장된 히스토그램 정보가 있으면, 옵티마이저는 그것을 사용해 더 정확하게 카디널리티를 구할 수 있다. 분포가 균일하지 않은 컬럼은 히스토그램으로 더 정확한 카디널리티를 산출할 수있다.

히스토그램 생성은 컬럼 통계수집시 버킷을 2개 이상으로 지정한다.

dba_tab_columns.histogram	설명
FREQUENCY	값별로 빈도수를 저장하는 도수분포 히스토그램 (값의 수 = 버킷 수)
HEIGHT-BALANCED	버킷의 높이가 동일한 높이균형 히스토그램 (값의 수 > 버킷 수)
NONE	히스토그램 없음

도수분포 히스토그램

값별로 빈도수를 저장하는 히스토그램

dba_histograms

컬럼	의미
endpoint_value	버킷에 할당된 컬럼 값
endpoint_number	endpoint_value 정렬 기준 누적 수량

높이 균형 히스토그램

컬럼이 가진 값의 수가 아주 많아 각각 하나의 버킷을 할당하기 어려울때 사용된다.

dba_histograms

컬럼	의미
endpoint_number	버킷 번호
endpoint_value	버킷이 담당하는 가장 큰 값

비용

쿼리를  수행하는데 소요되는 일량, 시간

I/O비용모델

예상되는 I/O요청 횟수만큼 쿼리 수행 비용으로 간주해 실행계획을 평가

인덱스를 경유한 테이블 액세스비용

- I/O 비용 모델에서의 비용은 디스크 I/O Call 횟수(논리적/물리적으로 읽은 블록 개수가 아닌 I/O Call 횟수)를 의미한다. 그리고 인덱스를 경유한 테이블 액세스 시에는 Single Block I/O 방식이 사용된다. 이는 디스크에서 한 블록을 읽을 때마다 한 번의 I/O Call을 일으키는 방식이므로 읽게 될 물리적 블록 개수가 I/O Call 횟수와 일치한다. 따라서 인덱스를 이용한 테이블 액세스 비용은 아래와 같은 공식으로 구할 수 있다. 

비용 = blevel                                                 -- 인덱스 수직적 탐색 비용 

         + (리프 블록 수 × 유효 인덱스 선택도)        -- 인덱스 수평적 탐색 비용

         + (클러스터링 팩터 × 유효 테이블 선택도)   -- 테이블 Random 액세스 비용 

Full Scan에 의한 테이블 액세스비용

Full Scan에 대해서는, 테이블 전체를 순차적으로 읽어 들이는 과정에서 발생하는 I/O Call 횟수로 비용을 계산한다. Full Scan할 때는 한 번의 I/O Call로써 여러 블록을 읽어 들이는 Multiblock I/O 방식을 사용하므로 총 블록 수를 Multiblock I/O 단위로 나눈 만큼 I/O Call이 발생한다. 예를 들어, 100블록을 8개씩 나누어 읽는다면 13번의 I/O Call이 발생하고, I/O Call 횟수로써 Full Scan 비용을 추정한다. 따라서 Multiblock I/O 단위가 증가할수록 I/O Call 횟수가 줄고 예상비용도 줄게 된다.

CPU비용모델 - I/O + 시간

출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148218&boardConfigUid=9&categoryUid=216&boardIdx=139&boardStep=1

http://wiki.gurubee.net/pages/viewpage.action?pageId=29065610