옵티마이져의 종류
옵티마이저는 최적화 방식에 따라 크게 비용기반과 규칙기반으로 구분합니다.
규칙기반 옵티마이저는 미리 정해놓은 규칙에 따라 엑세스 경로를 평가하고 실행계획을 선택합니다. 인덱스 구조, 연산자, 조건절 형태에 따라 규칙으로 정해진 순서에 따라 쿼리를 처리합니다. 비용기반 옵티마이저는 쿼리 실행에 소요되는 예상소요시간을 기반으로 동작합니다.
비용기반 옵티마이저
비용기반 옵티마이저는 사용자 쿼리를 위해 후보군이 될만한 실행계획과 데이터 딕셔너리에 수집해둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그 중 가장 낮은 비용의 실행계획 중 하나를 선택합니다. 활용하는 통계정보로는 데이터의 양, 칼럼 값의 수, 칼럼 값의 분포, 인덱스 높이, 클러스터링 팩터 등이 있습니다.
비용기반 옵티마이저도 규칙을 사용합니다.
버퍼캐시의 크기는 런타임 시 고려할 사항입니다.
비용기반 옵티마이저는 최적화 중 3개의 서브 엔진을 사용합니다.
- query transformer
사용자로부터 전달받은 SQL을 최적화에 유리한 형태로 변환을 시도합니다.
- estimator
쿼리 오퍼레이션 각 단계의 선택도, 카디널리티, 비용을 계산하고 궁극적으로 실행계획 전체에 대한 비용을 계산합니다.
- plan generator
하나의 쿼리를 수행할때, 후보군이 될만한 실행계획을 생성합니다. 옵티마이저는 가장 비용이 적은 엑세스 경로를 선택합니다. 사용자가 특정 방식을 사용하도록 힌트를 통해 강제하면 옵티마이저는 비용에 상관없이 그 경로를 선택합니다.
규칙기반옵티마이저
규칙기반옵티마이저는 조건절에 인덱스가 있으면 무조건 인덱스를 사용합니다. FULL TABLE SCAN은 15순위로 우선순위가 늦기때문입니다.
규칙기반 옵티마이저는 통계정보를 전혀 활용하지 않고 단순 규칙에만 의존합니다.
ORDER BY 절에 인덱스가 있으면 FULL TABLE SCAN 보다 한 단계 순위가 높기때문에 인덱스를 이용합니다. 부분범위처리가 가능한 상황을 제외하고 정렬을위해 인덱스로 전체 레코드를 엑세스 하는 것은 효율적이지 못합니다.
인덱스 칼럼에 대한 BETWEEN 조건이 부등호 조건보다 높기 때문에 인덱스를 사용합니다.
SELF-LEARNING OPTIMIZER
- ADAPTIVE CURSOR SHARING
처음 실행 시 특정 실행계획으로 실행했다가 바인드 변수에 다른 값이 입력되면 다른 실행계획을 추가적으로 생성하고 바인드 변수 값의 분퐁 따라 실행계획을 선택적으로 사용하는 기능
- CARDINALITY FEEDBACK
최초 실행계획을 수립할 때, 추정했던 카디널리티와 실제 실행 과정에서 읽은 결과 간의 차이가 크다고 판단되면 조정된 카디널리티 값을 저장해 두었다가 다음 번 실행시에 활용하여 다른 실행계획이 수립되도록하는 기능
- ADAPTIVE PLANS
런타임에 비용을 고려해 실행계획을 변경하는 기능입니다.
최적화 목표
전체 처리속도 최적화
쿼리의 결과집합을 끝까지 읽는 것을 전제로 시스템 리소스를 가장 적게 사용하는 실행계획을 선택합니다. 대부분의 DBMS 옵티마이저는 전체 처리속도 최적화를 목표로 합니다. 옵티마이저의 처리 모드를 변경하는 쿼리는 다음과 같습니다.
TABLE FULL SCAN 과 HASH JOIN을 사용하는 경향이 높습니다.
-- 시스템 전체의 처리범위 변경
ALTER SYSTEM SET OPTIMIZER_MODE = ALL_ROWS;
-- 세션별 처리범위 변경
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS
-- 쿼리의 처리범위 변경
SELECT /*+ALL_ROWS*/ * FROM T WHERE .. 최초 응답속도 최적화
일부만 읽다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택합니다. 이 모드에서 결과를 끝까지 읽는다면, 전체 처리속도 최적화 실행계획에 비해 속도가 느릴 수 있습니다. 옵티마이저 힌트를 통해 최초 N개를 처리하는 것을 전제로 실행계획을 선택할 수 있습니다.
INDEX RANGE SCAN과 NL JOIN을 사용하는 경향이 높습니다.
SELECT /*+FIRST_ROWS(10)*/ * FROM WHERE
옵티마이저 행동에 영향을 미치는 요소
SQL 연산자의 형태
SQL을 어떤 형태로 작성했는지, 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있습니다.
옵티마이징 팩터
인덱스, IOT, 클러스터링, 파티셔닝, MV 등의 구성에 따라 실행계획과 성능이 크게 달라집니다.
DBMS 제약
개채 무결성, 참조 무결성, 도메인 무결성 등을 위해 DBMS가 제공하는 PK, FK, CHECK, NOT NULL과 같은 제약 설정을 이용할 수 있고, 제약 설정은 옵티마이저가 쿼리 성능을 최적화하는데 매우 중요합니다. 예를 들어, 인덱스 구성 칼럼에 NOT NULL 제약이 설정돼 있으면, 옵티마이저는 전체 개수를 구하는 COUNT 쿼리에 인덱스를 활용할 수 있습니다.
옵티마이저의 한계점
- 부족한 옵티마이징 팩터 ( 인덱스 구성, IOT, 클러스터링, 파티셔닝 등 )
- 부정확한 통계
- 결합 선택도 산정의 어려움
- 바인드 변수 사용 시, 히스토그램 사용 제약
- 비현실적인 가정과 규칙에 의존
- 최적화 시간에 허용된 시간 제약
일반적으로 사용하는 옵티마이저는 정해진 시간 내에 최적화를 수행해야하므로 정보를 충분히 활용하지 못합니다. 오라클의 경우 튜닝 모드에서 오프라인 옵티마이저를 구동하면, 시간의 제약없이 다이나믹 샘플링을 포함한 다양한 정보와 기법을 활용할 수 있습니다.
라이브러리 캐시공간의 크기는 옵티마이저에 영향을 주지않습니다. 다만 공간이 부족하면 sql 실행계획이 캐시에서 자주 밀려나므로 파싱과 최적화를 자주 수행해야합니다.
통계정보를 이용한 비용계산 원리
실행계획을 수립할 때, 비용기반옵티마이저는 SQL 문장에서 엑세스할 데이터 특성을 고려하기 위해 통계정보를 이용합니다. 옵티마이저가 참조하는 통계정보 종류는 다음과 같습니다.
- 테이블 통계 ( 레코드 수, 블록 수, 평균 행 길이)
- 인덱스 통계(인덱스 높이, 리프 블록 수, 클러스터링 팩터)
- 칼럼 통계(중복을 제거한 칼럼값의 수, 최소값, 최대값, null 값 수, 밀도, 평균칼럼길이, 히스토그램 등)
- 시스템 통계 (Cpu 속도, 평균적인 Single block I/O 속도, 평균적인 Multiblock I/O 속도, 평균적인 Multiblock I/O 수,
I/O 서브시스템의 최대 처리량, 병령 SLAVE의 평균적인 처리량)
선택도
선택도는 전체 레코드 중 특정 조건에 의해 선택될 것으로 예상되는 레코드의 비율을 말합니다. 선택도를 통해 비용을 산출해 인덱스 사용 여부, 조인 순서와 방법등을 결정합니다. 선택도는 최적의 실행계획을 수립하는데 가장 중요한 요인입니다. 데이터 분포가 균일하다고 가정할 때, 특정 칼럼의 선택도를 구하는 공식은 다음과 같습니다.
선택도 = 1 / DISTINCT VALUE의 수 카디널리티
카디널리티는 특정 액세스 단계를 거친 후, 출력될 것으로 예상되는 결과 건수를 말합니다. 산출방법은 다음과 같습니다.
카디널리티 = 총 로우수 * 선택도 = num_rows / num_distinct
예를 들어, 부서 컬럼의 DISTINCT VALUE 수가 10 이면 선택도는 0.1입니다. 총 사원 수가 1000명이라면 부서 당 카디널리티는 100이 됩니다. 직급의 도메인이 [부장, 과장, 대리, 사원] 이면 DISTINCT VALUE의 수가 4이므로 선택도는 0.25입니다. 조건절이 두 개 이상인 쿼리는 각 컬럼의 선택도를 곱한 값을 전체 로우 수에 곱하면 카디널리티를 구할 수 있습니다.
-- 부서(0.1) * 직급(0.25) * 1000 = 25
select * from 사원 where 부서 = :부서 and 직급 = :직급
바인드 변수 사용시 카디널리티 계산
바인드 변수를 사용하면 최초 수행시 최적화된 실행계획을 캐시에 적재하고, 실행시점에는 그것을 그대로 가져와 조건 값만 다르게 바인딩해 재사용합니다. 변수를 바인딩하는 시점이 최적화 시점보다 나중인 실행시점입니다. 즉, SQL을 최적화하는 시점에 조건절 칼럼의 데이터분포를 활용할 수 없습니다.
이로인해, 바인드 변수를 사용할때 옵티마이저가 평균 분포를 가정한 실행계획을 생성합니다. 이런 특성으로 DW, OLAP, BATCH 에서 수행되는 쿼리는 바인드 변수보다 상수를 사용하는 것이 유리합니다.
히스토그램
옵티마이저는 미리 저장된 히스토그램 정보가 있으면, 이를 이용해 정확한 카디널리티를 구할 수 있습니다. 특히, 분포가 균일하지 않은 칼럼으로 조회할 때, 효과적입니다. ORACLE은 도수분포, 높이균형, 상위도수분포, 하이브리드 4가지 유형의 히스토그램을 제공합니다.
- 도수분포 히스토그램
값 별 빈도수를 저장합니다. 칼럼이 가진 값의 수가 적을 때 사용되며, 칼럼 값의 수가 적기 때문에 각각 하나의 버킷을 할당합니다. 버킷의 수가 NDV(256)개보다 작을 때 사용할 수 있습니다.
- 높이균형 히스토그램
칼럼이 가진 값의 수가 많아 하나의 버킷을 할당하기 어려운 경우 사용합니다. 히스토그램 버킷을 값의 수보다 적게 할당하기 때문에, 하나의 버킷에 여러 값을 할당합니다. 값의 범위별 분포를 확인하는데 유용홥니다
- 상위도수분포
많은 레코드를 가진 상위 N개 값의 빈도 수를 저장합니다.
- 하이브리드
도수분포와 높이균형 히스토그램의 특성을 결합한 형태입니다.
비용
비용은 쿼리를 수행하는데 소요되는 예상 일량(시간)을 뜻합니다. 옵티마이저 비용 모델에는 I/O 비용 모델과 CPU 비용 모델 두 가지가 있습니다. I/O 비용 모델은 예상되는 입출력 요청 횟수만을 쿼리 수행 비용으로 간주해 실행계획을 평가하고, CPU 비용 모델은 여기에 시간 개념을 더해 비용을 산정합니다.
I/O 비용 모델
예상되는 디스크 I/O CALL의 횟수를 의미합니다. CPU 비용 모델은 I/O 비용모델의 예상 시간과 CPU 사용시간을 구한 후, SINGLE BLOCK I/O 시간으로 나눈 값을 비용 값으로 사용합니다.
인덱스를 경유한 테이블 액세스의 비용
I/O 비용 모델에서의 비용은 디스크 I/O Call이 발생한 횟수를 의미합니다. 인덱스를 경유할 시, 테이블 엑세스는 SINGLE BLOCK I/O 방식이 사용됩니다. 이 경유 읽게될 물리적 블록 개수가 I/O CALL 횟수와 일치합니다.
비용 = BLEVEL -- 인덱스 수직 탐색 비용
+ (리프 블록 수 * 유효 인덱스 선택도) -- 인덱스 수평탐색 비용
+ (클러스터링 팩터 * 유효 테이블 선택도) -- 테이블 랜덤 엑세스 비용
- BLevel : 브랜치 레벨을 의미하며, 리프 블록에 도달하기 전에 읽게 될 브랜치 블록의 수.
- 클러스터링 팩터 : 특정 칼럼을 기준으로 같은 값 데이터가 모여있는 정도를 의미합니다. 인덱스를 경유해 테이블 전체 로우를 액세스할때 읽을 것으로 예상되는 논리적 블록의 수를 기준으로 합니다.
- 유효 인덱스 선택도 : 전체 인덱스 레코드 중, 조건절을 만족하는 레코드를 찾기 위해 스캔해야할 것으로 예상되는 비율. 리프 블록에는 인덱스 레코드가 정렬된 상태로 저장되므로 이 비율이 곧 방문할 리프 블록의 비율을 의미합니다.
- 유효 테이블 선택도 : 전체 레코드 중, 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율. 클러스터링 팩터는 인덱스를 경유해 전체 로우를 엑세스 할때 읽힐 것으로 예상되는 테이블 블록의 수 이므로, 클러스터링 팩터에 유효 테이블 선택도를 곱하면 조건절에 의해 읽힐 것으로 예상대는 테이블 블록의 수를 구할 수 있습니다.
FULL TABLE SCAN에 의한 테이블 엑세스 비용
테이블 전체를 순차적으로 읽어 들이는 과정에서 발생하는 I/O CALL 횟수를 비용으로 계산합니다. 여러 블록을 읽어 들이는 MULTIBLOCK I/O 방식을 사용하므로 총 블록 수를 MULTIBLOCK I/O 단위로 나눈 만큼 I/O CALL이 발생합니다. 따라서 MULTIBLOCK I/O 단위가 클수록 예상비용이 줄어듭니다.
