[퍼옴]옵티마이져(OPTIMIZER)

엔코아 정보 컨설팅 자료를 참고해서 작성해 봤습니다.

1. 옵티마이져(OPTIMIZER) ?

 

OPTIMIZING이란 SQL 문장을 수행하기 위하여 가장 효과적인 방법을 선택하는 처리과정을 말합니다.
이것은 DML 문장을 수행하는데 있어 중요한 단계 중 하나이며,  이러한 역할을 하는 담당하는 부분을
우리는 OPTIMIZER라 부릅니다.

어떻게 하면 SQL문을 가장 효과적으로 처리할까.. 이것이 옵티마이져의 역할이죠..

◈ SELECT, UPDATE, INSERT, DELETE문을 최소 비용으로 신속하게 처리 할 수 있을까 고민 합니다..

◈ 오라클의 옵티마이저 부분은 문장의 실행을 위해서 요구된 최소한의 프로세싱 시간과
     I/O를 위하여 참조된 데이타의 효과적인 경로를 결정하기 위하여 사용합니다.
   
◈ 옵티마이저는 실행계획(execution plan)을 세우고 문장을 실행하기 전에 가장
     효과적인 계획을 선택합니다.

◈ 옵티마이저는 SQL 문을 위한 실행 계획의 표현은 규칙 기반 접근법(rule-based approach) 또는     
     비용 기반 접근법(cost-based approach) 중 하나를 사용 합니다.  

◈ 모든 경우에 Optimizer가 완벽하게 처리해 주는 것이 아니기 때문에 사용자가 옵티마이저보다 SQL문장을
    수행하는 더 효율적인 방식을 선택할 수 도 있습니다.(대표적인 예가 오라클 힌트의 사용 입니다.)

 

2. Optimization Mode

 옵티마이져의 모드는 아래와 같은 방법으로 수정 할 수 있습니다.

◈  초기화 파라미터 파일 수정
   - OPTIMIZATION_MODE = COST 또는
      OPTIMIZATION_MODE = RULE

◈ ALETER SESSION 명령어 사용
   - ALTER SESSION SET OPTIMIZER_GOAL = COST

 

3. Rule-Based(규칙기반) Optimizer

 규칙기반 접근법은 OPERATIONS의 Ranking 순위에 따라 실행계획을 선택하게 됩니다.
 만약 SQL 문장을 수행하기 위한 방법이 하나 이상 있다면 규칙기반 접근법은 Ranking 순위가 더 낮은 것을
 사용하게 됩니다.

 SELECT *
 FROM emp
 WHERE ename like '%s%'
   AND empno = 8888   

 위와 같은 SQL문이 있다고 하면.. 규칙기반 접근에서는
 WHERE절에 ename like '%s%'  보다는  empno = 8888 이 보다 적은 결과값이 나올것으로
 옵티마이져는 예상하기 때문에..   empno = 8888 부분을 먼저 실행시킵니다..

 LIKE연산자 보다는 = 연산자가 순위가 더 낮다고 보시면 됩니다.

 물론 일반적으로 Ranking 순위가 낮은 operations은 Ranking 순위가 높은 operations 보다 빠른 수행
 속도를 보장하지만  항상 이 규칙이 성립하는 것은 아닙니다.

 간혹 복잡한 SQL에서는 Ranking 순위가 높은 operations이 Ranking 순위가 낮은 operations 보다
 더 좋은 수행 속도를 나타내기도 하기때문에 규칙기반 접근법으로 모든 SQL를 처리하는 데는 많은
 문제점이 있습니다.

[수행단계]

   가. 수행 가능한 실행 계획을 구한다.

   나. Rank Table에 따라서 순위를 결정한다

   다. 가장 낮은 순위를 선택하여 수행한다.

 

4. Cost-Based(비용기반) Optimizer

비용기반 접근법은 가장 효과적인 실행계획을 수립하기 위하여 데이터베이스의 통계 자료를 사용 합니다.

오라클 RDBMS은 ANALYZE명령어를 사용하여 테이블, 클러스터, 인덱스 등의 통계 자료들을 수집 저장합니다.

비용기반 접근법은 가장 효과적인 실행계획을 수립하기 위하여 Data Dictionary에서 해당 OBJECT에 대한
통계정보, 이용 가능한 Access paths, HINT 등을 모두 고려하게 됩니다.

비용기간 접근법은 통계 정보의 생성 여부, 인덱스 전략의 수립에 많은 영향을 받으므로
비용기반 접근법을 사용하는 경우에는 이러한 작업에 보다 더 신중을 기해야 합니다.

[수행단계]

가. Optimizer는 Access path와 Hint 등을 이용하여 가능한 실행계획 들을 수립한다.

나. 통계정보를 근거로 하여 각 실행계획의 비용을 산정한다

다. 가장 적은 비용의 SQL을 선택한다.

 

5. 참고..(en-core에 질문과 답변에 김정삼님의 글 참고)

 DB 인스탄스 초기화 파일인 init.ora 파일에 보면은 아래와 같은 파라미터가 있습니다.
"OPTIMIZER_MODE = XXX"

XXX에 '대치'될 수는 것들에 대해서 알아보겠습니다..

1) CHOOSE
    테이블에 통계정보가 있으면 CBO/ALL_ROWS, 없으면 RBO로 하겠다는 뜻

2) ALL_ROWS
    CBO로 하겠다는 것인데, 보다 구체적으로 throughput을 OPTIMIZER GOAL로 합니다.
    여기서, throughput을 '목표'로 한다는 의미는 전체 SQL문을 수행하는데,
    최소의 자원을 소비하는 것을 '목표'로 한다는 뜻입니다.

3) FIRST_ROWS
    CBO로 하겠다는 것인데, 보다 구체적으로 하나의 row를 리턴하는데 최소
    response time을 OPTIMIZER GOAL로 합니다.

4) RULE
    RBO로 하겠다는 뜻

옵티마이져의  '똑똑함'으로 둘을 비교해 보면
100점 만점에 RBO는 50점 정도 이면서 그 이상을 포기한 '넘'이고,
CBO는 딴에 100점을 지향하지만 실제 대략 5-70점 정도 되는 '넘'이 아닌가 합니다...
물론 상황에 따라 '변덕'이 죽 끓듯 하구요...
암튼, 100점이 되기 위해선 아무래도 더 똑똑한 '사람'의 손길이 필요하죠...