3.1 SQL과 옵티마이져

3.1.2.3 옵티마이져 목표(Goal)의 선택

가) 옵티마이져 모드의 종류

  • 초기결과 최적화(First_rows)
    • 일부라도 수행되는 것을 목표
    • 비용기준과 경험적 방법(Heuristic method)을 혼합한 접근 방법 사용
    • 과거의 'CHOOSE' 모드와 유사 : 비용기준 접근법과 규칙 기준 방법 중에서 상황에 따라 최적화를 수행
    • 예) 마라톤과 같이 일부라도 먼저 통과하는 것을 목표로 함

 

  • 전체결과 최적화(ALL_rows)
    • 전체가 모두 수행되는 것을 목표
    • 비용기준 옵티마이져(CBO, Cost-Based Optimizer) 기본 모드
    • 예) 팀간 달리기, 구보

 

  • FIRST_ROWS_n
    • n은  커트라인을 의미
    • n 이라는 숫자에 큰 의미는 없음.  커트라인에 따라서 실행계획이 크게 변하는 경우 없기 때문
    • 비용기준으로 최적화 접근
    • FIRST_ROWS 확장 버전

 

FIRST_ROWS 와 FIRST_ROWS_n 차이점

개념적으로 별 차이 없음

옵티마이져 형태에서 차이를 보임 (FRIST_ROWS_n 비용기준 최적화, FIRST_ROWS 비용기준과 경험적 방법 혼함    )

 

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

  • OLTP 환경
    • 'CHOOSE' 모드에서 개발된 경우 : FIRST_ROWS
    • 새로운 버전에서 개발된 시스템 : FIRST_ROWS_n
  • OLAP 형 업부처럼 배치처리 위주의 시스템 : ALL_ROWS
  • 변경시 주의사항
    • 운영중 변경은 위험하다
    • 충분한 준비가 필요하다.
    • 옵티마이져 버전을 지정하는 초기 설정 파라메터에 과거에 사용하던 버전을 지정한다.
    • 실행계획 요약본을 생성하여 이를 참조하는 아우트라인(Outline)을 이용한다.

 

 

OLTP(오엘티피. OnLine Transaction Procession)

  • 여러 과정이 하나의 단위 프로세스로 실행되도록 하는 프로세스
  • 데이터 입력이나 거래조회 등을 위한 트랜잭션 지향의 업무를 쉽게 관리해주는 프로그램

