옵티마이저 종류
일반적으로 옵티마이저의 종류에는 두가지가 있습니다.
규칙기반 옵티마이저
휴리스틱 옵티마이저라고 불리며, 미리 정해 놓은 규칙에 따라 경로를 평가하고 실행계획을 선택합니다. 규칙이란 엑세스별 우선순위로 인덱스 구조, 연산자, 조건절 형태 등이 있습니다.
비용기반 옵티마이저
쿼리 수행비용을 기반으로 최적화를 수행합니다.
최적화 목표
전체 처리속도 최적화
쿼리 최종 결과집합을 끝까지 읽는 것을 전재로, 시스템 리소스를 가장 적게 사용하는 실행계획을 선택합니다. 유도방법은 다음과 같습니다.
ALTER SYSTEM SET OPTIMIZER_MODE = ALL_ROWS; -- 시스템 레벨 변경
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS; -- 세션 레벨 변경
SELECT /*+ALL_ROWS*/ * FROM T WHERE ... -- 쿼리 례벨 변경
최초 응답속도 최적화
전체 결과 집합 중 일부만 읽다 멈추는 것을 전제로, 가장 빠른 응답속도를 낼 수 있는 실행계획을 선택합니다. 최초 응답속도 최적화 방식으로 데이터를 끝까지 읽는다면 전체 수행속도가 더 느려질 수 있습니다. 유도방법은 다음과 같습니다.
SELECT /*+ FIRST_ROWS(10) */ * FROM WHERE ;옵티마이저 행동에 영향을 미치는 요소
SQL과 연산자 형태
SQL을 어떤 형태로 작성했는지 또는 어떤 연산자를 사용했는지에 따라 결과가 같더라도 성능이 달라집니다.
옵티마이징 팩터
같은 쿼리라도 인덱스, IOT, 클러스터링, 파티셔닝, MV등을 어떻게 구성했는지에 따라 실행계획과 성능이 달라집니다.
DBMS 제약
개체 무결성, 참조 무결성, 도메인 무결성 등을 위해 DBMS가 제공하는 PK, FK, CHECK, NOT NULL 같은 제약설정기능을 이용할 수 있습니다. 옵티마이저가 쿼리 성능을 최적화하는데 중요한 정보를 제공합니다.
옵티마이저의 한계점
옵티마이징 팩터의 부족
옵티마이저는 주어진 환경에서 최적의 실행계획을 수립하기위해 정해진 기능을 수행합니다. 적절한 옵티마이징팩터(효율적인 인덱스, IOT, 클러스터링, 파티셔닝) 을 제공하지 않으면 옵티마이저 자체적으로 최적의 실행계획을 수립하는데 한계가 있습니다.
통계정보의 부정확성
최적화에 필요한 모든 정보를 수집하는 것은 불가능합니다. 특히 칼럼분포가 고르지 않을때 필요한 칼럼 히스토리는 수집하고 유지하는 비용이 높습니다. 칼럼이 결합했을때의 모든 결합분포를 미리 구해두기 어려운 것도 제약사항입니다. 잘못 수집된 결합분포는 결합칼럼이 조회절에 사용될때 옵티마이저가 잘못된 실행계계획을 수립하게 하는 주요인입니다.
바인드 변수 사용시 균등분포 가정
아무리 정확한 칼럼히스토그램을 보유하더라도 바인드 변수를 사용한 SQL에는 적용할 수 없습니다. 조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용을 계산하기 때문입니다.
규칙에 의존하는 CBO
아무리 비용기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존합니다. 예를 들어, 최적화 목표를 최초 응답속도에 맞추면 order by 소트를 대체할 인덱스가 있을때 무조건 인덱스를 사용합니다.
통계정보를 이용한 비용계산 원리
실행계획을 수립할 때, CBO는 통계정보를 이용합니다. 최적의 실행계획을 위해 통계정보가 항상 데이터 상태를 정확하게 반영하고 있어야하는 이유입니다. 옵티마이저가 참조하는 통계정보는 아래 네 가지입니다.
- 테이블 통계 : 전체 레코드 수, 총 블록 수, 빈 블록 수, 한 행당 평균 크기 등
- 인덱스 통계 : 인덱스 높이, 리프 블록 수, 클러스터링 팩터, 인덱스 레코드 수 등
- 칼럼 통계 : 값의 수, 최저 값, 최고 값, 밀도, null 값 개수, 칼럼 히스토그램 등
- 시스템 통계 : CPU 속도, 평균 I/O 속도, 초당 I/O 처리량
선택도
선택도는 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율을 말합니다. 선택도를 통해 카디널리티를 구하고, 카디널리티를 기반으로 비용을 구해 인덱스 사용여부, 조인순서와 방법을 결정합니다. 선택도는 실행계획을 수립하는데 가장 중요한 요인입니다.
히스토그램이 있으면 히스토그램으로 선택도를 산정하고, 단일 칼럼에 대해 정확한 값을 구합니다. 히스토그램이 없거나, 있더라도 조건절에 바인드 변수를 사용하면 옵티마이저는 데이터 분포가 균일하다고 가정한 상태에서 선택도를 구합니다. 히스토그램 없이 = 조건으로 사용된 칼럼에 대한 선택도를 구하는 방법은 다음과 같습니다.
선택도 = 1/DISTINCT VALUE 개수
카디널리티
카디널리티는 특정 액세스 단계를 거치고난 후, 출력될 것으로 예상되는 결과건수를 말합니다. 총 로우수에 조건으로 사용된 칼럼의 선택도를 곱한 값입니다.
카디널리티 = 총로우 수 * 선택도 = num_rows / num_distinct
히스토그램
히스토그램은 분포가 균일하지 않은 칼럼을 조건으로 조회할때 효과를 발휘합니다. ORACLE의 경우, 도수분포, 높이균형, 상위도수분포, 하이브리드 4가지 유형의 히스토그램을 제공합니다.
도수분포 히스토그램
값의 빈도수를 저장합니다. 칼럼이 가진 값의 수가 적을때 사용되며, 칼럼 값의 수가 적기 때문에 값을 하나의 버킷에 할당하는 것이 가능합니다.
높이균형 히스토그램
칼럼이 가진 값의 종류가 많아 각각 하나의 버킷에 할당하기 어려울때 사용합니다. 하나의 버킷에 여러 개의 값을 할당합니다.
버킷의 높이가 같으므로 각 버킷이 갖는 빈도수는 총 레코드 개수 / 버킷 수로 구할 수 있습니다. 빈도 수가 많은 값에 대해서는 두 개 이상의 버킷이 할당됩니다.
바인드변수 사용 시, 카디널리티 계산
바인드 변수를 사용하면 최초 수행시 최적화를 거친 실헹계획을 캐시에 적재하고, 실행시점에 그것을 가져와 값만 다르게 바인딩하면서 반복 재사용합니다. 여기서 변수를 바인딩하는 시점이 실행시점 이후라는 사실이 중요합니다. SQL을 최적화 하는 시점에 조건절 칼럼의 값 분포를 활용하지 못합니다.
바인드 변수를 사용할때, 옵티마이저가 평균 분포를 가정한 실행계획을 생성하는 것도 이 때문입니다. 칼럼 분포가 균일할때는 상관없지만 그렇지 않을 때는 실행 시점에 바인딩되는 값에 따라 쿼리 성능이 달라질 수 있습니다. 이에 DW, OLAP, BATCH와 같이 대랑데이터를 처리할때나 값의 종류가 적고 분포가 균일하지 않을 때는 상수 조건을 쓰는 것이 효율적입니다.
비용
CBO는 비용을 기반으로 최적화를 수행하고 실행계획을 생성합니다. 비용이란 쿼리를 수행하는데 소요되는 일량 또는 시간의 예상치입니다. 옵티마이저 비용 모델에는 I/O 비용모델과 CPU 비용모델 두가지가 있습니다. I/O 비용 모델은 예상 입출력요청수를 쿼리 수행비용으로 간주해 실행계획을 평가하고 CPU 비용모델은 여기에 시간 개념을 더합니다.
인덱스를 경유한 테이블 엑세스 비용
I/O 비용 모델에서의 비요은 디스크 I/O CALL 횟수를 의미합니다. 인덱스를 경유한 테이블 엑세스 시에는 BLOCK I/O 방식이 사용됩니다. 이는 디스크에서 한 블록을 읽을때마다 한 번의 I/O CALL이 발생하므로 읽게 될 블록 수와 I/O CALL 횟수가 일치합니다.
비용 = BLEVEL -- 인덱스 수직적 탐색 비용
+ (리프 블록 수 * 유효 인덱스 선택도) -- 인덱스 수평적 탐색 비용
+ (클러스터`링 팩터 * 유효 테이블 선택도) -- 테이블 랜덤 엑세스 비용
FULL SCAN에 의한 테이블 엑세스 비용
FULL SCAN은 테이블 전체를 순차적으로 읽어 들이는 과정에서 발생하는 i/o Call 횟수를 비용으로 계산합니다. 한 번의 I/O로 여러 블록을 읽어들이는 MULTIBLOCK I/O방식을 이용하므로 총 블록을 MULTIBLOCK I/O 단위로 나눈 만큼의 I/O CALL이 발생합니다. 단위가 증가할 수록 I/O CALL이 줄고 예상비용도 줄어듭니다.