Ashburton Grove

1. 옵티마이저의 개념

사용자가 실행한 SQL을 해석하고, 데이터 추출을 위한 실행계획을 수립하는 프로세스. 프로세스가 수립하는 실행계획은 전반적인 SQL의 속도를 결정 짓는다.


2. 옵티마이저의 종류

오라클에서는 RBO(Rule Based Optimizer), CBO(Cost Based Optimizer) 2가지의 옵티마이저를 지원해주고 있다. 나머지 DBMS는 CBO만 지원해주고 있다. 
RBO는 초창기 버전부터 제공. CBO는 v10g부터 기본적인 설정으로 적용되고 있다. 

1) RBO
- 기본적으로 15개의 순위가 매겨진 규칙이 있음. (이를 기초로 하여 실행계획을 수립함.) 순위는 인덱스 구조나 사용하는 연산자에 따라 각각 다르다.
- SQL에 대한 실행계획이 하나 이상일 경우엔, 순위가 높은 규칙을 이용하게 됨.
- 수립될 실행계획이 예측 가능하기 때문에 개발자가 원하는 처리 경로로 유도하기가 쉬움.

* 아래 표는 RBO의 우선 순위를 보여준다.

(Rank 1 ~ 4는 하나의 데이터만 찾는다는게 특징)

2) CBO
- 대상 row들을 처리하는데 필요한 자원 사용을 최소화해서, 궁극적으로 데이터를 빨리 처리하는 데 목적이 있음
- CBO에 영향을 미치는 산정 요소
(각종 통계 정보, Hint, 연산자, Index, Cluster, DBMS 버전, CPU/Memory 용량, Disk I/O 등과 같이 매우 다양함) 

* 통계정보
- CBO의 성능을 최적의 상태로 유지시키기 위해서 테이블, 인덱스, 클러스터 등을 대상으로 통계정보를 생성함 -> 정기적으로 ANALYZE 작업을 하는 것이 가장 중요함(변경된 부분이 생길 수도 있으므로)
- 가장 효율적인 실행계획을 수립하기 위해 최소비용을 계산할 때 중요하게 사용됨

ANALYZE 예시)
ANALYZE TABLE emp COMPUTE STATISTICS; (전체대상)
ANALYZE TABLE emp ESTIMATE STATISTICS
  SAMPLE 10 PERCENT;
ANALYZE TABLE emp ESTIMATE STATISTICS
  SAMPLE 5 ROWS;

ANALYZE 실행 여부 확인 쿼리)
SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME IN ('EMP', 'DEPT')
;

-> 오라클에서는 DBMS_STATS Package를 통해 확인 가능함.
(DBMS_STATS.GATHER_TABLE_STATS, DBMS_STATS.GATHER_SCHEMA_STATS, DBMS_STATS.GATHER_DATABASE_STATS)


3. 옵티마이저의 레벨별 설정

1) Instance Level 

- initSID.ora를 이용해서 지정함 (DB전체 차원)
OPTIMIZER_MODE = RULE, CHOOSE, FIRST_ROWS, ALL_ROWS 중에 하나 선택
(9g까지는 CHOOSE가 default, 10g부터는 ALL_ROWS가 default)

2) Session Level
- ALTER SESSION SET OPTIMIZER_MODE = RULE, CHOOSE, FIRST_ROWS, ALL_ROWS 중 하나를 선택

3) Statement Level
- 아래 쿼리와 같은 힌트 사용
SELECT /*+first_rows*/
ename
FROM emp;

* 우선 순위는 Statement Level이 가장 높다.


4. RBO와 CBO의 실행계획 비교

- 동일 SQL에 대해서 각 OPTIMIZER가 수립한 실행계획은 서로 다를 수 있음.
- 이는 SQL의 퍼포먼스가 OPTIMIZER에 따라 다르다는 의미.
- OPTIMIZER 종류에 따라 달라지는 DB성능의 차이점을 이해하는 것이 중요함.

인덱스 :

EC_TASK_PK : COURSE_CODE + TASK_NO

EC_TASK_TERM_IDX00 : COURSE_CODE + TASK_NO + YEAR + COURSE_SQ_NO

쿼리문 :

SELECT 

A.COURSE_CODE, A.TASK_NO, A.BBS_YN, A.UPDATE_DATE,

B.YEAR,  B.S_DATE, B.E_DATE

FROM EC_TASK A, EC_TASK_TERM B

WHERE A.COURSE_CODE = B.COURSE_CODE

AND A.TASK_NO = B.TASK_NO

AND B.COURSE_CODE = 36;

AND B.TASK_NO = 1

AND B.COURSE_SQ_NO = 1

;

위 쿼리문을 RBO로 해석하면 B에서 WHERE 조건절을 통해 데이터를 찾고 A와 조인을 통해 데이터를 합친다.

(B -> A 의 순서)

이에 반해 CBO로 해석하면 JOIN 조건인 COURSE_CODE, TASK_NO의 조건이 A에도 입력되어 있다 가정하고 A에서 데이터를 먼저 찾고 B와 합친다. (A는 조건이 2개(COURSE_CODE, TASK_NO)지만 B는 조건이 3개(COURSE_CODE, TASK_NO, COURSE_SQ_NO) (A-> B의 순서)

SQL를 만들 때, 실행계획이 차이나면 좋지 못한 SQL이다.예시에서는 CBO가 순서가 더 빠르다 ROWS가 적으므로
SQL을 수정한다면, 누가봐도 A에서 데이터를 찾고 B에서 데이터를 찾도록 아래와 같이 수정해야한다,

수정된 쿼리문 :

SELECT 

A.COURSE_CODE, A.TASK_NO, A.BBS_YN, A.UPDATE_DATE,

B.YEAR,  B.S_DATE, B.E_DATE

FROM EC_TASK A, EC_TASK_TERM B

WHERE A.COURSE_CODE = B.COURSE_CODE

AND A.TASK_NO = B.TASK_NO

AND A.COURSE_CODE = 36;

AND A.TASK_NO = 1

AND B.COURSE_SQ_NO = 1

;