SQLP - 옵티마이저

@markdown

# 옵티마이저

<br/>

## 옵티마이저란

____

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

- 옵티마이저가 생성한 SQL 처리경로가 `실행계획`

#### SQL 최적화 과정 요약

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

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

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

<br/>

## 옵티마이저 종류

____

### 규칙기반 옵티마이저(Rule-Based Optimizer, Heuristic Optimizer)

- 미리 정해 놓은 규칙에 따라 액세스 경로를 평가하고 실행계획을 선택

- 규칙 : 엑세스 경로별 우선순위 결정(인덱스 구조, 연산자, 조건절 형태)

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

- 비용을 기반으로 최적화를 수행

- 판단 기준 : 미리 구해놓은 테이블과 인덱스에 대한 여러 통계정보로 단계별 예상 비용을 산정하고, 이를 합산한 총비용이 가장 낮은 실행계획을 선택한다.

### 오브젝트 통계 항목

- 레코드 개수

- 블록 개수

- 평균 행 길이

- 칼럼 값의 수

- 칼럼 값의 분포

- 인덱스 높이

- 클러스터링 팩터

- 시스템 통계정보( CPU 속도, 디스크 I/O 속도 등 )

<br/>

## SQL 최적화 과정

____


- `Parser` : SQL문장을 이루는 개별 구성요소를 분석하고 파싱해서 파싱 트리를 만든다.( Syntax(문법), Semantic(의미) 오류 확인)

- `Query Transformer` : 파싱된 SQL을 좀 더 일반적이고 표준적인 형태로 변환한다.

- `Estimator` : 오브젝트 및 시스템 통계정보를 이용해 쿼리 수행 각 단계의 선택도, 카디널리티, 비용을 계산하고, 궁극적으로는 실행계획 전체에 대한 총비용을 계산한다.

- `Plan Generator` : 하나의 쿼리를 수행하는데 있어, 후보군이 될만한 실행계획들을 생성해 낸다.

- `Row-Source Generator` : 옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드(또는 프로시저 ) 형태로 포맷팅한다.

- `SQL Engine` : SQL을 실행한다.

<br/>

## 최적화 목표

____

### 1) 전체 처리속도 최적화

<pre><code class="sql" style="font-size:15px">--시스템 레벨

ALTER SYSTEM SET OPTIMIZER_MODE = ALL_ROWS;

--세션 레벨

ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;

--SQL 레벨

SELECT /*+ ALL_ROWS */ * FROM T WHERE ... ;

</code></pre>

<br/>

### 2) 최초 응답속도 최적화

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

- Oracle 옵티마이저에게 최초 응답속도 최적화를 요구하려면, 옵티마이저 모드를 FIRST_ROWS로 바꿔주면 됨

- 예) OPTIMIZER_MODE = FIRST_ROWS_10 : 사용자가 전체 결과 집합 중 처음 10개 로우만 읽고 멈추는 것을 전제로 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.

<pre><code class="sql" style="font-size:15px">ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;

SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;

세션이 변경되었습니다.

SQL> SHOW PARAMETER OPTIMIZER_MODE;

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- ------------------------------

optimizer_mode                       string                 FIRST_ROWS_10

</code></pre>

<br/>

- 쿼리 레벨에서 힌트 사용 방법

<pre><code class="sql" style="font-size:15px">-- Oracle

SELECT /*+ FIRST_ROWS(10) */ * FROM T WHERE ... ;

-- MS-SQL

SELECT * FROM T WHERE OPTION( fast 10 );

</code></pre>

<br/>

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

____

## SQL과 연산자 형태

- SQL 작성 방식, 연산자 방식에 따라 쿼리 성능에 영향을 미침

## 옵티마이징 팩터

- 인덱스, IOT, 클러스터링, 파티셔닝, MV 등을 어떻게 구성하는지에 따라 실행계획과 성능이 달라짐

## DBMS 제약 설정

- 개체 무결성, 참조 무결성, 도메인 무결성 등을 위해 DBMS가 제공하는 PK, FK, Check, Not Null 같은 제약 설정 기능을 이용하면 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공

## 옵티마이저 힌트

- 사용자가 지정한 옵티마이저 힌트가 우선

## 통계정보

- 통계정보가 옵티마이저에게 미치는 영향력은 절대적

- CBO 판단기준은 모두 통계정보 기반

## 옵티마이저 관련 파라미터

- DBMS 업그레이드로 옵티마이저 관련 파라미터가 추가 또는 변경되면서 다르게 작동할 수 있다.

## DBMS 버전과 종류

