옵티마이저

1. 옵티마이저 종류

1. 규칙기반 옵티마이저 Rule-Based Optimizer(RBO)

미리 정해 놓은 규칙에 따라 액세스 경로를 평가하고 실행계획을 선택한다. 여기서 규칙이란 액세스 경로별 우선순위로서, 인덱스 구조, 연산자, 조건절 형태가 순위를 결정짓는 주요인이다.

 

2. 비용기반 옵티마이저 Cost-Based Optimizer(CBO)

비용이란, 쿼리를 수행하는 데 소요되는 일량 또는 기산을 뜻한다.

CBO 가 실행계획을 수립할 때 판단 기준이 되는 비용은 어디까지나 예상치다. 미리 구해놓은 테이블과 인덱스에 대한 여러 통계정보를 기초로 각 오퍼레이션 단계별 예상 비용을 산정하고, 이를 합산한 총비용이 가장 낮은 실행계획을 선택한다. 비용을 산정할 때 사용하는 오브젝트 통계 항목으로는 레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 같은 것들이 있다. 오브젝트 통계뿐만 아니라 최근에는 하드웨어적 특성을 반영한 시스템 통계정보(CPU속도, 디스크 I/O 속도 등)까지 이용한다.

역사가 오래된 오라클은 RBO 에서 출발했으나 , 다른 상용 RDBMS 는 CBO 를 선택했다. 

 

2 .최적화 목표

1) 전체 처리속도 최적화

쿼리 최종 결과 집합을 끝까지 읽는 것을 전제로, 시스템 리소스(I/O, CPU, 메모리 등)을 가장 적게 사용하는 실행계획을 선택한다. Oracle, SQL 서버 등을 포함해 대부분 DBMS 의 기본 옵티마이저 모드는 전체 처리 속도 최적화에 맞춰져 있다.

오라클 옵티마이저 모드 변경 
alter system set optimizer_mode = all_rows;
alter session set optimizer_mode = all_rows;
select /*+all_rows*/ * from t where..;

2) 최조 응답속도 최적화 

전체 결과 집합 중 일부만 읽다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.

만약 이 모드에서 생성한 실행계획으로 데이터를 끝까지 읽는다면, 전체 처리속도 최적화 실행계획보다 더 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다.

오라클 옵티마이저에게 최초 응답속도 최적화를 요구하려면, 옵티마이저 모드를 first_row 로 바꿔주면된다.

오라클에서 옵티마이저 모드를 first_rows_n 으로 지정하면, 예를 들어 시스템 또는 세션 레벨에서 first_rows_10 으로 지정하면, 사용자가 전체 결과 집합 중 처음 10개 로우만 읽고 멈추는 것을 전체로 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.

select /*+first_rows(10)*/ * from t where..;

---

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

 

가. SQL 과 연산자 형태 

결과가 같더라도 sql 을 어떤 형태로 작성했는지 또는 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있고, 이는 쿼리 성능에 영향을 미친다.

 

나. 옵티마이징 팩터 

쿼리를 똑같이 작성하더라도 인덱스, IOT, 클러스터링, 파티셔닝, MV 등을 어떻게 구성하냐에 따라 실행계획과 성능이 크게 달라진다.

 

다. DBMS 제약 설정

개체 무결성, 참조 무결성, 도메인 무결성 등을 위해 DBMS 가 제공하는 PK,FK, Check,Not NUll 같은 제약 설정 기능을 이용할 수 있고, 이들 제약 설정은 옵티마이저가 쿼리 성느을 최적화하는데 매우 중요한 정보를 제공한다.

예를 들어 인덱스 컬럼에 Not Null 제약이 들어있으면, 옵티마이저는 전체 개수를 구하는 count 쿼리에 이 인덱스를 사용할 수 있다.

 

라. 옵티마이저 힌트 

옵티마이저 판단보다 사용자가 지정한 힌트를 우선한다.

 

마. 통계정보

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

 

바. 옵티마이저 관련 파라미터 

SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 동일해도 DBMS 버전에 따라 옵티마이저가 다르게 작동할 수 있다.

옵티마이저 관련 파라미터가 추가, 변경 되면서 나타나는 현상이다.

 

사. DBMS 버전과 종류 

같은 SQL 도 내부적 처리방식이 다를 수 있다.

---

옵티마이저 한계

가. 옵티마이징 팩터의 부족 

옵티마이저는 주어진 환경에서 가장 최적의 실행계획을 수립하기 위해 정해진 기능을 수행할 뿐이다. 

