OPTIMIZER_MODE 의 개념#

초기화 파라미터인 OPTIMIZER_MODE는 오라클 인스턴스가 최적의 Access 경로를 선택하는 기본 설정이 된다. 즉 옵티마이저가 어떠한 기준으로 최적화된 Access 경로를 추출 하는지 결정하는 방법에 대해 제시 한다고 볼 수 있다.

옵티마이저(Optimizer) : #

  1. Rule-based - SQL에 대한 Execution Plan이 여러 개 있다고 할 때, 가장 낮은 순위의 실행계획을 항상 사용
  2. Cost-based - 가장 cost가 적은 실행계획을 선택

실행환경#

  1. CHOOSE : Optimizer가 CBO와 RBO 두 가지가 모두 선택 가능할 경우에 사용
  2. ALL_ROWS : CBO 환경경에서 전체적인 처리, 즉 Batch 환경에서 주로 사용
  3. FIRST_ROWS : CBO 환경에서 빠른 응답시간을 얻고자 할 때, 즉 ON-LINE 환경에서 주로 사용
  4. RULE : RBO 환경에서 사용

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

Rule-Based Optimizer (규칙기반 최적화)#

  1. RBO는 규칙에 의해서 실행되므로 예측가능하고 통제 가능하므로 대부분의 DBA, 프로그래머들이 선호
  2. RBO는 구문 중심으로, SQL 구문을 변경할 경우 성능이 향상될 수 있음(통계정보, 비용계산 비사용). 오직 규칙에 의해서만 실행계획을 결정
  3. 테이블의 row수가 작거나 query가 일반적으로 row의 대부분을 반활할 때에도 가능하면, 항상 인덱스를 사용함으로써 비효율적이 될 수도 있음. 테이블명에 대한 파싱은 우에서 좌로 진행되기 때문에 최후 기술된 테이블이 먼저 처리

RBO 순위표(규칙)#

  1. 문장의 WHERE절에 ROWID나 Oracle Precompiler에 지원되는 확장된 SQL 구문 구조의 CURRENT OF CURSOR에 의해 지정된 ROW들이 식별될 수 있을 경우에만 사용이 가능
  2. 문장의 WHERE 절에 join이 단지 하나의 row를 return하는 것을 보증하는 조건절을 가지고 있어야 함. 이러한 조건절은 unique하거나 primary key인 칼럼의 '=' 조건절과 유사함. 이러한 조건들은 반드시 AND 연산자를 통해 합쳐져야 하고, 이러한 문장을 수행하기 위해 Oracle은 nested loops 연산을 수행
  3. WHERE 절이 '='조건절에 HASH CLUSTER KEY의 모든 칼럼을 사용하여야 하며, 합성된 CLUSTER KEYS인 경우에는 '=' 조건절이 반드시 AND 연산자에 의해 연결되어야 함. 문장은 칼럼이 HASH CLUSTER KEY를 만들어 내듯이 UNIQUE나 PRIMARY KEY를 만들어내기 때문에 단지 하나의 ROW만을 RETURN해야 함
  4. WHERE절이 '=' 조건절에서 칼럼들의 unique나 primary key를 사용할 경우에 사용
  5. 이 접근 경로는 두 Join 테이블이 같은 cluster에 저장되고, 문장의 Where절에 한 테이블의 각 칼럼에 대해 대응하는 테이블의 칼럼을 같게 하는 조건절을 포함하는 경우 사용함.
  6. WHERE절에 hash cluster key인 칼럼의 '='조건절이 있을 경우에 사용
  7. WHERE절에 Index cluster key인 칼럼의 '='조건절이 있을 경우에 사용
  8. WHERE절에 결합 INDEX의 칼럼이 있을 경우에 사용
  9. WHERE절에 single row index의 칼럼이 있을 경우에 사용
  10. index column이 bound range 된 것을 Where 조건에서 있을 경우에 사용
  11. index column이 unbound range 된 것을 Where조건에서 있을 경우에 사용
  12. where조건에 있는 칼럼들이 각각의 index로 구성되었을 경우에 사용
  13. MAX와 MIN함수의 argument는 칼럼, 상수, +연산자, ||연산자나 CONCAT함수 등을 포함하는 어떠한 수식도 올 수 있음. 단, select 절에 다른 수식이 오지 않아야 하며 문장은 WHERE절이나 GROUP BY절이 없어야 함.
  14. index의 칼럼이 order by절에서 이용할 경우에 사용
  15. 마지막으로 테이블 전체를 읽게 됨

Cost-Based Optimizer (비용기반 최적화)#

  1. 이론적으로 RBO에 비해 진보됨, 전문지식이 없어도 악성계획의 회피 가능
  2. 논리적인 한계성이 존재, 원하는 경로로 유도하기 어려운 단점

