SQLP 3-3 Optimizer

in Devlopment on SQLP


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 예제 ```sql 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 예제
```sql
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 절에 비교할 컬럼이 많은 경우 그 중 작업량을 많이 줄여줄거라 판단한 조건부터 먼저 비교한다.

이 글이 도움이 되셨다면 공감 및 광고 클릭을 부탁드립니다 :)