사용자가 적절한 옵티마이징 팩터(효과적으로 구성된 인덱스, IOT, 클러스터링, 파티셔닝 등)를 제공하지 않는다면 

결코 좋은 실행계획은 수립할 수 없다.

 

나. 통계정보의 부정확성

특히 컬럼 분포가 고르지 않은 때 컬럼 히스토그램이 반그시 필요한데, 이를 수집하고 유지하는 비용이 만만치 않다.

컬럼을 결합했을 때의 모든 결합 분포를 미리 구해두기 어려운 것도 큰 제약 중 하나다. 이는 상관관계에 있는 두 컬럼이 조건절에 사용될 때 옵티마이저가 잘못된 실행계획을 수립하게 만드는 주요이다.

select * from 사원 where 직급='부장' and 연봉>=5000;

직급이 {부장,과장,대리,사원}의 집합이고 각각 25% 의 비중을 갖는다. 그리고 전체 사원이 1000명이고 히스토그램상 연봉>=5000 조건에 부합하는 사원비중이 10% 이면, 옵티마이저는 위 쿼리 조건에 해당하는 사원 수를 25(1000*0.25*0.1)

명으로 추정한다. 하지만 직급과 연봉의 상관관계가 높아서, 만약 5000만원 이상 사원이 모두 부장이라면 실제 위 쿼리 결과는 100(1000*0.1)건이다.

이런 조건절에 대비해 모든 컬럼 간 상관관계와 결합분포를 미리 저장해두는 것은 불가능에 가깝다. 테이블 컬럼이 많을수록 잠재적인 컬럼 조합의 수는 기하급수적으로 증가하기 때문이다.

 

다. 바인드 변수 사용 시 균등분포 가정

아무리 정확한 컬럼 히스토그램을 보유하더라도 바인드 변수를 사용한 SQL 에는 무용지물이다.

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

*바인드 변수 : select * from tab where id = ?  일때 '?' 를 의미한다. select * from tab where id = :id_val;  로 치환된다. 

 

라.비현실적인 가정

예전 오라클 버전에서 SingleBlock I/O 와 Multiblock I/O 의 비용을 같에 평가하고 데이터 블록의 캐싱효과도 고려하지 않았다. DBMS 버전이 올라가면서 이런 잘못된 가정들이 보완되지만 완벽하진 않다.

 

마. 규칙에 의존하는 CBO

바. 하드웨어 성능 특성

---

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

 

옵티마이저 통계 유형 

통계유형	세부 통계 항목
테이블 통계	전체 레코드 수, 촐 블록 수, 빈 블록 수, 한 행당 평균 크기 등
인덱스 통계	인덱스 높이, 리프 블록수, 클러스터링 팩터, 인덱스 레코드 수 등
컬럼 통계	값의 수, 최저 값, 최고 값, 밀도, null 개수, 컬럼 히스토리 등
시스템 통계	CPU 속소, 평균 I/O 속도, 초당 I/O 처리량 등

가. 선택도 

선택도란 전체 대상 레코드 중 특정 조건에 의해 선택될 것으로 예상되는 레코드의 비율을 말한다.

선택도도 카디널리티를 구하고, 다시 비용을 구해 액세스 방식, 조인순서, 조인방법 등을 결정하므로 

선택도는 최적의 실행계획을 수립하는데 가장 중요한 요일이다.

 

히스토그램이 있으면 그것으로 선택도를 산정하며, 단일 컬럼에 대해서는 비교적 정확한 값을 구한다.

히스토그램이 없거나 있어도 조건절에 바인드 변수를 사용하면 옵티마이저는 데이터 분포가 균일하다고 가정하고 선택도를 구한다.

 

히스토그램 없이 등치조건에 의해 선택도를 구하는 공식

1/distinct value 개수 = 1/num_distinct

 

나. 카디널리티 

특정 액세스 단계를 거치고 나고 출력될 것으로 예상된느 결과 건수를 말한다.

카디널리티 = 총 로우수 x 선택도 

 

컬럼 히스토리가 없을 때 등치조건에 대한 선택도가 1/num_distinct 이므로 다음과 같이 구해진다.

카디널리티 = 총 로우수 x 선택도  = num_rows/num_distinct 

select * from 사원 where 부서 =:부서

위 쿼리에서 부서 컬림의 distinct value 개수가 10이면 선택도는 1/10 = 0.1 이고 , 

총 사원수가 1000명일 때 카디널리티는 1000*0.1 = 100 이 된다.