CBO 환경 설정 Parameter#

  1. Optimizer Mode = CHOOSE : 비용 기반 옵티마이저 환경을 의미
  2. ALL_ROWS : 비용 기반 옵티마이저 환경을 의미하며, SQL문의 WHERE 조건을 만족하는 모든 행을 가장 빠르게 검색하는 실행 계획을 결정함
  3. FIRST_ROWS : SQL문의 WHERE 조건을 만족하는 첫 번째 행을 가장 빠르게 검색하는 실행계획을 결정함
  4. FIRST_ROWS_10 : ~~ 10개 행을
  5. FIRST_ROWS_100 : ~~ 100개 행을
  6. FIRST_ROWS_1000 : ~~ 1000개 행을

CBO 특징#

  1. 좌에서 우로 파싱되므로 최초 기술된 테이블이 먼저 처리됨
  2. ANALYZE 명령은 과부하를 초래하기 때문에 항상 최선은 아님
  3. CHOOSE Mode에서 SQL문에 포함된 테이블 중 어느 하나라도 이미 분석된 상태라면, 그 SQL문은 기본적으로 CBO(비용기반 최적화)에 맞춰지게 됨

Optimizer Modes 설정 방법#

  1. Optimizer Mode = ( CHOOSE, ALL_ROWS, FIRST_ROWS, RULE
  2. 시스템 전체에 지정하기 (instance level) SQL> SET AUTOTRACE TRACE EXPLAIN
  3. 세션에서 지정 (Seesion level) ALTER SESSION SET OPTIMIZE_MODE = ( CHOOSEM ALL_ROWS, FIRST_ROWS, RULE )
  4. SQL문장에서 지정 (Statement level)
  • SELECT /*+ (ALL_ROWS, FIRST_ROWS, RULE) * /

/*+ CHOOSE */
/*+ RULE */
/*+ ALL_ROWS */
/*+ FIRST_ROWS[(n)] */

  • Hint를 사용하지 않는 경우 Session 혹은 System 전체에 정해진 Rule로 실행계획을 생성한 상태

옵티마이저를 이용한 SQL 튜닝#

인덱스 정보 이용법#

SELECT DISTINCT B.CLASS, COUNT(B.CLASS)   // DISTINCT는 아래의 GROUP BY에 의해 의미가 없음
FROM COL A, COM B
WHERE A.YEAR = B.YEAR
AND A.HOUSE = B. HOUSE
A.SERIAL_NO = B.SERIAL_NO
A.AMT >=100
B.DATE < '950501'
B.CLASS IN('C''Y''I' ,'P')
GROUP BY B.CLASS;

CHOOSE Mode와 CBO#

  1. 조합된 테이블 중 어느 하나라도 이미 분석된 상태라면, 기본적으로 CBO로 작동함
  2. SELECT /** RULE **/ ROUND (NUV(SUM(C.REQR_QTY Z.MIN_COMP_QTY * ...

비용 기반 옵티마이저를 이용한 sql튜닝#

  1. 아무런 인덱스가 없는 테이블 EMP의 znalyze 되기 전의 실행계획

SELECT ename
FROM emp
WHERE deptno = 20
AND empno BETWEEN 100 TO 200
ORDER BY ename;

Execution Plan
-----------------------------------------------------------------------
0          SELECT STATEMENT Optimizer = CHOOSE
1     0      SORT (ORDER BY)
2     1         TABLE ACCESS (FULLof 'EMP'
현재는 아무런 인덱스가 없으며 분석되지 않았으며, 비용기반이 아님

  1. 테이블 emp만 analyze 되었을 경우의 실행계획

SQL> ANALYZE TABLE emp COMPUTE STATISTICS;
분석 이후 Cost, Card, Byte등이 출력됨. (Cost가 큰 수일수록 비효율적임)

  1. 테이블 emp의 칼럼 deptno에 대해서 인덱스 생성시의 실행계획

SQL> CREATE INDEX EMP_DEMPTNO_IDX ON EMP(DEPTNO);
초기의 COST보다 18이 줄은 값이 나옴 인덱스 사용이 미사용보다 COST가 높았다면 인덱스를 사용하지 않았을 것임

예제#

SQL>conn / as sysdba
SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
optimizer_mode                       string      CHOOSE
SQL> alter session set optimizer_mode = first_rows_1;

세션이 변경되었습니다.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
optimizer_mode                       string      FIRST_ROWS_1

사용된 optimizer mode 알아내기#

select sql_text, optimizer_mode, optimizer_cost, parsing_schema_name 
from v$sql
where lower(sql_textlike 쿼리

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-7) was last changed on 05-Jan-2008 16:52 by DongGukLee