이화식 선생님의 새로쓴 대용량 데이터 베이스 1의 온라인 스터디 입니다.
매주 각 스터디 팀원들이 담당 분량을 정리해서 올리고 토론식으로 진행합니다.
스터디 포스팅은 팀원들에의해 이루어지지만 스터디 참여는 사이트 회원 모두가 가능합니다.

진행기간: 2009.07 ~ 2009.10. 종료

3.1 SQL의 실행계획 (2/3)

2009.07.24 20:53

운영자 조회 수:88411

3.1.2.3. 옵티마이져 목표(Goal)의 선택
가) 옵티마이져 모드의 종류
  • 초기 결과 최적화(First_rows)
    • 일부라도 우선 수행
    • 비용기준과 경험적 방법(Heuristic method)을 혼합한 접근 방법
    • 주어진 상황에 따라 선택하여 최적화를 수행

  • 전체결과 최적화(All_rows)
    • 전체가 모두 수행
    • CBO의 기본모드
    • 사용자 입장에서는 전체를 모두 처리하는것보다 First_Rows 모드가 일반적일수 있음
    • 부분처리를 원하는 SQL을 All_rows모드로 처리하면 심각한 속도 저하가 올수 있음
  • First_Rows_n  ??
    • n 은 처리를 원하는 범위
    • 비용기준으로 최적화를 접근

나) 옵티마이져 모드의 결정 기준

  • 기존 시스템이 있다면
    • 'CHOOSE'모드가 있던 버전 - FIRST_ROW
    • 신버전에서 개발된 버전 - FIRST_ROWS_N
    • 배치처리 위주 - ALL_ROWS
  • 옵티마이져 모드 변경
    • 충분한 준비후 작업
  • 모드 변경후 기존 SQL 을 위한방법
    • 파라메터 수정
    • 옵티마이져 버전을 지정하는 파라메터를 과거 버전으로 지정
    • OPTIMIZER_FEATURES_ENABLE (옵티마이져 버전 지정)
  • 기존 방법에서 안정화를 하려는것보다 신기술에대한 적용이 바람직
  • OUTLINE 이용
    • 요약본을 생성하여 이를 참조

다) 옵티마이져 모드와 관련된 파라메터 지정

  • CURSOR_SHARING
    • SQL문내의 상수값을 변수로 전환하여 파싱
    • 옵션
    • FORCE ; 상수로 입력되어도 변수로 인정 (SIMILAR보다 등급이 높음)
    • SIMILR ; 상수로 입력되어도 변수로 인정
    • EXACT(default) ; 대,소문자,공백,비교 상수값이 달라도 공유 불가
    • OUTLINE 적용하려면 FORCE,SIMILAR적용 필수
  • DB_FILE_MULTIBLOCK_READ_COUNT
    • 전체 테이블 스캔이나 인덱스 고속전체스캔을 할때 한번의 I/O로 읽어야하는 블록수 지정에 사용
    • Default 8 (대용량의 배치 위주라면 수치증가 시키는 것이 유리)
  • OPTIMIZER_INDEX_CACHING
    • 인덱스를 통해 반복 랜덤액세스할때 블럭들이 버퍼에 캐쉬되어있을 확률을 지정 (Nested Loops 조인이나 IN-List 탐침수행시)
    • Default 0 (반복랜덤엑세스가 빈번한 인덱스 블록이 많다면 값 증가)
  • OPTIMIZER_INDEX_COST_ADJ
    • 비용계산시 인덱스의 액세스 비중을 조정하는 역할
    • (Default) 100 (정상인 비용을 그대로 적용)
    • 10 (1/10으로 계산)
    • 인덱스액세스 경우가 전체 테이블 스캔으로 자주 일아날경우 조정할 필요있음
  • 'choose'모드
    • 옵티마이져의 비용기준으로 이전 과도기에 사용
    • 통계정보가 없을경우 규칙으로 전환
  • Optimizer_dynamic_sampling
    • 동적 표본화 (Dynamic Sampling)
    • 기존 choose모드의 대체 개념
    • 소량의 표본을 동적으로 자동으로 추출하여 통계정보로 활용 (소량이나 자주 수행될경우 적용하지 않아야함)
    • 통계정보 미보유,사용불능(에러등),너무오래된경우 적용
    • 파라메트값: 0 사용하지 않음
    • 기본블록수 32, 레벨증가시 2의 제곱만큼 증가
    • 전체 테이블 표본으로 할경우 10레벨

    인용:
    옵티마이져 모드 변경시 상황을 깊히 고려해야하며 필요에따라 세션만 적용하는 등의 방법으로 사용하여야한다

