SQL 튜닝비법 - 옵티마이저

  1. 옵티마이저란? DBMS의 두뇌라고 할 수 있으며, 사용자가 실행한 SQL을 해석하고 데이터를 추출하기 위한 실행계획을 수립하는 프로세스이다.
  2. 옵티마이저의 종류
    1. RBO (Rule Based Optimizer)
      1. 처리 방식의 우선 순위를 정해서 앞서는 방식을 채택하여 실행계획을 수립한다.
      2. 처리 방식에 따른 우선 순위
        1. Row ID에 대한 단일 행 실행
        2. Cluster 조인에 의한 단일 행 실행
        3. Hash Cluster Key에 의한 단일 행 실행
        4. Unique Key 또는 Primary Key에 의한 단일 행 실행
        5. Cluster 조인
        6. Hash Cluster Key
        7. Indexed Cluster Key
        8. 결합 컬럼(Composite) 인덱스
        9. 단일 컬럼(Single) 인덱스
        10. 인덱스에 의한 유한 영역(Bounded Range) 검색
        11. 인덱스에 의한 무한 영역(Unbounded Range) 검색
        12. 소트 머지(Sort Merge) 조인
        13. 인덱스로 구성된 컬럼의 최대 또는 최소 처리
        14. 인덱스로 구성된 컬럼의 Order by
        15. 인덱스 없이 전체 태이블 스캔 (FTS : Full Table Scan)
      3. 위 처리 순서 중에서 1부터 15 순으로 채택하도록 한다.
      4. 한계점
        1. 힌트를 사용할 수 없다.
        2. Hash Join을 사용할 수 없다.
    2. CBO (Cost Based Optimizer)
      1. 비용 즉, 옵티마이저가 문장을 수행하는데 걸릴 것이라고 추측한 시간을 최소화 해서 데이터를 빨리 처리하도록 실행계획을 수립한다.
      2. 영향을 미치는 비용 산정 요소 : 각종 통계 정보, Hint, 연산자, Index, Cluster, DBMS 버전, CPU/Memory 용량, Disk I/O 등과 같이 매우 다양한다.
        1. CBO에서 영향을 미치는 비용 산정 요소 중에서 주기적으로 확인이 필요한 통계정보는 가장 효율적인 실행계획을 수립하기 위한 최소 비용을 계산하는데 중요하게 사용된다.
        2. 통계정보는 정기적인 분석 작업을 통해서 확인한다.
          1. 분석작업은 ANALYZE 명령어를 사용하는 방식과 DBMS_STATS 패키지를 이용하는 방법이 있다. 
          2. ANALZE 실행 방법
            ANALYZE 사용법
            ANALYZE TABLE emp COMPUTE STATISTICS;
            : emp 테이블의 전체 내용을 분석한다.

            ANALYZE TABLE emp ESTIMATE STATISTICS
                                                SAMPLE 10 PERCENT;
            : emp 테이블의 10%의 내용만 분석한다.

            ANALYZE TABLE emp ESTIMATE STATISTICS
                                                SAMPLE 5 ROWS;
            : emp 테이블의 5개의 Rows만 분석한다.

            ANALYZE INDEX XINDEX COMPUTE STATISTICS;
            : XINDEX Index의 내용을 분석한다.

            -- ANALYZE 실행 여부 확인
            SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED
            FROM USER_TABLES
            WHERE TABLE_NAME IN ('테이블 명')
          3.  DBMS_STATS 패키지 실행 방법
            DBMS_STATS 패키지 사용법
            EXEC DBMS_STATS.GATHER_TABLE_STATS(Owner Name->'Schema_Name', 'Table Name', Estimate_Percent->10, NULL, TRUE/FALSE(Cascade))
            : 특정 테이블에 대한 통계 정보 생성

            EXEC DBMS_STATS.GATHER_TABLE_STATS(Owner Name->'Schema_Name')
            : 특정 스키마에 대한 통계정보 생성
  3. 옵티마이저 모드
    1. initSID.ora라는 파일 내에 OPTIMIZER_MODE로 실행계획을 어느 종류의 옵티마이저로 쓸지, 어느 수준까지 나타낼지, 범위는 어떻게 되는지 등에 따라 실행계획이 달라지게 되는데 이것을 설정하여 최적의 실행계획을 나타내게 된다.
    2. 모드 종류
      1. RULE : RBO으로 최적의 실행계획을 세운다.
      2. CHOOSE : SQL이 실행되는 환경에서 통계 정보를 가져올 수 있으면 CBO, 그렇지 않으면 RBO로 실행계획을 세운다.
      3. FIRST_ROWS : 옵티마이저가 처리할 결과 중 첫 건을 출력하는데 걸리는 시간을 최소화 할 수 있는 실행계획을 세운다.(CBO)
      4. FIRST_ROWS_n : n 건을 출력하는데 걸리는 시간을 최소화 할 수 있는 실행계획을 세우는 것으로 FIRST_ROWS를 보완하기 위한 옵션이다.(CBO)
      5. ALL_ROWS : SQL 실행 결과 전체에 대한 최적화된 실행 계획을 세운다.(CBO)
  4. RBO와 CBO의 실행계획 비교
    1. 예시
      실행 계획에 대한 예시
      SELECT
       E.ENAME,
       D.DNAME
      FROM EMP E,
                 DEPT D
      WHERE E.DEPNO = D.DEPNO
      AND D.DEPNO = 10
      ;
    2. RBO를 이용한 실행계획
      Id
      Operation
      Name
      Rows
      0 SELECT STATEMENT
      		3
      1  NESTED LOOPS
      		3
      2   NESTED LOOPS
      		3
      *3    TABLE ACCESS FULL EMP 1
      *4    INDEX UNIQUE SCAN PK_DEPT 1
      5   TABLE ACCESS BY INDEX ROWID DEPT 1
    3. CBO를 이용한 실행계획
      Id
      Operation
      Name
      Rows
      0SELECT STATEMENT
      		3
      1 NESTED LOOPS
      		3
      2  TABLE ACCESS BY INDEX ROWIDDEPT1
      *3   INDEX UNIQUE SCANPK_DEPT1
      *4  TABLE ACCESS FULLEMP3
    4. 위와 같이 RBO와 CBO로 수립한 실행계획이 다른 경우, 수립할 실행계획이 동일하게 수정이 필요하며, 이 때 필요한 것이 SQL 튜닝이다.
참고도서 : 실전사례로 살펴보는 SQL 튜닝비법