Optimizer Mode
OPTIMIZER MODE는 샤워기에 비유 할 수 있다.
더운 여름날에는 차가운 물로 손을 씻을 수 있고, 추운 겨울에는 따뜻한 물(혹은 뜨거운 물)로 손을 씻을 수 있다.
OPTIMIZER 또한 샤워기처럼 상황에 맞는 모드의 선택이 필요하다.
- 정의 : optimizer가 어떠한 기준으로 최적화된 Access 경로 검색 결정법에 대해 제시
- CHOOSE : 통계 정보가 있는 경우 CBO로 운영하고, 통계 정보가 없을 경우 RBO로 동작하라는 의미 (9i default value)
- ALL_ROWS : "테이블 안에 있는 전체 데이터를 검색해서 전체의 합이나 평균을 구하겠다"할 경우에 사용.
주로 OLAP(Online Analytical Processing)에 적당 (10g default value) - Rule : SQL문에 대한 실행계획이 여러 개 있다고 할 때, 가장 낮은 순위의 실행계획을 항상 사용하는 RBO 환경에서 사용
- 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 설정 방법
- 시스템 전체 지정(instance level) : initSID.ora에서 설정. 변경시에는 DB를 재시작.
OPTIMIZER MODE = { RULL / CHOOSE / FIRST_ROWS / ALL_ROWS }
-# 세션 지정 (Seesion level)
ALTER SESSION SET OPTIMIZE_MODE = ( CHOOSE, ALL_ROWS, FIRST_ROWS, RULE )
-# SQL문장 지정 (Statement level)
SELECT /*+ (ALL_ROWS, FIRST_ROWS, RULE, FIRST_ROWS[(n)]) * / ~~~ FROM ~~~
-# 우선순위 : Statement > Session > Instance
-# 현재 설정된 optimizer mode 확인
SELECT NAME, VALUE, ISDEFAULT, ISMODIFIED, DESCRIPTION
FROM V$PARAMETER
WHERE NAME LIKE '%optimizer_mode%';
- EXPORT와 OPTIMIZER MODE(9i 이전)
- 문제(BUG# 391656) : EXPORT시 5 분 걸리던 작업이 init.ora 에 optimizer mode=first_rows로 하면 20 시간이 걸리는 현상이 발생 (export시 rows=n 으로 주어도 export time이 굉장히 오래 걸림)
- 원인 : event 10046을 setting후 각 statement path 확인해보니 optimizer 가 CDEF$ 와 COL$ table 을 full scan 함. 만일 DB 에 constraint가 많고 table comment 가 많다면 많은 시간이 걸려서 이러한 결과가 나타남.
- 이 경우는 CDEF$ 와 COL$를 analyze 한다 하더라도 별 도움이 안되고, optimizer_mode=first_rows 를 사용하지 말아야 함
문서에 대하여
- 최초작성자 : 박혜은
- 최초작성일 : 2009년 11월 19일
- 이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.