3.1.2.4. 실행계획의 고정화(Stability)
  • 아우트라인(Outline)
    • 동일한 실행계획을으로 재현할수 있도록 도와주는 최소한의 참조정보를 보유
    • 범용적,개별적으로 관리 하거나 적용,금지 시킬수 있으며 필요시 편집과 익스포트와 임포트역시 가능
    • 필요 조건에 따른 카테고리 별로 관리하는것도 좋음

가) 아우트라인의 생성과 조정
  • 패키지
    • DBMS_OUTLN 과 DBMS_OUTLN_EDIT
    • CREATE_OUTLINE:  지정된 건을 공유 커서에서 찾아 아우트라인을 생성
    • CLEAR_USED: 지정한 아우트라인을 제거
    • DROP_BY_CAT: 지정한 카테고리에 속한 아우트라인을 제거
    • DROP_UNUSED: SQL파싱에 사용된적이 없는 아우트라인을 제거
    • UPDATE_BY_CAT : 어떤카테고리를 새로운 카테고리로 변경
    • GENERATE_SIGNATURE: 지정한 SQL문에 대한 식별자 생성

    • 생성
      • CREATE_STORED_OUTLINES (파리메터) 를 특정세션에 지정하여 생성 시작,종료 하는것이 일반적임
      • TRUE나 FALSE를 지정하거나 카테고리 명 직접 지정가능
      • TRUE; 'DEFAULT' 카테고리로 생성

    • 사용
      • USE_STORED_OUTLINES 를 지정하여 사용
      • TRUE나 FALSE, 카테고리 명지정가능
      • ALTER OUTLINE 으로 아우트라인 이름이나 카테고리 변경, 재생성도 가능
  • PRIVATE 아우트 라인
      1) 기존 아우트 라인 복제
      CREATE PRIVATE OUTLINE prv_ol_1 From outln_1;
      2) 아우트라인 수정가능 툴이나 DBMS_OUTLN_EDIT 패키지의 프로시져를 이용해 갱신
         REFRESH_PRIVATE_OUTLINE- (수작업으로 갱신하였을경우 사용)
      3) 검정
        USE_PRIVATE_OUTLINES를 TRUE로 지정후 검증
      4) 적용
      CREATE OR REPLACE OUTLINE outln_1 From PRIVATE prv_ol_1;
      5) 수행종료
      USE_PRIVATE_OUTLINES 를 FALSE로 지정

