'옵티마이저'에 해당되는 글 2건



DBMS Optimizer(옵티마이저)


1. DBMS Optimizer (옵티마이저)의 개요

 1-1. 정의

 - 사용자의 다양한 요구에 따라 SQL문의 문법적 오류를 확인하고 가장 빠른 데이터 엑세스 경로(Plan)을 생성하고 선택하여 최적의 경로를 찾아주는 역할을 하는 DBMS의 핵심 엔진

 - 옵티마이저에 영향을 미치는 요소 : SQL 형태, 연산자 형태, 힌트사용, 분산DB, 인덱스/클러스터링, 통계정보, 옵티마이저 모드, DBMS 버전


 1-2. 목표

 - 전체 처리속도 최적화

 - 최초 응답속도 최적화


2. DBMS Optimizer (옵티마이저)의 개념도 및 요소 설명, 종류

 2-1. DBMS Optimizer (옵티마이저)의 개념도

2-2. DBMS Optimizer (옵티마이저)의 요소 설명

 1) Parse : 구문, 보안, Object 등의 체크 및 Simple Transformation을 수행

 2) Query Rewrite : 서브질의와 부의 병합을 수행하고, OR Expansion 작업을 수행

 3) Query Optimization : 질의에 대한 엑세스 경로를 결정

 4) QEP Generation : 질의실행계획 (질의를 실행하는데 필요한 상세 정보를 생성)

 5) Query Execution : QEP에 따라 SQL 문장을 실행


 2-3. DBMS Optimizer (옵티마이저)의 종류

 1) RBO : 사전에 실행할 SQL문의 순서를 규칙으로 설정하여 그 규칙에 따라 SQL문의 실행 순서를 결정하는 옵티마이저

 2) CBO: 실행 처리 방법들에 대한 비용을 산정하여 그중에 가장 적은 비용이 들어가는 처리방법들을 선택하는 옵티마이저


3. DBMS Optimizer (옵티마이저)의 발전방향

 1) RBO의 한계 : 추출해야하는 쿼리나 데이터가 복잡해지면서 단순 정해진 규칙대로 수행에 한계가 발생되어 CBO를 많이 사용함

 2) Self Learning Optimizer : 과거에는 단순히 오프젝트 및 시스템 통계로 부터 산정한 비용만으로 실행계획을 수립했으나 앞으로는 예상치와 수행결과를 비교하여 오류를 스스로 수정하여 최적화를 도출하는 옵치마이저로 발전 예상


블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,