옵티마이저는 위 조건절에 의한 결과 집합이 100건일 것으로 예상한다는 뜻이다.

조건절이 두 개 이상일 때는 각 컬럼의 선택도와 전체 로우수를 곱해주기만 하면 된다.

select * from 사원 where 부서 =:부서 and 직급 =:직급

직급 4개 집합일 때 선택도는 0.25 = 1/4 , 카디널리티는 100*0.1*0.25=25 이다.

 

다. 히스토그램 

Histogram이란 Table형태의 빈도(개수)를 Graphical하게 표현한 것이다.

오라클 12c 는 도수분포(Frequency) , 높이균형(Height-balanced), 상위도수분포(Top-Frequency), 하이브리드(Hybrid) 4가지 유형의 히스토그램을 제공한다. 그 중 전통적으로 사용해온 도수분포와 높이 균형 히스토그램만 살펴본다.

 

1. 도수분포 히스토그램

값별로 빈도수(Frequency Number)를 저장하는 히스토그램을 말한다.

컬럼이 가진 값의 수가 적을 때 사용되며, 컬럼 값의 수가 적기 때문에 각각 하나의 버킷을 할당(값의 수=버킷개수)하는 것이 가능하다.

2. 높이 균형 히스토그램 

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

히스토그램 버킷을 값의 수보다 적게 할당하기 때문에 하나의 버킷이 여러 개 값을 담당한다.

예를 들어 값의 수가 1000개인데 히스토그램을 위해 할당된 버킷 개수가 100개이면 하나의 버킷에 10개의 값을 대표한다. 높이 균형 히스토그램은 각 버킷의 높이가 같다 .

각 버킷은 {1/버킷개수 * 100}%의 데이터 분포를 갖는다. 따라서 각 버킷이 갖는 빈도수는 {총 레코드 개수/버킷 개수}다.

빈도 수가 많은 값 popular value 에 대해서는 두 개 이상의 버킷이 할당된다.

아래에서 x축은 연령대를 의미하는데 age=40 인 레코드 비중이 50%라서 총 20개 버킷 중 10개 를 차지한 것을 볼 수 있다.

라.비용 

옵티마이저 비용 모델에는 I/O 비용 모델과 CPU 비용모델이 있다.

I/O 비용 모델은 예상되는 I/O 요청 횟수만을 쿼리 수행 비용으로 간주해 실행 계획을 평가하는 반면,

CPU 비용 모델은 여기에 시간 개념을 더해 비용을 산정한다.

 

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

I/O 비용 모델에서의 비용은 디스크 I/O Call 횟수(논리적/물리적으로 읽은 블록 개수가 아닌 I/O call 횟수)를 의미한다. 

그리고 인덱스를 경유한 테이블 액세스 시에는 Single Block I/O 방식이 사용된다. 이는 디스크에서 한 블록을 읽을 때마다 한 번의 I/O call 을 일으키는 방식이므로 읽게 될 물리적 블록 개수가 I/O call 횟수와 일치한다.

따라서 인덱스를 이용한 테이블 액세스 비용은 다음과 같은 공식으로 구할 수 있다.

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

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

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

항목	설명
blevel	브랜치 레벨을 의미하며, 리프 블록에 도달하기 전에 읽게 될 브랜치 블록 개수
클러스터링 팩터	특정 컬럼을 기준으로 같은 값을 갖는  데이터가 서로 모여있는 정도. 인덱스를 경유해 테이블 전체 로우를 액세스할 때 읽을 것으로 예상되는 논리적 블록 개수
유효 인덱스 선택도	전체 인덱스 레코드 중 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율 리프블록에는 인덱스 레코드가 정렬된 상태로 저장되므로 이 비율이 곧 방문할 리프 블록 비율
유효 테이블 선택도	전체 레코드 중 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율 클러스터링 팩터는 인덱스를 경유해 전체 로우를 액세스할 때 읽힐 것으로 예상되는 테이블 블록 개수이므로 여기에 유효 테이블 선택도를 곱함으로써 조건절에 대해 읽힐 것으로 예상되는 테이블 블록 개수를 구할 수 있음

 

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

테이블 전체를 순차적으로 읽어 들이는 과정에서 발생하는 I/O call 횟수로 비용을 계산한다.

Full Scan 할 때는 한번의 I/O 로써 여러 블록을 읽어 들이는 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 횟수가 줄고 예상비용도 줄어든다.

---

출처

SQL 전문가 가이드 - 한국데이터산업진흥원