[Tuning] perfect_study_01

1일차_참고자료.zip

Database 기동 작업
==================
 
  /DBA1/dba00> sqlplus "/as sysdba"

  SQL>  startup
  ORACLE instance started.
  Total System Global Area                         51454112 bytes
  Fixed Size                                          73888 bytes
  Variable Size                                    33538048 bytes
  Database Buffers                                 16777216 bytes
  Redo Buffers                                      1064960 bytes
  Database mounted.
  Database opened.

  SQL> exit


Optimizer
=========

- SQL에 대해 해석(parse)을 하고, 실행계획을 수립한 후
   이를 통해 데이터를 처리하는 프로세스를 일컫는다.
                              ---------
   (cf) 실행계획(Execution plan) 이란
        Optimizer가 데이터 처리를 위해 수립하는 작업 방법을
        일컫는다.
 

tkprof utility
==============

- 기본적으로 SQL에 대한 각종 정보수집을 하면서, 그 결과로서
   트레이스 파일이 생성되며 이 때 생성된 트레이스 파일을 통해
   튜닝에서 필요한 정보를 분석할 수 있게 한다.

※ 관련된 Parameters

   ① SQL_TRACE  (FALSE/TRUE)      → 트레이스 파일 생성을 위해 지정
   ② TIMED_STATISTICS(FALSE/TRUE) → 시간관련 정보수집을 위해 지정
   ③ USER_DUMP_DEST               → 트레이스 파일이 생성되는 곳을 지정
   ④ MAX_DUMP_FILE_SIZE           → 트레이스 파일이 최대크기 지정


※ SQL에 대한 정보 수집 과정

  SQL> ALTER SESSION SET SQL_TRACE = TRUE;

  SQL> SELECT *
         FROM EC_TASK A, EC_TASK_TERM B
        WHERE A.COURSE_CODE  = B.COURSE_CODE
          AND A.TASK_NO      = B.TASK_NO
          AND A.COURSE_CODE  = 36
          AND B.TASK_NO      = 1
          AND B.COURSE_SQ_NO = 1;

  SQL> ALTER SESSION SET SQL_TRACE = FALSE;
-----------------------------------------------------------

→ tkprof utility 실행

eg. tkprof ###.trc tuning.txt sys=no aggregate=no explain=ecampus/ecampus


◆ tkprof에 의한 통계 항목들

  ○ Parse
    - SQL문이 파싱되는 단계에 대한 통계
    - 새로 파싱을 했거나, 공유 풀에서 찾아 온 것도 포함됨.
    - 단, PL/SQL 내에서 반복 수행(Loop)된 SQL이나 PL*SQL에서 보존
      커서(Hold cursor)를 지정한 경우에는 한 번만 파싱됨.

  ○ Execute
    - SQL문의 실행단계에 대한 통계임.
    - UPDATE, INSERT, DELETE 문들은 여기에 수행한 결과가 나타나게 됨.
    - 전체범위 방식으로 처리된 결과가 여러 건인 경우는 주로 여기에
      많은 값이 나타나며 Fetch에는 아주 적은 값이 나타남.

  ○ Fetch
    - SQL문이 실행되면서 Fetch된 통계임.
    - 부분범위 방식으로 처리된 SELECT 문들이나 전체범위 처리를 한 후
      한 건을 추출하는 경우   (AGGREGATE, 전체집계, COUNT 등)는 주로
      여기에 많은 값들이 나타나고 EXECUTE에는 아주 적은 값이 나타남.

  ○ Count
    - SQL문이 파싱된 횟수, 실행된 횟수, FETCH가 수행된 횟수

  ○ Cpu
    - PARSE, EXECUTE, FETCH가 실제로 사용한 CPU 시간(1/100초 단위)

  ○ Elapsed
    - 작업의 시작에서 종료시까지 실제 소요된 총 시간

  ○ Disk
    - 디스크에서 읽혀진 데이타 블록의 수

  ○ Query
    - 메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해 변경
      되었으나 아직 Commit되지 않아 복사해 둔 스냅샷 블록을 읽은 블록의 수
    - SELECT 문에서는 거의가 여기에 해당하며 UPDATE, DELETE, INSERT시에는
      소량만 발생됨.

  ○ Current
    - 현 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한
      블록(Dirty Block)을 액세스한 블록 수
    - 주로 UPDATE, INSERT, DELETE 작업시 많이 발생
    - SELECT문에서는 거의 없으나 아주 적은 양인 경우가 대부분임.