1. SQL과 옵티마이저

  • 논리적으로 처리 가능한 경로는(인덱스, 클러스터, 옵티마이져 모드, 수립된 통계정보, SQL문장과 형태, 시스템 및 네트워크 상태 등) 여러가지에 의해 종합적으로 감안하여 옵티마이져가 실행 계획을 수립.

       

  • 선택성에 가장 중요하게 미치는 정보는 컬럼값들에 대한 차별적인 분포도(밀도)와 엑세스의 분류 단가 즉 클러스터 팩터 부분이다.

       

    • Clustering Factor 란 Index의 Table에 대한 정렬 정도이다. 이것은 Index를 Scan하는 동안 방문(ACCESS)하게 되는 Table의 Data Block의 개수이다. 즉 넓은 범위의 Data를 Index를 경유해서 읽을 경우, Clustering Factor가 Physical Reads의 발생 빈도에 큰 영향을 미친다.

         

  • 옵티마이저가 가장 실행 계획을 수립하는데 절대적으로 영향을 미치는 것은 가장 최소량을 처리할 수 있도록 하는 것과 가장 싼값으로 엑세스 할 수 있느냐가 가장 큰 요소이다.

       

  • 옵티마이져와 우리의 역할
  • SQL이라는 언어로 요구를 하면 DBMS는 최적의 경로를 사용하여 결과를 보여준다. 여기서 최적의 경로를 계산하는 부분이 옵티마이져의 역할이다.

       

  • 사용자가 준비해 두어야 할 기본적인 옵티마이징 팩터는 인덱스 구성에 대한 전략과 적절한 SQL을 작성하는 것이다. 그러나 적절한 판단을 하기 위해서는 실행 계획을 이해하고, 제어하기 위해서 옵티마이져를 알아야 한다.

       

  • 옵티마이져의 형태
    • 규칙기준 옵티마이져(RBO, Rule Based Optimizer)
      • 인덱스 구조나 비교연산자에 따라 순위를 부여하여 이것을 기준으로 최적의 결정을 결정.

           

        • ROWID 1 로우 액세스
        • 클러스터 조인에 의한 1 로우 액세스
        • Unique HASH Cluster 의한 1 로우 액세스
        • Unique Index 의한 1 로우 엑세스
        • Cluster 조인
        • Non Unique Hash cluster key
        • Non Unique cluster key
        • Non Unique 결합 인덱스
        • Non unique 컬럼 인덱스
        • index 의한 범위처리
        • index 의한 전체범위처리
        • Sort Merge 조인
        • 인덱스 컬럼의 Min, MAX 처리
        • 인덱스 컬럼의 order by
        • 전체테이블 스캔

           

      • 같은 랭킹이라면 Where절은 뒤에서 부터, From절도 뒤의 객체가 우선순위를 갖는다.

           

      • 한 객체(테이블)아ㅔ서 같은 랭킹의 인덱스가 있다면 가장 최근에 만들어진 인덱스를 사용

           

      • 장점
        • 사용자가 문제점을 미리 예측하고 자신이 원하는 방법으로 실행계획을 제어하기 쉽다.
        • 전략적인 인덱스를 구성하여 SQL를 구성 하였을 경우 다른 요소에 실행 계획이 변동되는 변수가 적다.

             

      • 단점
        • 현실적인 요소를 무시하여 개산하므로 판단 오차가 크게 나타날 수 있음.

           

           

    • 비용기준 옵티마이져(CBO, Cost Based Optimizer)
      • 통계 정보를 바탕으로 실제의 Cost비용을 산출, 비교하여 최소비용이 드는 방식 선택

           

      • 통계 정보 형태, 종류 DBMS, Version에 따라 차이는 있지만 보통 테이블의 로우 수, 블록 수 블록당 평균 로우수, 로우의 평균길이, 컬럼별 상수값의 종류, 분포도, 컬럼 내 Null 값의 수, 클러스터링 팩터, 인덱스의 깊이, 최소 최대 값, 리프 블럭수, 가동시스템의 I/O, CPU사용정보 등을 가지고 있다.

           

      • 장점
        • 현실을 감안한 판단을 할 수 있다는 것
        • 통계정보의 관리를 통해 최적화를 제어 할 수 있다.
        • 옵티마이져를 깊이 이해하고 있지 않더라도 최소한의 성능이 보장된다.

             

      • 단점
        • 실행계획을 미리 예측하기 어렵다.
        • 버전에 따라 변화가 심하다.
        • 실행게획의 제어가 어렵다.

         

  • 실행계획의 고정화(Stability)
    • 아우트라인(Outline)
      • 과거에 수립되었던 실행계획의 요약본을 저장하고 있다가 이것을 참조하여 실행 계획을 수립하는 기능

           

      • 일반적으로 통계정보의 변화에 따라 변동되는 실행계획을 최적화된 실행계획으로 고정시키고자 하는 용도로 사용

           

    • 아우트라인 사용의 바람직한 방법
      • 잘 정비된 옵티마이징 팩터와 적절한 SQL을 기반으로 대부분의 경우는 옵티마이저에게 맡기고 특별히 문제가 있는 경우에 대해서만 아우트라인으로 통제
        • 카테고리(Category): Outline의 선별적인 적용을 가능하게 하기 위하여 지정한 그룹

             

  • 옵티마이저의 최적화 절차
    • 옵티마이져의 목표
      • 사용자가 요구한 결과를 가장 최소의 자원으로 처리할 수 있는 방법을 찾아내는 것.

           

    • 옵티마이저 처리 과정
      • 최초 실행한 SQL은 딕셔너리를 참조하여 파싱을 수행 -> 옵티마이져는 파싱된 결과를 이용해 논리적으로 적용 가능한 실행 계획 형태를 골라내고, 힌트를 감안하여 일차적으로 잠정적인 실행계획들을 생성
      • 통계정보를 기반으로 데이터의 분포도와 테이블의 저장 구조의 특성, 인덱스 구조, 파티션 형태, 비교 연산자 등을 감안하여 각 실행계획의 비용을 계산-> 비용의 계산에는 컴퓨터의 자원(I/O, CPU,Memory)도 함께 감안됨
      • 비용이 산출된 실행계획들을 비교하여 가장 최소의 비용을 가진 실행 계획을 선택

           

    • 질의 변환기
      • 질의의 변환
      • 이행성규칙
      • 뷰병합 등… 보충필요

         

    • 비용 산정기
      • 선택도(Selectivity)
        • 처리할 대상 집합에서 해당 조건을 만족하는 로우가 차지하는 비율
        • 히스토그램 정보 유무에 따라 선택도 계산 방식은 달라짐

히스토그램정보가 없는 경우

통계정보만을 이용하여 선택도를 계산

히스토그램정보가 있는 경우

미리 계산된 분포값을 직접활용

  • 카디널리티(Cardinality)
    • 판정 대상이 가진 결과건수 혹은 다음 단계로 들어가는 중간결과건수를 의미
    • 위에서 계산한 선택도(Selectivity)와 전체 로우 수(Num_rows)를 곱해서 계산

     

  • 비용(Cost)
    • 각 연산들을 수행할 때 소요되는 시간비용을 상대적으로 계산한 예측치
    • 스키마 객체에 대한 통계정보에 추가적으로 CPU와 메모리 상황, Disk I/O비용도 고려되어 계산

         

  • 실행 계획 생성기
  • 적용 가능한 실행 계획을 선별하고 비교 검토를 거쳐 가장 최소의 비용을 가진 것을 선택
  • 다양한 적용 가능한 실행계획 형태에 대해 비교평가를 하지만 그렇다고 해서 논리적을 존재하는 모든 것에 대해 시도를 하지는 않는다.

       

    • 실행 계획 생성기는 아래의 두가지 전략을 사용
      • 쿼리 수행에 예상되는 총 수행시간에 비해 최적화에 소요되는 시간이 일정비율을 넘지 않도록 함.
      • 탐색 도중 최적이라고 발생하면 실행계획을 더 이상 진행하지 않고 멈춤.

   

<참고문헌>

Oracle Database 11g : Adminstration Workshop

Oracle Database 10g Documentation Library - councepts

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://www.gurubee.net/pages/viewpage.action?pageId=12353616>

블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,