OLAP(오랩. OnLine Analytical Processing)

  • 최종 사용자(end-user)가 다차원 정보에 직접 접근하여 대화식으로 정보를 분석하고 의사결정에 활용하는 과정

 

 

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

  • CURSOR_SHARING
    • 의   미 : SQL 문장 내의 상수값들을 변수로 전환하여 파싱을 할 수 있다.
    • 옵    션 : FORCE, SIMILAR, EXACT
    • 기본값 : EXACT(대,소문자, 공백, 비교되는 상수값이 조금만 달라도 공유하지 못함)  ⇒ 부하발생 가능성 있음
    • SIMILAR 사용 권유 : SQL 조건절에 상수값을 지정했더라도 옵티마이저는 변수로 인정하여 실행계획을 수립하므로 실행계획의 공유 비율을 높일 수 있음
    • FORCE, SIMILAR는 실행계획의 등급 적용에 차이만 있을뿐 거의 동일하다

 

  • DB_FILE_MULTIBLOCK_READ_COUNT
    • 의   미 : 전체 테이블을 스캔하거나 인덱스 고속전체스캔을 할 때 한번의 I/O에 읽어야 할 블록 수를 지정
    • 기본값 : 8
    • 대용량의 배치처리를 위주로 한다면 이 값을 좀 더 증가시키면 좋다.

 

  • OPTIMIZER_INDEX_CACHING
    • 의   미 : Nested Loops 조인이나 IN-List 탐침으로 수행되어 인덱스를 통해 반복 랜덤 액세스를 할때 인덱스 블록이 버퍼에 캐쉬되어 있을 활률
    • 기본값 : 0
    • 랜던 액세스가 많이 발생하는 환경에서 반복 액세스가 일어나는 인덱스 들이 적은 블록을 가졌거나 특정한 범위를 주로 액세스하여 재사용률이 매우 높다면 이 값을 증기시켜 주면 좋다.
    • 이 값의 수정은 비용의 조정을 의미 => 옵티마이져에 영향을 미칠 수 있음. 주의

 

  • OPTIMIZER_INDEX_COST_ADJ
    • 의   미 : 비용 계산을 할 때 인덱스의 액세스 비중을 조정하는 역할을 담당
    • 기본값 : 100   (10을 주면 1/10로 계산)
    • 인덱스로 액세스되어야 할 경우가 전체테이블 스캔으로 자주 일어날 경우 조정 필요
    • 전체적으로 기본값으로 두고 조정시엔 특정한 세션에 선별적으로 적용하는게 바람직하다.

 

  • 동적 표본화(Dynamic Sampling)
    • 소량의 표본을 동적으로 추출하여 통계정보를 활용 => 통계정보가 없을 시 비용기준으로 작동 가능
    • 적용 사례
      • 통계정보가 없을 때
      • 에러 등의 문제로 사용할 수 없을 때
      • 너무 오래되어 더 이상 신뢰할 수 없을 때
    • 적용하면 안되는 경우 (∵ SQL이 파싱될 때마다 표본을 추출해야 하므로)
      • 적은 양의 데이터를 처리하는 경우
      • 빈번하게 수행되는 경우

 

 

 3.1.2.4 실행계획의 고정화(Stability)

 

  •  아우트라인(Outline)
    • 과거에 수립되었던 실행계획의 요약본을 참조하여 실행계획을 수립하는 기능
    • 실행계획 요약본은 새롭게 실행계획시 양질의 최적화를 얻을 수 있게 한다.
    • 범용적(Public), 개별적(Private)으로 관리 가능
    • 필요에 따라 적용(Enable)시키거나 금지(Disable) 가능. 또, 편집도 가능
    • 카테고리 생성 가능 (선별적 적용 가능)

     

     

    가) 아우트라인의 생성과 조정

    • 아우트라인을 생성하거나 조정하는 패키지
      • DBMS_OUTLN
      • DBMS_OUTLN_EDIT

     

    • 프로시저
      • CREATE_OUTLINE : 지정된 건을 공유커서에서 찾아 아우트라인을 생성한다.
      • CLEAR_USED : 지정한 아우트라인을 제거한다.
      • DROP_BY_CAT : 지정한 카테고리에 속한 아우트라인들을 제거한다.
      • DROP_UNUSED : SQL 파싱에 사용된 적이 없는 아우트라인을 제거한다.
      • UPDATE_BY_CAT : 어떤 카테고리를 새로운 카테고리로 변경한다.
      • GENERATE_SIGNATURE : 지정한 SQL 문에 대한 식별자를 생성한다.

     

    나) 아우트라인의 관찰

     

     

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

    •  규칙기준 옵티마이져를 비용기준으로 전환
      1. 특정 카테고리를 주고 아우트라인 생성을 시작한다.
        • ALTER SESSION SET CREATE_STORED_OUTLINES = category_name;
        • 전체적으로 적용시 : ALTER SYSTEM...
      2. 대부분의 SQL에 대해 아우트라인이 생성되도록 오랜 기간 수집한다.
      3. 아우트라인 생성을 종료한다.
        • CREATESTORED_OUTLINES 파라메터를 'FALSE'로 지정한다.
      4. DBMS_STATS 패키지를 이용하여 통계정보를 생성한다.
      5. 옵티마이져 모드를 변경한다.
        • RULE => CHOOSE
      6. USE_STORED_OUTLINES 파라메터에 앞서 생성한 카테고리를 지정하여 아우트라인을 적용한다.
        • 일부만 아우트라인을 적용할 경우 : ALTER SESSION...
        • 전체적으로 적용할 경우 : ALTER SYSTEM...

     

    • 옵티마이져 버전을 업그레이드 할 경우
      1. 특정 카테고리를 주고 아우트라인 생성을 시작한다.
        • ALTER SESSION SET CREATE_STORED_OUTLINES = category_name;
        • 전체적으로 적용시 : ALTER SYSTEM...
      2. 대부분의 SQL에 대해 아우트라인이 생성되도록 오랜 기간 수집한다.
      3. 아우트라인 생성을 종료한다.
      4. 옵티마이져 버전 업그레이드
      5. 애플리케이션을 수행하면서 테스트 실시

     

    3.1.2.5 옵티마이져의 한계

    • 현재의 정보만으로 처리범위를 예측하기 어려움
      • 많은 정보를 가지고 있더라도
      • 정보는 COST, 얼마나 많은 정보를 가질수 있는가?

     

    • 분포도 산정의 어려움
      • 컬럼의 사용 연산자별로 정확한 분포도를 얻을 수 없다.
      • 컬럼의 결합에 따른 정확한 분포도를 얻을 수 없다.
      • 모든 결합형태에 대한 통계정보를 가질 수 없다.
      • 단위 컬럼 분포도들의 계산에 의해 산정한 분포도를 신뢰할 수 없다.

     

    종합적이고 전략적인 차원에서 적절한 인덱스나 클러스터링을 결정하고, 수준 높은 SQL을 구사하는 것이 필요