[J STORY] SQLP - 옵티마이저

1. 옵티마이저

옵티마이저란?

• SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리경로를 생성해 주는 DBMS 내부의 핵심엔진이다.

SQL 최적화 과정

• 사용자가 던진 쿼리 수행을 위해, 후보군이 될만한 실행계획을 찾는다.
• 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
• 각 실행계획을 비교해서 최저비용을 갖는 하나를 선택한다.

나. 옵티마이저 종류

1) 규칙기반 옵티마이저( = Heuristic 옵티마이저 )

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

규칙?

• 액세스 경로별 우선순위로서, 인덱스 구조, 연산자, 조건절 형태가 순위를 절정짓는 주요인다.

2) 비용기반 옵티마이저

• 테이블과 인덱스에 대한 여러 통계정보를 기초로 각 오퍼레이션 단계별 예상 비용을 산정하고, 이를 합산한 총비용이가장 낮은 실행계획을 선택한다.

비용?

• 쿼리를 수행하는데 소요되는 일량 또는 시간을 뜻함

오브젝트 통계 항목?

• 레코드 개수
• 블록 개수
• 평균 행 길이
• 칼럼 값의 수
• 칼럼 값의 분포
• 인덱스 높이
• 클러스터링 팩터
• 시스템 통계정보( CPU 속도, 디스크 I/O 속도 등 )

스스로 학습하는 옵티마이저( Self-Learning Optimizer )

• 예상치와 런타임 수행 결과를 비교하고, 예상치가 빗나갔을 때 실행계획을 조정하는 옵티마이저로 발전할 것이다.

다. SQL 최적화 과정

SQL 최적화 수행과정

• Parser : SQL문장을 이루는 개별 구성요소를 분석하고 파싱해서 파싱 트리를 만든다.( Syntax(문법),Semantic(의미))
• Query Transformer : 파싱된 SQL을 좀 더 일반적이고 표준적인 형태로 변환한다.
• Estimator : 오브젝트 및 시스템 통계정보를 이용해 쿼리 수행 각 단계의 선택도, 카디널리티, 비용을 계산하고, 궁극적으로는 실행계획 전체에 대한 총비용을 계산해 낸다.
• Plan Generator : 하나의 쿼리를 수행하는데 있어, 후보군이 될만한 실행계획들을 생성해 낸다.
• Row-Source Generator : 옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드(또는 프로시저 ) 형태로 포맷팅한다.
• SQL Engine : SQL을 실행한다.

라. 최적화 목표

1) 전체 처리속도 최적화

ALTER SYSTEM SET OPTIMIZER_MODE = ALL_ROWS;	--시스템 레벨
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;	--세션 레벨
SELECT /*+ ALL_ROWS */ * FROM T WHERE ... ;	--SQL 레벨

2) 최초 응답속도 최적화

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

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

-- MS-SQL
SELECT * FROM T WHERE OPTION( fast 10 );

 

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

옵티마이저 통계유형

• 테이블 통계 : 전체 레코드 수, 총 블록 수, 빈 블록 수, 한 행당 평균 크기 등
• 인덱스 통계 : 인덱스 높이, 리프 블록 수, 클러스터링 팩터, 인덱스 레코드 수 등
• 칼럼 통계 : 값의 수, 최저 값, 최고 값, 밀도, null값 개수, 칼럼 히스토그램 등
• 시스템 통계 : CPU 속도, 평균적인 I/O 속도, 초당 I/O 처리량 등

가. 선택도

• 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율

실행계획 수립 절차 : 선택도 -> 카디널리티 -> 비용 -> 액세스 방식, 조인 순서, 조인 방법 등 결정

• 히스토그램이 있으면 그것으로 선택도를 산정하며, 단일 컬럼에 대해서는 비교적 정확한 값을 구한다. 히스토그램이 없거나 있더라도 조건절에 바인드 변수를 사용하면 옵티마이저는 데이터 분포가 균일하다고 가정한 상태에서 선택도를 구한다.

나. 카디널리티

• 특정 액세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수

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

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

다. 히스토그램

• 분포가 균일하지 않은 컬럼으로 조회할 때 효과를 발휘한다.

도수분포 히스토그램 ( Frequency number ) : 1 : 1 ( 버킷 : 값 )

도수분포 히스토그램

높이균형 히스토그램 ( Height-Balance Histogram ): 1 : M ( 버킷 : 값 ) AND M : M( 버킷 : 값 )

• 데이터 분포도 : 1 / (버킷 개수) * 100
• 빈도수 : ( 총 레코드 개수 ) / ( 버킷 개수 )

빈도 수가 많은 값( popular value )

 

라. 비용 : 예상 일량 또는 시간

• IO 비용 모델 : I/O 요청 횟수만을 쿼리 수행 비용으로 간주
• CPU 비용 모델 : I/O 요청 횟수만을 쿼리 수행 비용 + 시간 개념

인덱스를 경유한 테이블 액세스 비용 : Single Block I/O COUNT

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

비용 = blevel					-- 인덱스 수직적 탐색 비용
      + (리프 블록 수 * 유효 인덱스 선택도)	-- 인덱스 수평적 탐색 비용
      + (클러스터링 팩터 * 유효 테이블 선택도)	-- 테이블 Random 액세스 비용

Full Scan에 의한 테이블 액세스 비용 : Multiblock I/O COUNT

5. 옵티마이저 힌트

Oracle 힌트1) 힌트 기술 방법

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;

2) 힌트가 무시되는 경우

• 문법적으로 안 맞게 힌트를 기술
• 의미적으로 안 맞게 힌트를 기술 : 서브쿼리에 UNNEST와 PUSH_SUBQ를 같이 기술한 경우( unnest되지 않은 서브쿼리만이 push_subq힌트의 적용 대상임)
• 잘못된 참조 사용 : 잘못된 별칭
• 논리적으로 불가능한 액세스 경로

-- EMP_ENAME_IDX NULL 허용 컬럼 인덱스
SELECT /*+ INDEX( E EMP_ENAME_IDX ) */ COUNT(*) FROM EMP E

버그

• Oracle : 사용 인덱스 변경시 에러 미발생 ( 장점 : 안정적, 단점 : 성능 )
• SQL Server : 사용 인덱스 변경시 에러 발생 ( 장점 : 성능, 단점 : ? )

3) 힌트 종류

힌트종류

나. SQL Server 힌트

• 테이블 힌트 : 테이블명 다음에 WITH절을 통해 지정한다. fastfirstrow, holdlock, nolock 등
• 조인 힌트 : FROM절에 지정하며, 두 테이블 간 조인 전략에 영향을 미친다. loop, hash, merge, remote 등
• 쿼리 힌트 : 쿼리당 맨 마지막에 한번만 지정할 수 있는 쿼리 힌트는 OPTION절을 이용한다.