Oracle SQL과 옵티마이져

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>