- DBMS 버전에 따라 옵티마이저 실행계획이 다를 수 있음

- 같은 SQL이더라도 DBMS 종류에 따라 내부적으로 처리 방식이 다를 수 있음

<br/>

# 옵티마이저의 한계

____

## 옵티마이징 팩터의 부족

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

- 사용자가 적절한 옵티마이징 팩터(인덱스, IOT, 클러스터링, 파티셔닝 등)을 제공하지 않는 다면 좋은 실행계획 수립할 수 없음

## 통계정보의 부정확성

- 100% 정확한 통계정보를 유지하긴 힘듦

- 컬럼을 결합했을 때의 모든 결합의 분포를 미리 구해하는데 한계

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

- 바인드 변수를 사용하는 SQL에는 히스토그램 정보 무용지물

## 비현실적인 가정

## 규칙에 의존하는 CBO

- 비용기반 옵티마이저라도 부분적으로 규칙에 의존함

## 하드웨어 성능 특성

- 개발 HW 사양과 실제 운영 시스템 HW 사양과 다를 경우 옵티마이저가 잘못된 실행계획을 수립

<br/>

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

____

- 데이터 딕셔너리에 미리 수집해둔 통계정보가 옵티마이저에 의해 구체적으로 어떻게 활용되는지 살펴본다.

## 옵티마이저 통계 유형

____

- 테이블 통계 : 전체 레코드 수, 총 블록 수, 빈 블록 수, 한 행당 평균 크기 등

- 인덱스 통계 : 인덱스 높이, 리프 블록 수, 클러스터링 팩터, 인덱스 래코드 수 등

- 컬럼 통계 : 값의 수, 최저 값, 최고 값, 밀도, null값 개수, 칼럼 히스토그램 등

- 시스템 통계 : CPU 속도, 평균적인 I/O 속도, 초당 I/O 처리량 등

<br/>

## 선택도

____

- 선택도 : 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상 되는 레코드 비용

- <b>선택도 : 1 / Distinct Value 갯수 = 1 / num distinct</b>

- <b>선택도 → 카디널리티 → 비용 →  액세스 방식, 조인 순서, 조인 방법 등 결정</b>

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

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

<br/>

## 카디널리티

____

- 카디널리티 : 특정 액세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수를 말함

- <b>카디널리티 = 총 로우 수 × 선택도</b>

- 컬럼 히스토 그램이 없을때 `=`조건에 대한 선택도가 1/num distinct이므로 카디널리티는 아래와 같이 구해짐

<pre><code class="sql" style="font-size:15px">SELECT * FROM 사원 WHERE 부서 = :부서

DISTINCT VALUE = 10

NUM_ROWS = 1000

선택도 = 1 / 10 = 0.1

카디널리티 = 1000 * 0.1 = 100

--조건 컬럼이 두 개 이상인 경우

SELECT * FROM 사원 WHERE 부서 = :부서 AND 직급 = :직급

--부서

DISTINCT VALUE = 10

선택도 = 1 / 10 = 0.1

--직급

DISTINCT VALUE = 4

선택도 = 1 / 4 = 0.25

--카디널리티 = 1000 * 0.1 * 0.25 = 25

</code></pre>

<br/>

## 히스토그램

____

- 분포가 균일하지 않은 컬럼으로 조회할 때 효과를 발휘함

### 도수분포 히스토그램

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


### 높이균형 히스토그램

- 컬럼이 가진 값의 수가 많아 하나의 버킷에 할당하기 어려울 때 사용

- 데이터 분포도 : 1 / (버킷 개수) * 100

- 빈도수 : (총 레코드 개수) / (버킷 개수)


- x축은 연령대를 의미, age=40 레코드 비중이 50%이기 때문에 총 20개 중 10개의 버킷을 차지한 것을 확인

<br/>

## 비용

____

- 비용 : 쿼리를 수행하는데 소요되는 일량 또는 시간, 예상치

- IO 비용 모델 : I/O 요청 횟수만을 쿼리 수행 비용으로 간주

- CPU 비용 모델 : I/O 요청 횟수만을 쿼리 수행 비용 + 시간 개념

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

- I/O 비용 모델에서의 비용은 디스크 I/O Call 횟수를 의미

- 인덱스 경유 테이블 액세스 시 Single Block I/O 방식이 사용됨

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

- blevel : 브랜치 레빌을 의미하며, 리프 블록에 도달하기 전에 읽게 될 브랜치 블록 개수임

- 클러스터링 팩터 : 특정 칼럼을 기준으로 같은 값을 갖는 데이타가 서로 모여있는 정도

