SQLP 요점정리 #13 : 3과목 Tuning - 3장 Optimizer 원리

3과목 SQL 고급 활용 및 Tuning

3장 Optimizer 원리

3.1 Optimizer

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

Optimizer 종류

• RBO (Rule-based Optimizer) : 규칙(우선 순위)를 가지고 실행계획 생성
• CBO (Cost-based Optimizer) : 통계정보를 기반으로 여러가지 실행계획을 생성하여 그중 최저비용의 실행계획을 선택

최적화 과정

1. Parser : SQL Parsing. SQL의 문법(Syntax) , 의미 (Semantic)을 확인
2. Optimizer
   • Query Transformer : Parsing된 SQL을 표준 형태로 변환
   • Estimator : 통계정보를 이용하여 선택도, 카디널리티 등으로 Execution Plan의 총 비용을 계산
   • Plan Generator : 후보군이 될만한 Execution Plan을 생성
3. Row-Source Generator : 최종 선택된 Execution Plan을 SQL 엔진이 실행할 수 있는 코드 생성
4. SQL Engine : SQL을 실행

• 최적화 목표
  1. 전체 처리속도 최적화 (all_rows) : 결과집합을 끝까지 읽는 것을 전제. 대부분 DBMS의 기본옵션
  2. 최초 응답속도 최적화 (first_rows) : 결과중 일부만 읽다가 멈추는 것을 전제.

SELECT /*+ ALL_ROWS */ ... ; -- 전체 처리속도 최적화
SELECT /*+ FIRST_ROWS(10) */ ... ; -- 처음 10개의 row만 읽고 멈추는 것을 전제로 최적화

Optimizer 통계유형

• Table : 전체 레코드 수, 총 블록 수, 빈 블록 수, 한 행당 평균 크기 등
• Index : 높이, 리프 블록 수, 클러스터링 팩터, 인덱스 레코드 수 등
• Column : 값의 수, MIN, MAX, 밀도, NULL값 개수, 히스토그램 등
• System : CPU 속도, 평균 I/O 속도, 초당 I/O 처리량 등

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

• 선택도 (Selectivity) : 1 / Distinct Value 수
• 카디널리티 (Cardinality) : 총 Row 수 X 선택도
• 히스토그램 (Histogram) : Column의 분포도
  • 도수분포 히스토그램 : 값별로 빈도수(Frequency Number)를 저장
  • 높이균형 히스토그램 : 각 버킷의 높이를 같게 하고 빈도 수가 많은 값(popular value)는 여러 버킷에 할당. 컬럼이 가진 값의 수가 아주 많을 경우 효과적
• 비용 (Cost)
  • I/O 비용 모델 : 예상되는 I/O 요청(Call) 횟수로 평가
  • CPU 비용 모델 : I/O비용 모델 + 시간 개념을 더해 비용 산정

Optimizer Hint

• Optimizer도 잘못된 판단을 할 수 있으므로, 개발자가 직접 실행방식을 원하는대로 유도하는 방법
• Hint가 무시되는 경우
  1. 문법적으로 틀린 경우
  2. 의미적으로 틀린 경우 : RBO에서 CBO Hint (ex. first_rows_10), unnest 와 push_subq를 같이 쓴 경우
  3. 잘못된 참조 사용 : 없는 Table, Index, Alias 지정
  4. 논리적으로 불가능 : JOIN에 등치(=)조건 없이 Hash Join으로 유도, Nullable 칼럼에 대해 Index를 활용해 COUNT(*) 계산시도

• Hint 종류는 별도로 정리할 예정

3.2 Query Transformation (쿼리 변환)

• Optimizer가 SQL을 분석하여 동일하지만 더 나은 성능의 SQL로 재작성
• Query Transformer가 담당

Query 변환 방식

• Heuristic Query 변환 : 결과만 보장된다면 무조건 수행. Rule-based 최적화 기법
• Cost-based Query 변환 : 변환된 Query의 비용이 더 낮을 때만 그것을 사용

Subquery Unnesting

