[튜닝] 옵티마이저 동작의 조정

■ 오라클9i 기준 옵티마이저의 기본 동작
  ○ 해당 SQL문이 참조하는 개체들에 대해 사용할 수 있는 통계가 전혀 없는 경우 RBO 사용
  ○ 최소한 하나의 참조 개체에 대한 통계가 준비되어 있다면 CBO 사용
  ○ 사용자 또한 옵티마이저 모드 및 목표를 설정할 수 있음.
    - 인스턴스 레벨
      => OPTIMIZER_MODE 파라미터의 설정(파라미터 파일의 수정 또는 alter system 명령)
    - 세션 레벨
      => OPTIMIZER_MODE 파라미터의 설정(alter session 명령)
    - 명령문 레벨
      => 힌트 사용


■ 인스턴스 또는 세션레벨에서의 OPTIMIZER_MODE 파라미터 값
  - CHOOSE : 오라클9i 옵티마이저의 기본 동작에 따름. 10gR2부터는 사라짐.
  - RULE : 통계 존재 여부에 상관없이 RBO 사용. 10gR2부터는 사라짐.
  - ALL_ROWS : 전체 처리율의 최적화.
  - FIRST_ROWS[_n] : 처음 결과가 나올 때까지의 시간을 줄이기 위해 최적화.


■ SQL문장 레벨에서의 힌트
  - /*+ CHOOSE */ : 오라클9i 옵티마이저의 기본동작에 따름.
  - /*+ RULE */ : 통계 존재 여부에 상ㅇ관없이 RBO 사용.
  - /*+ ALL_ROWS */ : 전체 처리율의 최적화.
  - /*+ FIRST_ROWS[(n)] */ : 처음 결과가 나올 때까지의 시간을 줄이기 위해 최적화.


■ RBO 동작의 조정
  ○ RBO는 SQL의 syntax에 기반하며, 내장된 규칙에 따라 동작
  ○ 따라서 조정이 어려우며 조정할 수 있는 범위 또한 제한적
    - FROM절에서 테이블 순서 변경
    - WHERE절에서 술어 순서 변경
    - 인덱스의 생성 및 삭제
    - 기타 syntax 트릭의 사용

◇ Syntax 트릭 사용 예

SQL> select e.ename, d.dname
   from emp e, dept d
   where e.deptno = d.deptno
   and e.ename = 'FORD'
   and d.dname between 'HR' and 'SALES';

조인의 철칙은 앞에 읽는 테이블의 데이터(내가 찾고자 하는 데이터 건수)가 무조건 더 작아야 한다. 옵티마이저는 = 조건이 between 조건이 들어간 것보다 데이터를 더 잘 추려낼수 있을 것이라고 추정.

그러나 실제로 데이터 분포 현황을 보니 아래와 같았다고 가정.
ename => 10 / 100      // 10%
dname => 5   / 100     //  5%

이렇게 되면 무조건 dname이 들어간 테이블을 드라이빙 테이블로 잡아야 한다.
하지만 RBO를 쓴다면 무조건 = 조건이 들어간 것을 드라이빙 테이블로 잡기 때문에 융통성이 적다.
우리가 해야 할일은 dname을 드라이빙 테이블로 잡지 않게끔 해주는 것이다.

SQL> select e.ename, d.dname
   from emp e, dept d
   where e.deptno = d.deptno
   and substr(e.ename, 1, 4) = 'FORD'
   and d.dname between 'HR' and 'SALES';

위와 같이 syntax 트릭을 이용하면 드라이빙 테이블을 제어 가능하다.

■ CBO의 조정
  ○ 데이터에 대한 통계 수집
    - 가장 원칙적인 방법
    - CBO가 최적의 실행계획을 산출하기 위해서는 정확한 통계정보의 제공이 필수적
  ○ 힌트의 사용
    - SQL문장 레벨
  ○ CBO의 행동에 영향을 주는 파라미터의 설정
    - 인스턴스 또는 세션 레벨