Part 1. 옵티마이저 모드

Optimizer는 SQL에 대해 해석(parse)을 하고, 실행계획을 수립 후  
이를 통해 데이터를 처리하는 프로세스이다.

Optimizer는 Rule base와 Cost base로 두 종류가 있으며, 두 종류
간에는 상당히 많은 차이가 있다. 이와 같은 차이점을 구분하지 않
고서는 튜닝이 어렵다고 할 수 있다.

따라서 Part 1 에서는 주어진 사례를 통해서 Optimizer간 차이점을
생각해 보고, 튜닝에서 고려해야 할 사항으로는 무엇이 있는지  구
체적으로 알아보고자 한다.


- Optimizr 지정
  
※ OPTMIZER_MODE을 사용해서 지정함.

    ① CHOOSE     - v9i까지의 기본값
    ② ALL_ROWS   - Cost Base임을 뜻함 (v10g의 기본값)
    ③ FIRST_ROWS - Cost Base임을 뜻함
    ④ RULE       - Rule Base임을 뜻함

★ OPTIMIZER_MODE = CHOOSE

       SQL에서 사용하는 테이블에 대한 비용관련 정보의 존재여부
       에 따라 비용관련 정보를 갖고 있는 테이블을 사용하는 SQL
       은 Cost Base로서, 반면 비용관련 정보를 갖고 있지 않는
       테이블을 사용하는 SQL은 Rule Base로서 사용하게 된다.

      eg. 테이블       비용관련 정보 존재여부
          ------       ----------------------
            A                 있다
            B                 있다
            C                 없다
            D                 없다


     ① SELECT ~     (Cost Base)
          FROM A, B
         WHERE ...;

     ② SELECT ~     (Rule Base)
          FROM C, D
         WHERE ...;

     ③ SELECT ~     (Cost Base)
          FROM A, D
         WHERE ...;

    ---------------------------------------------------------
    이때 각 테이블별로 생성된 비용관련 정보의 존재여부를
    확인하기 위해서는 다음과 같은 SQL이 필요하다.

    SQL> SELECT TABLE_NAME, NUM_ROWS, AVG_SPACE, LAST_ANALYZED
           FROM USER_TABLES
          WHERE TABLE_NAME IN ('EC_TASK','EC_TASK_TERM');
    ---------------------------------------------------------


[사례연구 #01] SQL 분석 시 차이

동일한 SQL을 Rule Base와 Cost Base 상에서 각각 실행하게
될 때 실행계획이 서로 다르게 수립되는 것을 확인함으로써,
이를 통해 두 Optimizer 간 차이점을 확인함과 동시에 각
Optimizer별로 SQL을 어떻게 해석해야 하는지를 살펴 봄.


(예제)
        
  SELECT A.*, B.*
    FROM A, B
   WHERE A.KEY = B.KEY
     AND A.KEY = 10;

  Rule Base 옵티마이저는 작성된 그대로 SQL을 인식하지만,
  Cost Base 옵티마이저는 다음과 같이 조건을 인식하게 됨.

  SELECT A.*, B.*
    FROM A, B
   WHERE A.KEY = B.KEY
     AND A.KEY = 10
     AND B.KEY = 10;  <--- Cost Base optimizer가 자동으로 인식!!!



[사례연구 #02] 인덱스 사용 시 차이

Optimizer mode가 Rule base일 때와 Cost base일 때, SQL실행에
있어서 테이블로 부터 데이터 처리 시 각 optimizer가 사용하는
인덱스는 서로 다르다는 것을 구체적으로 알아봄.

→ 두 optimizer는 사용하는 인덱스 대부분이 서로 다르다.


★★★ 인덱스 선택 기준 → "인덱스 매칭률"
  
※ Optimizer의 인덱스 선택 시 판단 절차
  
   1) SQL의 조건에 의해 사용 가능한 인덱스 별로
      인덱스 매칭률을 계산해서 그 중 인덱스 매칭률이
      높은 인덱스를 우선적으로 선택하게 된다.

   2) 인덱스 매칭률이 같을 경우엔, 인덱스별로 각각
      인덱스 컬럼 개수를 참고해서, 컬럼 개수가 많은
      인덱스를 우선적으로 선택하게 된다.

   3) 인덱스 매칭률과 인덱스가 갖고 있는 컬럼의 개수가
      같을 경엔, 가장 최근에 생성된 인덱스를 우선적으로
      선택하게 된다.

     (참고) 위의 절차를 RBO와 CBO 모두 사용하지만,
            특히 CBO는 이외에도 Cost를 고려해서 최종
            판단을 하게 된다는 점에서 RBO와 차이점이
            있다.


                   Where절에 첫째 컬럼부터 연속된 컬럼에 대해서
                   '='조건으로 상수가 사용된 인덱스 컬럼의 개수
   인덱스 매칭률 = --------------------------------------------
                              인덱스 컬럼의 총 개수


  eg. 매칭률 계산에 대한 예제 :

                    1st  2nd  3rd
