친절한 SQL 튜닝_7장.SQL 옵티마이저_2

 

SQL 옵티마이저

7.2 옵티마이저에 대한 이해

 

7.2.1 옵티마이저 종류

 

비용기반(Cost-Based) 옵티마이저(이하 ‘CBO’)는 사용자 쿼리를 위해 후보군이 될만한 실행계획을 도출하고, 데이터 딕셔너리(Data Dictionary)에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저이다. CBO가 사용하는 통계정보로는 데이터량, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등이 있다.

 

과거에는 각 액세스 경로에 대한 우선순위 규칙에 따라 실행계획을 만드는 옵티마이저를 사용했어다. 일명, ‘규칙기반(Rule-Based) 옵티마이저’, 줄여서 RBO!, RBO는 데이터 특성을 나타내는 통계정보를 전혀 활용하지 않고 단순한 규칙에만 의존하기 때문에 대량 데이터를 처리하는 데 부적합하다.

 

 

 

7.2.2 옵티마이저 모드

 

최적화 목표를 설정하는 기능으로서 아래 세가지 옵티마이저 모드 중 하나를 선택하면 된다.

 

- ALL_ROWS : 전체 처리속도 최적화

 

- FIRST_ROWS : 최초 응답속도 최적화

 

- FIRST_ROWS_N : 최초 N건 응답속도 최적화

 

옵티마이저 모드를 ALL_ROWS로 설정하면 옵티마이저는 쿼리 결과집합 ‘전체를 읽는 것을 전제로’ 시스템 리소스(I/O, CPU, 메모리 등)를 가장 적게 사용하는 실행계획을 선택한다. 즉, 전체 처리속도 최적화가 목표다.

 

FIRST_ROWS로 설정하면 옵티마이저는 전체 결과집합 중 ‘앞쪽 일부만 읽다가 멈추는 것을 전제로’ 응답 속도가 가장 빠른 실행계획을 선택한다. 즉, 최초 응답속도 최적화가 목표다. ALL_ROWS와 비교하면, Table Full Scan 보다 인덱스를 더 많이 선택하고, 해시 조인, 소트 머지 조인보다 NL 조인을 더 많이 선택하는 경향을 보인다.

 

ALL_ROWS와 쉽게 비교 설명하려고 FIRST_ROWS를 소개했는데, 사실 이 옵티마이저 모드는 이제 사용하면 안 된다. 앞으로 사라지게 될(deprecated) 옵티마이저 모드이기 때문이다. 그 대신 FIRST_ROWS_N을 사용해야 한다.

 

FIRST_ROWS_N으로 설정하면 옵티마이저는 사용자가 ‘앞쪽 N개 로우만 읽고 멈추는 것을 전제로’ 응답 속도가 가장 빠른 실행계획을 선택한다. ALTER SYSTEM 또는 ALTER SESSION 명령어로 옵티마이저 모드를 설정할 때 N으로 지정할 수 있는 값은 아래와 같이 1, 10, 100, 1000 네 가지다.

 

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_100;
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1000;

 

아래와 같이 FIRST_ROWS(n) 힌트로 설정할 때는 괄호 안에 ()보다 큰 어떤 정수 값이라도 입력할 수 있다.

 

SELECT /*+ FIRST_ROWS(30) */ COL1, COL2, COL3 FROM T WHERE ...

 

FIRST_ROWS는 사용자가 데이터를 어느 정도 읽다가 멈출지를 지정하지 않았으므로 정확한 비용 산정이 어렵다. 반면, FIRST_ROWS_N은 읽을 데이터 건수를 지정하였으므로 더 정확한 비용 산정이 가능하다. FIRST_ROWS_N이 FIRST_ROWS보다 더 완벽한 CBO 모드로 작동하는 이유다.

 

 

 

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

 

(1) SQL과 연산자 형태

 

(2) 인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이징 팩터

 

(3) 제약 설정

 

(4) 통계정보

 

(5) 옵티마이저 힌트

 

(6) 옵티마이저 관련 파라미터

 

 

 

7.2.4 옵티마이저의 한계

 

7.2.5 개발자의 역할

 

7.2.6 튜닝 전문가 되는 공부방법

출처 : 친절한 SQL 튜닝 - 조시형 지음