ORACLE|Optimizer Mode (옵티마이저 모드)

Optimizer Mode

OPTIMIZER MODE는 샤워기에 비유 할 수 있다.
더운 여름날에는 차가운 물로 손을 씻을 수 있고, 추운 겨울에는 따뜻한 물(혹은 뜨거운 물)로 손을 씻을 수 있다. 
OPTIMIZER 또한 샤워기처럼 상황에 맞는 모드의 선택이 필요하다. 

• 정의 : optimizer가 어떠한 기준으로 최적화된 Access 경로 검색 결정법에 대해 제시
  1. CHOOSE : 통계 정보가 있는 경우 CBO로 운영하고, 통계 정보가 없을 경우 RBO로 동작하라는 의미 (9i default value)
  2. ALL_ROWS : "테이블 안에 있는 전체 데이터를 검색해서 전체의 합이나 평균을 구하겠다"할 경우에 사용.
           주로 OLAP(Online Analytical Processing)에 적당 (10g default value)
  3. Rule : SQL문에 대한 실행계획이 여러 개 있다고 할 때, 가장 낮은 순위의 실행계획을 항상 사용하는 RBO 환경에서 사용
  4. FIRST_ROWS(FIRST_ROWS_N) : 일부 데이터를 보여주는데 최적화된 모드.
           SQL문의 WHERE 조건을 만족하는 첫 번째 행(혹은 1,10,100,1000 행)을 가장 빠르게 검색하는 실행계획을 결정.
           주로 OLTP(Online Transaction Processing)에 적당
     • FIRST_ROWS_1 : 1개 행을
     • FIRST_ROWS_10 : ~~ 10개 행을
     • FIRST_ROWS_100 : ~~ 100개 행을
     • FIRST_ROWS_1000 : ~~ 1000개 행을

       MODE	장점	단점
       RULE	Analyze작업이 불필요, 일정한 응답속도를 기대	전문가에게 의존도가 매우 높음, optimizer 대부분의 기능을 사용 못함
       CHOOSE	optimizer 기능을 최대한 활용	주기적인 Analyze작업이 필요, 비효율적인 실행계획수립 발생
       FIRST_ROWS	Nested Loop 위주의 실행계획수립	일부 Hash Join으로 바꾸는 작업 필요
       ALL_ROWS	Hash Join 위주의 실행계획 수립	일부를 Nested Loop로 바꾸는 작업 필요

       
       
       

• Optimizer Mode 설정 방법
  1. 시스템 전체 지정(instance level) : initSID.ora에서 설정. 변경시에는 DB를 재시작.

     OPTIMIZER MODE = { RULL / CHOOSE / FIRST_ROWS / ALL_ROWS }

  2. 세션 지정 (Seesion level)

     ALTER SESSION SET OPTIMIZE_MODE = ( CHOOSE, ALL_ROWS, FIRST_ROWS, RULE )

  3. SQL문장 지정 (Statement level)

     SELECT /*+ (ALL_ROWS, FIRST_ROWS, RULE, FIRST_ROWS[(n)]) * / ~~~ FROM ~~~

  4. 우선순위 : Statement > Session > Instance
  5. 현재 설정된 optimizer mode 확인

     SELECT NAME, VALUE, ISDEFAULT, ISMODIFIED, DESCRIPTION
     FROM V$PARAMETER
     WHERE NAME LIKE '%optimizer_mode%';

     
     
     

• EXPORT와 OPTIMIZER MODE(9i 이전)
  1. 문제(BUG# 391656) : EXPORT시 5 분 걸리던 작업이 init.ora 에 optimizer mode=first_rows로 하면 20 시간이 걸리는 현상이 발생 (export시 rows=n 으로 주어도 export time이 굉장히 오래 걸림)
  2. 원인 : event 10046을 setting후 각 statement path 확인해보니 optimizer 가 CDEF$ 와 COL$ table 을 full scan 함. 만일 DB 에 constraint가 많고 table comment 가 많다면 많은 시간이 걸려서 이러한 결과가 나타남.
  3. 이 경우는 CDEF$ 와 COL$를 analyze 한다 하더라도 별 도움이 안되고, optimizer_mode=first_rows 를 사용하지 말아야 함

문서에 대하여

• 최초작성자 : 박혜은
• 최초작성일 : 2009년 11월 19일
• 이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.

문서정보

• 이 문서는 오라클클럽에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://www.gurubee.net/display/DBSTUDY/OPTIMIZER_MODE?
• 오라클클럽 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.