인덱스를 경유해 테이블 전체 로우를 액세스 할 때 읽을 것으로 예상되는 논리적인 블록 개수로 계수화 함

- 유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율

- 유효 테이블 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고, 최종적으로 테이블을 방문할 것으로 예상되는 비율

- 클러스터링 팩터는 인덱스를 경유해 전체 로우를 엑세스할 때 읽힐것으로 예상되는 테이블 블록 개수이므로 여기에 유효 테이블 선택도를 곱함으로써 조건절에 대해 읽힐 것으로 예상되는 테이블 블록 개수를 구할 수 있음

<pre><code class="sql" style="font-size:15px">비용 = blevel -- 인덱스 수직적 탐색 비용

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

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

</code></pre>

<br/>

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

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

- Full Scan은 Multiblock I/O 방식을 사용

- 총 블록 수 / Multiblock I/O 

- 예) 100 블록을 8개씩 나누어 읽는다면 13번의 I/O Call 발생

- Multiblock I/O 단위가 증가할수록 I/O Call 횟수가 줄고 예상비용도 줄게 됨

<br/>

# 옵티마이저 힌트

- 개발자가 직접 인덱스를 지정하거나 조인 방식을 변경함으로써 더 좋은 실행계획을 유도하는 방법

## Oracle 힌트

____

### 1) 힌트 기술방법

<pre><code class="sql" style="font-size:15px">SELECT /*+ LEADING( E2 E1 ) USER_NL( E1 ) INDEX( E1 EMP_EMP_ID_PK )

           USE_MERGE(J) FULL(J) */

       E1.FIRST_NAME, E1. LAST_NAME, J.JOB_ID, SUM( E2.SALARY ) TOTAL_SAL

 FROM EMPLOYEES E1, EMPLOYEES E2, JOB_HISTORY J

WHERE E1.EMPLOYEE_ID - E2.MANAGER_ID

  AND E1.EMPLOYEE_ID = J.EMPLOYEE_ID

  AND E1.HIRE_DATE = J.START_DATE

GROUP BY E.1FIRST_NAME, E1.LAST_NAME, J.JOB_ID

ORDER BY TOTAL_SAL;

</code></pre>

<br/>

### 2) 힌트가 무시되는 경우

- 문법적으로 맞지 않는 힌트를 기술한 경우

- 의미직으로 맞지 않는 힌트를 기술한 경우

- 잘못된 참조를 사용한 경우(존재하지 않는 테이블이나 인덱스, 잘못된 Alias 사용)

- 논리적으로 불가능한 액세스 경로

- 조인절에 조건이 하나도 없는데 Hash Join으로 유도하거나 인덱스를 이용해 전체 건수를 세려고 시도

<br/>

### 3) Oracle 힌트 종류

#### 최적화 목표

- all_rows

- first_rows

#### 액세스 경로

- full

- cluster

- hash

- index, no_index

- index_asc, index_desc

- index_combine

- index_join

- index_ffs, no_index_ffs

- index_ss, no_index_ss

- index_ss_asc, index_ss_desc

#### 쿼리 변환

- no_query_transformation

- use_concat

- no_expand

- rewrite, no_rewrite

- merge, no_merge

- start_transformation, no_star_transformation

- fact, no_fact

- unnest, no_unnest

#### 조인 순서

- ordered 

- leading

#### 조인 방식

- use_nl,no_use_nl

- use_nl_with_index

- use_merge, no_use_merge 

- use_hash, no_use_hash

#### 병렬 처리

- parallel, no_parallel

- pq_distribute

- parallel_index, no_parallel_index

#### 기타 

- append, noappend

- cache, nocache

- push_pred, no_push_pred

- push_subq, no_push_subq

- qb_name

- cursor_sharing_exact

- driving_site

- dynamic_sampling

- model_min_analysis

### 4) SQL Server 힌트 종류

- 테이블 힌트 : 테이블명 다음에 WITH절을 통해 지정한다.(fastfirstrow, holdlock, nolock 등)

- 조인 힌트 : FROM절에 지정하며, 두 테이블 간 조인 전략에 영향을 미친다.(loop, hash, merge, remote 등)

- 쿼리 힌트 : 쿼리당 맨 마지막에 한번만 지정할 수 있는 쿼리 힌트는 OPTION절을 이용한다.

> SQL 전문가 가이드 : 과목3. SQL 고급 활용 및 튜닝, 3장 옵티마이저 원리 - 제1절 옵티마이저(p.558 ~ p.570)

> 사진 출처 : 구루비넷