• Nested Subquery를 풀어서 Main-query와의 JOIN된 형태로 변환
  • Optimizer는 JOIN방식에 대해서 여러가지 최적화 기법을 시도 할 수 있다.
  • 만약 Nested Subquery를 그대로 두고 최적화를 해야 한다면 각각의 Subquery, Main-query별로 최적화를 해야하는데, 이렇게 부분의 최적화가 전체 수행 성능의 최적화를 보장하진 못한다.
  • 관련 Hint
    • UNNEST : Unnesting 하여 JOIN방식으로 유도
    • NO_UNNEST : 그래도 둔 상태에서 Filter 방식으로 최적화 유도

SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT); -- Main (M) : Sub (1) 관계에서는 Unnesting이 대부분 유리
-- 변환
SELECT EMP.* FROM DEPT, EMP WHERE EMP.DEPT = DEPT.DEPTNO;

• Subquery가 M쪽 집합이거나 Non-unique Index일 경우
  • 예를 들어 SELECT * FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP)와 같은 경우
  • 위 방법과 같이 Unnesting하면 결과가 달라지게 된다. (EMP에는 같은 DEPTNO가 많다.)
  • 이럴 경우 Optimizer는 2 가지 방법 중 하나를 선택한다.
    • Sort Unique : Subquery쪽 Table이 1쪽임을 호가신할 수 없는 경우 먼저 Sort Unique를 수행한 후에 JOIN
    • Semi Join : Driving Table의 한 row가 Inner Table의 한 row와 JOIN에 성공하면 Outer Table의 다음 row를 진행. Main-query 쪽 Table이 먼저 Driving될 경우

View Merging

• 일반 View, Inline View를 JOIN으로 풀어서 변환
• 사람 눈으로 볼땐 Query가 블록화 되어 보기 편하지만 Optimizer는 가급적 풀어서 JOIN형태로 변환한 뒤 최적화를 시도
• 단순한 View는 Merging하여도 성능이 나빠지지 않는다.
• 복잡한 연산을 포함하는 View는 Merging하면 성능이 나빠질 수 있다.
• Inline View Merging 예제

SELECT A.* 
  FROM (SELECT * FROM EMP WHERE JOB = 'SALESMAN') A,
       (SELECT * FROM DEPT WHERE LOC = 'CHICAGO') B
 WHERE A.DEPTNO = B.DEPTNO;

-- 변환

SELECT *
  FROM EMP A, DEPT B
 WHERE A.DEPTNO = B.DEPTNO
   AND A.JOB = 'SALESMAN'
   AND B.LOC = 'CHICAGO';

• View Merging 예제

CREATE OR REPLACE VIEW EMP_SALESMAN
AS
SELECT * FROM EMP WHERE JOB = 'SALESMAN';

SELECE E.*
  FROM EMP_SALESMAN E, DEPT D
 WHERE D.DEPTNO = E.DEPTNO
   AND E.SAL >= 1500;

-- 변환

SELECT E.*
  FROM EMP E, DEPT D
 WHERE D.DEPTNO = E.DEPTNO
   AND E.JOB = 'SALESMAN'
   AND E.SAL >= 1500;

• View Merging시 성능이 나빠질 수 있는 연산
  • GROUP BY 절
  • DISTINCT 연산
• View Merging이 불가능한 연산
  • 집합(SET) : UNION, UNION ALL, INTERSECT, MINUS
  • CONNECT BY 절
  • ROWNUM pseudo 칼럼
  • 집계 함수 (AVG, CONUT, MAX, MIN, SUM, ...)
  • 분석 함수 (Analytic Function)
• 관련 Hint : MERGE, NO_MERGE

Predicate Pushing (조건절 푸싱)

• 조건절을 가능한 빨리 처리되도록 View 안으로 밀어넣어서 처리량을 최소화 하는 방식
• 종류
  • Predicate Pushdown : Query 블록 밖에 있는 조건절은 안으로 밀어 넣음
  • Predicate Pullup : Query 블록 안의 조건절을 밖으로 내오와서, 다른 Query 블록 안으로 Pushdown하는데 사용
  • Join Predicate Pushdown : NL Join 수행시 Driving Table에서 읽은 값을 Inner View Query 쪽으로 밀어 넣음
• Predicate Pushdown

SELECT DEPTNO, AVG_SAL
  FROM (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO)
 WHERE DEPTNO = 30;