■ 옵티마이저 힌트
  ○ 실행계획의 결정은 원래 옵티마이저의 역할이나 옵티마이저가 반드시 최적의 실행계획만을 산출하는 것은 아니다. 사용자가 옵티마이저는 알지 못하는 최적의 정보를 알고 있을 수 있다. 이러한 경우 사용자는 힌트를 이용하여 옵티마이저의 결정에 영향을 미칠 수 있음.
  ○ 필요한 경우 힌트를 전역적으로 저장하는 방법에 의해 실행계획의 일관성을 제공해 주는 Stored Outline 기능을 사용할 수 있음.
  ○ 힌트 사용 규칙
    - SQL 블록의 첫 키워드 바로 뒤에 입력
    - 각 블록에는 하나의 힌트 주석만 있어야 하지만, 하나의 힌트 주석은 여러 개의 힌트 포함 가능
    - 힌트는 해당 블록에만 적용
    - 문장에 alias를 사용하는 경우 힌트는 그 alias를 참조해야 함.
    - 힌트는 옵티마이저에 의해 무시될 수 있음.(syntax상 오류 또는 옵티마이저가 생각하길 비효율적인 힌트일 경우)


■ CBO에 영향을 주는 파라미터들
  - OPTIMIZER_MODE
  - OPTIMIZER_FEATURES_ENABLE
  - CURSOR_SHARING
  - DB_FILE_MULTIBLOCK_READ_COUNT
  - SORT_AREA_SIZE, HASH_AREA_SIZE, PGA_AGGREGATE_TARGET
  - HASH_JOIN_ENABLED
  - OPTIMIZER_INDEX_CACHING
  - OPTIMIZER_INDEX_COST_ADJ
  - OPTIMIZER_MAX_PERMUTATIONS
  - PARTITION_VIEW_ENABLED
  - QUERY_REWRITE_ENABLED
  - STAR_TRANSFORMATION_ENABLED

---

[면접예상질문] CURSOR_SHARING 파라미터의 3가지 모드 각각의 의미에 대해 설명하시오.
  1. EXACT
    - 2개의 문장에 사용된 SQL문이 모두 동일한 조건을 가져야만 파싱 정보를 공유할 수 있다.
    - where 조건에 정의된 상수가 동일해야 한다.

SQL> select * from dept where deptno = 30;
SQL> select * from dept where deptno = 40;

    => 실행된 2개의 문장에 대한 파싱 정보가 각각 생성된다.

  2. SIMILAR
    - 2개의 문장에 사용된 SQL문이 모두 동일해야 파싱 정보를 공유할 수 있다.
    - 단, where 조건에 정의된 바인드 변수가 각각 다른 값을 가지고 있더라도 공유할 수 있다.

SQL> select * from dept where deptno = 10;
SQL> select * from dept where deptno = 12;

    => 실행된 2개의 문장에 대한 파싱 정보가 하나만 생성된다.

  3. FORCE
    - 2개의 문장에 사용된 SQL문이 모두 동일해야 파싱 정보를 공유할 수 있다.
    - 단, where 조건에 정의된 상수가 다르더라도 커서를 공유할 수 있다.

SQL> select * from dept where deptno = 10;
SQL> select * from dept where deptno = 12;

    => 실행된 2개의 문장에 대한 파싱 정보가 하나만 생성된다.

[면접예상질문] DB_FILE_MULTIBLOCK_READ_COUNT 파라미터에 대해 설명하시오.
풀스캔 시 한번에 몇개의 블록을 끌어올릴 것인가에 대한 파라미터. 오라클 쪽에서 해당 파라미터 값을 적어준다 해도 OS의 커널에 따라 파라미터 값만큼 모두 끌어올릴 수 없을 수도 있다. 그러므로 해당 OS가 몇 개의 블록을 끌어올릴 수 있는지 확인 후 파라미터 값에 적어주는 것이 현명하다.