나) 아우트라인의 관찰
    뷰를 통해 내용 확인 가능
    • USER_OUTLINES
    • USER_OUTLINE_HINTS
    • 권한이 허용하면 DBA나 ALL로도 가능

    코드:
    수행 SQL
    SELECT empno,ename,job,loc
    from emp e,dept d
    where e.deptno=d.deptno and e.empno=7856
    order by loc

    NAME : SYS    SYS_OUTLINE_050921102126523 -------------(A)
    CATEGORY : LHS001 ---------------------------------------(B)
    USED        : UNUSED
    TIMESTAM : 05 / 09 /16
    VERSION : 9.2.0.1.0
    SQL_TEXT : SELECT EMPNO, ENAME, JOB LOC FROM EMP E,DEPT D WHERE
                     e.deptno=d.deptno and e.empno=:"SYS_B_0" order b loc ----(C)
    SIGNATURE : 2E945C676F84B939E862C3F6D8A4E7CF -----------------(D)

    • A) 아우트라인의 명칭(자동으로 생성된것)
    • B) 생성시 부여한 카테고리
    • C) CURSOR_SHARING 값이 SIMILAR로 지정, 조건절의 상수값은 바인드 변수로 변경되어 생성
    • D) RAW 타입으로 생성된 SQL 식별자 , 동일 SQL을 다른 카테고리에 생성하면 값은 동일 A의 명칭은 변경됨
    코드:
    create outline emp_j_otl
    on SELECT emp.empno,emp.deptno,dept.loc FROM emp,dept
      WHERE emp.deptno=dept.deptno;


    SELECT * FROM USER_OUTLINES WHERE name='EMP_J_OTL';

    NAME     CATEGORY     USED     TIMESTAMP     VERSION     SQL_TEXT     SIGNATURE     COMPATIBLE     ENABLED     FORMAT
    EMP_J_OTL     DEFAULT     UNUSED     2009.07.21 21:42:14     10.2.0.1.0     SELECT emp.empno,emp.deptno,dept.loc FROM emp,dept WHERE emp.deptno=dept.deptno     AF8B1B1EB1ED3CD0C9523EF77C200ACA     COMPATIBLE     ENABLED     NORMAL


    실테이블에서 확인
    OUTLN.OL$HINTS
    SELECT * FROM OUTLN.OL$HINTS;


    _NAME     HINT#     CATEGORY     HINT_TYPE     HINT_TEXT     TABLE_NAME     COST     CARDINALITY     JOIN_PRED
    EMP_J_OTL     1     DEFAULT     19     USE_NL(@"SEL$1" "EMP"@"SEL$1")     EMP     4.00691534303308     4     EMP.DEPTNO = DEPT.DEPTNO
    EMP_J_OTL     2     DEFAULT     40     LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")           0     0     
    EMP_J_OTL     3     DEFAULT     101     INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))     EMP     .000160523044559189     2     
    EMP_J_OTL     4     DEFAULT     2     FULL(@"SEL$1" "DEPT"@"SEL$1")     DEPT     3.0046162831899     2     
    EMP_J_OTL     5     DEFAULT     111     OUTLINE_LEAF(@"SEL$1")           0     0     
    EMP_J_OTL     6     DEFAULT     113     ALL_ROWS           0     0     
    EMP_J_OTL     7     DEFAULT     109     OPTIMIZER_FEATURES_ENABLE('10.2.0.1')           0     0     
    EMP_J_OTL     8     DEFAULT     108     IGNORE_OPTIM_EMBEDDED_HINTS     

    실행계획
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1619650520

    ---------------------------------------------------------------------------------------
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |         |     4 |    92 |     4   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     2 |    24 |     1   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS              |         |     4 |    92 |     4   (0)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL        | DEPT    |     2 |    22 |     3   (0)| 00:00:01 |
    |*  4 |    INDEX RANGE SCAN         | EMP_IDX |     2 |       |     0   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")



    완벽한 실행계획의 모습은 아니지만 조인방법과 조인순서,인덱스,비용,카디널리티 까지 보유하고 있으며
    테이블을 수정하여 변경하는것도 가능


다) 옵티마이져 업그레이드 시의 적용

  • 규칙기준-> 비용기준
  • 기존 실행계획을 유지하면서 점차적으로 비용기준에 맞추어 가도록 하는전략
    • 1) 특정 카테고리를 주고 아우트 라인을 생성
    • alter session set create_stored_outlines = category_name;
    • 전체적으로 적용시 alter system
    • 2) 대부분의 SQL대해 아우트 라인이 생성되도록 충분한 기간을 두고 수집
    • 3)아우트 라인 종료 ' create_stored_outlines' 파라메터를 'false'로 지정
    • 4) dbms_stats 패키지를 이요하여 통계정보를 생성
    • 5) 옵티마지여 모드를 Rule에서 Choose로 변경
    • 6) 현세션만적용시 Alter Session ,전체 Alter system 을 이용

  • 옵티마져의 버전업등의 각 상황에 맞게 아웃라인을 생성해서  단계적으로 적용하며
  • 생성시 작업의 성격, 테이블의 성격등 업무특성에 맞게 카테고리 별로 생성시켜 두고 활용하는게 좋다

3.1.2.5. 옵티마이져의 한계
인용:
비용기준 옵티마이져는 완벽할수 없는통계정보를 토대로 정확한 처리범위를 예측할수 없다는 것
다양한 사용 형태를 만족할 수 있도록 종합적이고 전략적인 차원에서 적절한 인덱스나 클러스터링을 결정하고, 수준높은 sql을 구사하는것이 반드시 필요하다는 것