Inline View 안에서 DEPTNO에 대해서만 GROUP BY 하여 데이터량을 줄일 수 있다.

• Predicate Pullup

SELECT *
  FROM (SELECT DEPTNO, AVG(SAL)           FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO) E1,
       (SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP                   GROUP BY DEPTNO) E2
 WHERE E1.DEPTNO = E2.DEPTNO;

E1의 조건을 Pullup하여 E2로 Pushdown하여 데이터량을 줄일 수 있다.

• Join Predicate Pushdown

SELECT D.DEPTNO, D.DNAME, E.AVG_SAL
  FROM DEPT D,
       (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) E
 WHERE E.DEPTNO(+) = D.DEPTNO;

D에 존재하는 DEPTNO에 대해서만 E에서 수행하여 데이터량을 줄일 수 있다.

조건절 이행(Transitive Predicate Generation, Transitive Closure)

SELECT * FROM DEPT D, EMP E
 WHERE E.JOB = 'MANAGER'
   AND E.DEPTNO = 10
   AND D.DEPTNO = E.DEPTNO

E의 DEPTNO = 10 조건을 D에서도 수행

불필요한 JOIN 제거 (Join Elimination)

SELECT E.* FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO;

• D의 참조가 전혀 없으므로 제거
• 단, PK, FK의 제약조건이 있어야만 가능.
  • PK가 없는 경우 Join Cardinality를 파악할 수 없으므로 결과가 달라 질 수 있음
  • FK가 설정되어 있다하더라도 EMP의 DEPTNO가 Nullable이면 결과가 달라질 수 있음

OR 조건을 UNION으로 변환

• OR 조건을 그대로 둘 경우 Full Table Scan으로 처리되거나 각각의 Column별 Index를 활용하여 Bitmap 연산을 하는 Index Combine으로 작동할 경우가 있다.
• 관련 Hint
  • USE_CONCAT : UNION ALL 표현 (OR-Expansion)을 유도
  • NO_EXPAND : 나누지 말고 그대로 실행

집합 연산을 JOIN 연산으로

SELECT JOB, MGR FROM EMP
MINUS
SELECT JOB, MGR FROM EMP WHERE DEPTNO = 10;

위 문장을 실행하면 Table Full Scan 후 Sort Unique 하는 연산을 2번 수행 후에 결과집합을 구하게 된다.
이 경우 아래와 같은 형태로 Query 변환을 하여 실행을 한다.

SELECT DISTINCT JOB, MGR FROM EMP E
 WHERE NOT EXISTS (SELECT 'X' FROM EMP
                    WHERE DEPTBO = 10
                      AND SYS_OP_MAP_NONNULL(JOB) = SYS_OP_MAP_NONNULL(E.JOB)
                      AND SYS_OP_MAP_NONNULL(MGR) = SYS_OP_MAP_NONNULL(E.MGR)

• SYS_OP_MAP_NONNULL() : null끼리 비교시 true값을 반환하도록 처리

JOIN Column에 IS NOT NULL 조건 추가

• 어차피 NULL인 Column은 JOIN에 실패한다. 그러기 때문에 미리 NULL인 Column에 대해서 Filtering 하면 불필요한 액세스를 줄일 수 있다. (Oracle의 경우 해당 Column의 NULL값 비중이 5% 이상이면 내부적으로 추가해준다.)

SELECT EMPNO, DNAME
  FROM EMP E, DEPT D
 WHERE D.DEPTNO = E.DEPTNO
   AND SAL <= 2900
   AND E.DEPTNO IS NOT NULL  -- Optimizer가 추가
   AND D.DEPTNO IS NOT NULL  -- Optimizer가 추가

Filter 조건 추가

SELECT * FROM EMP WHERE SAL BETERRN :MIN AND :MAX

위 Query에서 :MIN 이 :MAX보다 크면 당연히 결과는 공집합이다.
이 경우 :MIN 값이 :MAX보다 작거나 같다는 Filter조건을 임의로 추가해서 실행해준다.

WHERE 비교 순서

WHERE 절에 비교할 컬럼이 많은 경우 그 중 작업량을 많이 줄여줄거라 판단한 조건부터 먼저 비교한다.