옵티마이저(Optimizer) 최적화

[Optimizer - 최적화]

1. OPTIMIZER란?

OPTIMIZING이란 SQL 문장을 수행하기 위하여 가장 효과적인 방법을 선택하는 처리과정을 말합니다.

이것은 DML 문장을 수행하는데 있어 중요한 단계 중 하나이며, 이러한 역할을 담당하는 부분을 우리는 OPTIMIZER라 부릅니다.

 

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

 

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

◎ 오라클의 Optimizer 부분은 문장의 실행을 위해서 요구된 최소한의 프로세싱 시간과 I/O를 위하여 참조된 데이타의 효과적인 경로를 결정하기 위하여 사용합니다.

◎ Optimizer는 실행계획(Execution Plan)을 세우고 문장을 실행하기 전에 가장 효과적인 계획을 선택합니다.

◎ Optimizer는 SQL문을 위한 실행 계획의 표현은 규칙 기반 접근법(Rule-based approach) 또는 비용 기반 접근법(Cost-based approach) 중 하나를 사용합니다.

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

 

2. Optimizer Mode

Optimizer의 모드는 아래와 같은 방법으로 수정할 수 있습니다.

◎ 초기화 파라미터 파일 수정

-. OPTIMIZATION_MODE=COST 또는 OPTIMIZATION_MODE=RULE

◎ ALTER 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 이 보다 적은 결과값이 나올것으로 Optimizer는 예상하기 때문에, empno = 8888 부분을 먼저 실행시킵니다.

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

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

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

 

[수행단계]

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

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

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

 

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

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

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

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

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

 

[수행단계]

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

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

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

 

 

[Oracle Optimizer goal - Choose, Rule, First rows, All rows]

■ Choose

-. 테이블에 통계정보가 있으면 CBO/All_rows, 없으면 RBO로 하겠다는 뜻

 

■ Rule

-. RBO로 하겠다는 뜻.

 

■ First rows

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

-. response time 최소화를 위한 optimizing을 한다.

-. index scan을 선호하는 경향이 있다.

-. index scan을 선호하는 경향이 있기에 작은 테이블로부터 데이터를 찾을 때도 index scan을 해서 full table scan을 하는 것보다 cost가 더 걸리는 단점이 있다.

-. user interaction 즉, 화면계에 사용하면 좋다.

 

■ All rows

-. CBO로 하겠다는 것인데, 보다 구체적으로 throughput을 OPTIMIZER GOAL로 합니다.

   여기서 throughput을 '목표'로 한다는 의미는 전체 SQL문을 수행하는데, 최소의 자원을 소비하는 것을 '목표'로 한다는 뜻입니다.

-. throughput 최대화를 위한 optimizing을 한다.

-. full table scan을 선호하는 경향이 있다.

-. batch 프로그램 또는 report 출력 프로그램에 사용되는게 좋다.

■ FIRST_ROWS_N

-. Oracle 9i부터 도입된 파라미터.

-. FIRST_ROWS의 단점을 보완했다.

   단점이란, FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000처럼 FIRST_ROWS의 범위를 지정하도록 함으로써 index scan을 해야하는지, full table scan을 해야하는지에 대한 선택을 더 현명하게 하도록 했다는 것이다.