(예) 결합인덱스 : ( 시 + 구 + 동 )

  1) 매칭률 = 1/3    →  2) 매칭률 = 2/3      →  3) 매칭률 = 3/3

    WHERE 시 = '서울시';   WHERE 시 = '서울시'     WHERE 시 = '서울시'
                             AND 구 = '강서구';      AND 구 = '강서구'
                                                     AND 동 = '화곡동';

(예) 결합인덱스 : ( 시 + 구 + 동 )

   ① 매칭률 = 1/3

      ⓐ WHERE 시 = '서울시';

     Q. 아래와 같은 조건에 대한 매칭률은 어떻게 되는가?

      ⓑ WHERE 시 = '서울시'
           AND 동 = '역삼동';

      ⓒ WHERE 시 = '서울시'
           AND 구 LIKE '강%'
           AND 동 = '역삼동';
    
   ② 매칭률 = 2/3

      ⓐ WHERE 시 = '서울시'
           AND 구 = '강남구'

      ⓑ WHERE 시 = '서울시'
           AND 구 = '강남구'
           AND 동 LIKE '역%'

      → 이러한 경우는 시 = '서울시'와 구 = '강남구' 에 의해서
         처리범위가 결정
         즉, (시,구)에 대한 '='이 검색조건임을 의미함


   ③ 매칭률 = 3/3

      교환법칙 :  Y * X = X * Y

      ⓐ WHERE 시 = '서울시'     ⓑ WHERE 시 = '서울시' 
           AND 구 = '강남구'          AND 동 = '역삼동'      
           AND 동 = '역삼동';         AND 구 = '강남구';

      ⓒ WHERE 구 = '강남구'     ⓓ WHERE 구 = '강남구'
           AND 동 = '역삼동'          AND 시 = '서울시'
           AND 시 = '서울시';         AND 동 = '역삼동';

      ⓔ WHERE 동 = '역삼동'     ⓕ WHERE 동 = '역삼동'
           AND 시 = '서울시'          AND 구 = '강남구'
           AND 구 = '강남구';         AND 시 = '서울시';

      → 즉, (시,구,동)에 대한 '='들이 모두 검색조건임을 의미함


[사례연구 #03] Analyze의 SQL에 대한 영향력

특정한 SQL의 퍼포먼스를 향상시키기 위한 방법으로 ANALYZE를
실행하게 되는 경우, 이로 인해 오히려 퍼포먼스에 문제가 되는
SQL이 있을 수 있다 것을 확인함.


참고) ANALYZE 명령 사용(Cost Base일 경우에만 사용)
                        ----------
   Optimizer의 판단에 의해 산정한 비용이 최소인 것을 채택함
   비용 산정시 이용되는 정보 중 Objects에 대한 ANALYZE 정보가
   매우 큰 비중을 차지함. 정기적으로 통계정보를 생성하는 것이
   매우 중요함

