성능고도화 3-1. 옵티마이저

옵티마이저란 

 - 오라클은 10g 이후로 CBO만 지원함 

규칙기반 옵티마이저 

 - 휴리스틱 옵티마이저 

 - OLTP 환경의 중소형 데이터베이스 시스템이라면 RBO 규칙이 어느 정도 보편 타당성을 갖는다.

   그러나 RBO는 대용량 데이터를 처리하는데 있어 합리적이지 못할 때가 많다. 

비용기반 옵티마이저 

 - 전통적인 I/O 비용 모델에서의 I/O 요청 횟수만으로 비용을 평가하지 않고, 

    CPU 연산 비용까지 감안하여 수행 일량을 상대적인 시간 개념으로 환산해서 비용을 평가 

 - CBO가 실행계획을 수립할 때 판단 기준이 되는 비용은 어디까지나 예상치 

 - 오브젝트 통계뿐만 아니라 하드웨어적 특성을 반영한 시스템 통계정보까지 이용 

최적화 수행단계를 요약 

  ● 사용자가 던진 쿼리수행을 위해 , 후보군이 될만한 실행계획을 찾는다. 

  ● 데이터 딕셔너리에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 

        각 실행계획의 예상비용을 산정한다. 

  ● 각 실행계획의 비용을 비교해서 최저비용을 갖는 하나를 선택한다. 

 

동적 샘플링 

 - 만약 테이블과 인덱스에 대한 통계정보가 없거나 너무 오래돼 신뢰할 수 없을 때 옵티마이저가

    동적으로 샘플링을 수행하도록 할 수 있다. 

 - 통계 정보가 생성되지 않으면 하드 파싱 할때마다 동적 샘플링을 위한 recursive call 발생 

    ( 성능이 좋지 못하다. ) 

CBO를 기준으로 SQL 처리 절차 요약 

   ● Query Transformer 

   ● Estimator 

   ● Plan Generator

옵티마이저 모드  

 - rule 

 - all_rows 

    ● DML 문자은 일부 데이터만 가공하고 멈출 수 없으므로 옵티마이저 모드에 상관없이 

        항상 all_rows 모드로 작동한다. 

    ● Select 문장도 union, minus 같은 집합 연산자나 for update 절을 사용시 

        all_rows 모드로 작동

    ● PL/SQL 내에서 수행되는 SQL도 힌트를 사용하거나 기본 모드가 rule인 경우를 제외하면 

        항상 all_rows 모드로 작동 

 - first_rows

    ● first_rows는 비용과 규칙을 혼합한 형태의 옵티마이저 모드다.  

    ● 얼마만큼 fetch 할지 지정하지 않았으므로 정확한 비용을 예측할 수 없고, 

         따라서 옵티마이저 내부적으로 정해진 규칙을 사용한다. 

    ●  first_rows는 이제 과거 버전과의 호환성을 위한 용도로만 남게 되었다.  

 - first_rows_n 

    ● n으로 지정할 수 있는 값은 1, 10, 100, 1000 네 가지며, 사용자가 지정한 n개 로우이상을 

        Fetch 한다면 오히려 더 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다. 

        (파라미터 사용 시 ) 

    ● 힌트를 사용할 때는 괄호 안에 () 보다 큰 어떤 정수 값이라도 입력 가능하므로 

        파라미터를 이용 할 때보다 더 정밀하게 제어할 수 있다. 

    ● first_rows_n은 완전한 CBO 모드로 작동한다. 

 - choose 

    ● 엑세스되는 테이블 중 적어도 하나에 통계정보가 있다면 CBO, 그중에서도 all_rows 모드를 

        선택한다. 

    ● 9i 까지는 choose가 기본 설정이었으나 10g부터는 all_rows가 기본 옵티마이저 모드로 

        설정된다.

         ( 통계정보 없는 테이블을 발견하면 무조건 동적 샘플링이 일어나기 때문에 

           RBO로 작동할 일이 없어진 것이다. )

옵티마이저 모드 선택 

 - first_rows 모드가 효과적인 애플리케이션 아키텍처는 주로 2-Tier 환경의 클라이언트/서버 구조

   이 애플리케이션 구조의 특징은 전체 결과집합이 아무리 많아도 사용자가 스크롤을 통해 

   일부만 Fetch하다가 멈춘다는 점이다. 결과집합을 끝까지 Fetch 하거나 다른 쿼리를 수행하기 

   전까지 SQL 커서는 오픈된 상태를 유지한다. 

 - OLTP성 애플리케이션이라도 3-Tier 구조는 클라이언트와 서버 간 연겨을 지속하지 않는 

   환경이므로 오프 커서를 계속 유지할 수 없어 페이지 처리 기법을 주로 사용한다. 

   이는 대량의 데이터에서 일부만 fetch 하다 멈추는 것이 아니라 집합 자체를 소량으로 

   정의한다는 것이다. 

 - 애플리케이션 특성상 확실히 first_rows가 적합하다는 판단이 서지 않는다면 all_rows를 

   기본 모드로 선택하고, 필요한 쿼리 또는 세션 레벨에서 first_rows 모드로 전환할 것을 권고