예) OPTIMIZER_MODE = CHOOSE

       SQL에서 사용하는 테이블에 대한 비용관련 정보의 존재여부
       에 따라 비용관련 정보를 갖고 있는 테이블을 사용하는 SQL
       은 Cost Base로서, 반면 비용관련 정보를 갖고 있지 않는
       테이블을 사용하는 SQL은 Rule Base로서 사용하게 된다.

      eg. 테이블       비용관련 정보 존재여부
          ------       ----------------------
            A                 있다
            B                 있다
            C                 없다
            D                 없다

     ① SELECT ~     (Cost Base)
          FROM A, B
         WHERE ...;

     ② SELECT ~     (Rule Base)
          FROM C, D
         WHERE ...;

     ③ SELECT ~     (Cost Base)
          FROM A, D
         WHERE ...;


   Q. ③번과 같은 SQL의 근본적인 문제는 비용(cost)의 정확성이
      없다는 것인데, 이를 해결하기 위해서 테이블 A를 대상으로
      analyze 작업을 수행하는 경우에 있어서 발생 할 수 있는
      문제로는 무엇이 있는가?

      ①번과 같이 테이블 A를 사용중인 SQL의 퍼포먼스에 영향을
      미칠 가능성이 존재한다. 즉, ①번과 같은 SQL은 Rule base
      속성을 갖는 SQL이었지만 테이블 A에 대해서 analyze하고 난
      뒤로는 Cost base속성을 갖는 SQL이 된다.



★ Nested Loops 조인의 튜닝 포인트

   1) 조인순서의 최적화
   2) 조인조건(연결고리)에 따른 인덱스 생성 및 사용 여부

      - 발생 가능한 조인순서
        가정 : 테이블 3개 : A, B, C  (6 가지)

          ① A → B → C           우선 첫 번째 테이블을 먼저 결정함.
          ② A → C → B           물론 가장 적게 데이터를 찾을 수 있는         
          ③ B → A → C           테이블이어야 한다.
          ④ B → C → A           다음으로 두 번째 테이블을 결정함.
          ⑤ C → A → B           앞서와 마찬가지로 다른 테이블들 보다
          ⑥ C → B → A           데이터를 적게 찾을 수 있는 테이블이
                                   되어야 한다.

★★★
   Suppressing

   → 인덱스 컬럼에 변형을 가함으로써 해당 컬럼을 첫 번째로하는
      결합 인덱스 및 해당 컬럼만 갖는 단일컬럼 인덱스를 사용하지
      못하게 하는 것을 의미한다.

      모든 DBMS에서 사용할 수 있는 방법이다.


★ 특정 SQL의 퍼포먼스 향상을 위해서 analyze를 할 수는 있다.
    이 때, 충분히 생각해야 할 것은 analyze를 하고자 하는 테이블을
    사용하고 있는 다른 SQL들이 있는지 여부이다.

    만약에 이와 같은 SQL이 많이 있음에도 불구하고 확인하지 않고서
    analyze를 하는 경우에는 퍼포먼스에 문제가 발생하는 SQL이 나타
    날 수 있기 때문에 충분히 검토할 수 있어야 한다.


[사례연구 #04] From절의 테이블 순서 변경

★ Nested Loops 조인에서 조인순서 제어 방법

   ① /*+ ORDERED */, /*+ LEADING(t명) */ 등과 같은 힌트 사용
   ② Suppressing 사용
   ③ Inline view 사용
   ④ FROM 절에 있는 테이블 순서 변경 (단, Rule Base일때만)
     
   FROM 절에서의 테이블 순서 변경을 통한 방법은 Rule Base일 경우에
   규칙이 서로 같은 테이블들에 대해서만 적용 가능하다.

   특히, Rule base 옵티마이저는 규칙이 같은 테이블들에 대해 FROM 절
   기준으로 볼 때 뒤에 있는 테이블 부터 먼저 처리하게 된다.

   단, Cost Base일 경우에는 적용될 수 없다.