오라클 파라미터 OPTIMIZER_MODE, 세팅방법, RBO, CBO, 옵티마이저모드
초기화 파라미터인 OPTIMIZER_MODE는 오라클 인스턴스가 최적의 Access 경로를 선택하는 기본 설정이 됩니다. 즉 옵티마이저가 어떠한 기준으로 최적화된 Access 경로를 추출 하는지 결정하는 방법에 대해 제시 한다고 볼 수 있습니다. 아래에 설정 가능한 값과 그에 대한 설명이 있으니 참고 바랍니다.
CHOOSE : 오라클 옵티마이저는 CBO(Cost-Based Optimizer)와 RBO(Rule-Based Optimizer)중 하나를 선택 합니다. 통계 정보의 이용가능 여부에 따라 이용 가능하면 CBO를 사용하고 불가능 한경우에는 RBO를 사용 합니다
만약 데이터딕셔너리에 액세스 되는 테이블 중 적어도 하나의 테이블에 대한 통계 정보가 있는 경우엔 CBO를 사용하며 최대 처리량을 목표로 최적화를 수행 합니다.
딕셔너리에 약간의 통계 정보만을 가지고 있다면 CBO가 사용 될 수 있지만 옵티마이저는 통계 정보가 없는 부분에 대해서는 통계 정보를 추측해야 합니다. 이러한 것은 최선의 실행 계획이라기 보다 차선의 실행 계획이라고 볼 수 있습니다.
만약 Access되는 테이블에 대한 통계 정보가 하나도 없다면 RBO를 사용 합니다.
ORACLE 11g에서는 한물간 방식이지만 지원은 하고 있으며 오라클 11gR2에서는 문서로는 지원 안하지만 여전히 사용은 가능한 방식입니다.
ALL_ROWS : 통계 정보의 유무와 상관없이 모든 SQL 문에 대해 CBO를 이용하며 최대 처리량을 목표로 합니다. 최대 처리량을 목표로 한다는 것은 전체 SQL문의 결과를 완료 하는데 걸리는 시간이 적게 드는 것을 목표로 한다는 것 입니다. . (Oracle11g의 기본값으로 테이블에 대한 통계정보가 없더라도 CBO로 동작된다. )
FIRST_ROWS_n : 통계 정보의 유무와 상관없이 모든 SQL 문에 대해 CBO를 이용하며 처음 n개의 레코드를 가장 빨리 추출 할 수 있는 최적의 경로를 찾습니다.
FIRST_ROWS : 옵티마이저는 첫번째 레코드의 추출을 가장 빠르게 하는 최적의 경로를 찾습니다.
RULE : 통계정보의 유무와 관계 없이 모든 SQL에 대해 RBO를 사용하도록 합니다. 지금은 잘 사용하지 않는 방식이지만 아직 사용은 가능하다. 아직 이 방식을 사용하고 있다면 빨리 CBO형태로 개선이 필요합니다.
초기 매개 변수 파일(init.ora)에서 위의 파라미터를 변경 할 수도 있으며 alter session set optimizer_mode 명령을 이용 할 수도 있습니다. 또한 명령 행에서 Hint를 이용하여 Optimizer Mode나 목표를 변경 하는 것도 가능 합니다.
/*+ CHOOSE */
/*+ RULE */
/*+ ALL_ROWS */
/*+ FIRST_ROWS[(n)] */
결국 힌트는 개별 SQL문에 대해 CBO의 목표를 설정하기 위해 사용하는 것이다. 각 SQL문에서 힌트를 사용하게 되면 초기 파라미터에서 설정 한 것 보다 우선 적용 됩니다.
[예]
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
2 ;
세션이 변경되었습니다.
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
optimizer_mode string FIRST_ROWS_1
CBO가 사용하는 통계 정보는 데이터 딕셔너리에 저장되며 DBMS_STATS 패키지나 ANALYZE 명령을 이용하여 스키마 객체에 대한 데이터의 분포도 등을 구할 수 있습니다. SQL문을 구사하면서 최적화된 CBO의 사용을 위해서는 반드시 통계 정보를 가져야 합니다.
Optimizer Mode Setting 방법
--------------------------
1. Instance 수준의 셋팅 방법
--------------------------
- DB의 설정 파일(initSID.ora or spfileSID.ora)에 전체적으로 적용이 되도록 정의하는 방법, OPTIMIZER MODE는 RULE, CHOOSE, ALL_ROOWS, FIRST_ROWS와 같은 종류가 있습니다. CHOOSE인 경우 한 테이블이라도 Analyzed되어 있는 경우엔 비용기반 접근(CBO) 방식을 이용하는 것이며 RULE인 경우 규칙기반 접근 방식을 사용, ALL_ROWS인 경우 비용기반 옵티마이저의 한 방법인데 모든 ROW들을 처리한다고 할 때 그 비용을 최소화 하는 방법으로 실행계획을 수립, FIRST_ROWS인 경우엔 최초 ROW를 추출하는데 드는 비용을 최소화 하도록 실행 계획을 구성하는 것입니다.
- 예) OPTIMIZER_MODE=FIRST_ROWS
- 만약 initSID or spfileSID.ora에 아무 내용도 정의하지 않은 경우 기본적으로 CHOOSE 방식이 됩니다.
-------------------------
2. Session 수준의 셋팅 방법
-------------------------
- alter session이라는 명령을 이용하면 현재 접속된 세션 레벨에서 옵티마이저 모드를 정의할 수 있습니다.
- 예) alter session set optimizer_mode=rule 과 같이 정의하면 해당 세션이 끝나기 전까지는 규칙 기반(RULE-BASED) 옵티마이저 모드를 이용하게 됩니다.
--------------------------
3. Statement 수준의 셋팅 방법
--------------------------
- 힌트(Hint) 구문을 이용한다면 매 SQL 문장마다 서로 다른 옵티마이저 모드를 적용할 수 있습니다.
- 예) SELECT /*+ FIRST_ROWS */
ENAME,
SAL,
JOB
FROM EMP
WHERE SAL > (SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 10)
규칙 기반 옵티마이저(Rule-Based Optimizer)
오라클이 규칙 기반 옵티마이저로 동작하는 경우는 initSID.ora 파일에 OPTIMIZER_MODE=RULE or OPTIMIZER_MODE=CHOOSE 이면서 테이블이나 인덱스에 대한 통계 정보가 없는 경우(11g이후 CBO로 동작) 인데 DW(Data WareHouse)에서는 비용 기반 옵티마이저(Cost-Based Optimizer)가 파티션테이블, 구체화 뷰 등을 지원하므로 규칙기반 보다는 비용 기반 옵티마이저가 더 효율적 입니다.
물론 OPTIMIZER_MODE=FIRST_ROWS or FIRST_ROWS_n or ALL_ROWS라고 한다면 통계 정보의 유무와 관계없이 비용 기반 옵티마이저를 이용하며 OPTIMIZER_MODE=CHOOSE인 경우 테이블이나 인덱스의 통계 정보가 있다면(Oracle11g이후에는 DBO로 동작) default로 ALL_ROWS로 동적 합니다.
참고로 Oracle11g 이후 RULE, CHOOSE Mode는 더 이상 지원하지 않으며 RULE BASED로 동작하기 위해서는 쿼리 문에 직접 RULE 힌트를 사용해야 합니다.
-----------------------------------------------------------
규칙 기반 옵티마이저의 우선 순위
-----------------------------------------------------------
rowid에 의한 단일 행 검색이 우선 순위가 가장 높습니다.
1. rowid에 의한 single row access
SQL>select empno, ename from emp where rowed = ‘AAA78BBBBBBBBB’;
2. cluster join에 의한 single row access
같은 클러스터 내에 있는 테이블을 조인할 때 이용, 아래 예는 EMP 테이블과 DEPT 테이블이 dept_no에 의해 cluster 되어 있으며 empno 컬럼이 EMP 테이블의 pk 인 경우의 예입니다.
SQL> select ename, dname, sal
from emp e, dept d
where e.deptno = d.deptno
and e.empno = 8888;
Execution Plan
------------------------------------------
SELECT STATEMENT
NESTED LOOP
TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’
INDEX (UNIQUE SCAN) OF ‘pk_emp’ (UNIQUE)
TABLE ACCESS (BY CLUSTER) OF ‘DEPT’
3. unique key를 가지는 hash cluster에 의한 single row access
where절이 = 비교이며 hash cluster key를 가지며 단일 로우를 추출하는 경우에
사용되는 됩니다.
4. unique index에 의한 single row access
아래에서 EMP 테이블의 PK는 EMP_NO
SQL> select ename, sal
from emp
where empno = 8000;
5. cluster join
6. hash cluster key
7. indexed cluster key
8. composite index
EMP 테이블에서 JOB과 SAL에 복합 인덱스가 있는 경우
SQL>select ename
from emp
where job = ‘SALES’
and sal = 8000;
9. non unique index (single column)
EMP 테이블에서 JOB에 인덱스가 있는 경우
SQL>select ename
from emp
where job = ‘SALES’
10. bounded range search on indexed column(인덱스 컬럼을 이용한 제한된 범위 검색)
EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우
SQL>select ename, sal
from emp
where sal > 1000
and sal < 5000;
11. unbounded range search on indexed column(인덱스 컬럼에 의한 제한되지 않은 범위 검색)
EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우
SQL>select ename, sal
from emp
where sal > 1000;
12. sort merge join
13. index column의 MIN or MAX
EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우
SQL>select MAX(SAL)
from emp;
14. index column에 대한 order by
15. full table scan
이상 규칙 기반 옵티마이저의 우선 순위에 대해 살펴 보았는데 규칙 기반 옵티마이저인 경우엔 실행 계획 수립시 번호가 낮은 것을 먼저 검토하여 적용가능한지 파악하며 한 단계식 높은 번호를 수행 합니다. 즉 낮은 번호 일수록 높은 우선 순위를 가지는데 예를 들어 다음과 같은 Query가 있다고 가정할 때….
SQL>select ename, job from emp where ename = ‘홍길동’;
(ename에 인덱스가 걸려 있다고 할 때)
데이터가 몇건 되지 않는다고 할 때 규칙 기반 옵티마이저인 경우 인덱스를 이용하는 것이 FULL SCAN하는 것보다 우선 순위가 높으므로 무조건 인덱스를 경유하여 EMP TABLE을 rowid에 의해 access 하는 실행계획을 세울 겁니다, 그러나 사실 이 테이블의 경우엔 데이터가 몇건 안되니까 한번에 FULL SCAN하여 가지고 오는 것이 훨씬 빠른데도 말입니다.
이 규칙 기반 옵티마이저의 경우 무조건 규칙에 위해 실행 계획을 만들어 내므로 융통성이 없는 것이 단점이며 그래서 최근엔 CBO를 많이 사용하고 있는 추세 입니다.
비용기반 옵티마이저(Cost-Based Optimizer)
비용기반 옵티마이저(CBO)는 실제로 가장 빠르게 실행되는 방법의 Cost를 계산하여 실행 경로를 선택하는 방법인데 DB 의 버전이 높아질수록 더욱 정교하게 발전되고 있는 방법이며 오라클에서 권장 하고 있습니다.
하지만 옵티마이저가 가장 최선의 실행 경로를 찾기 위해서는 ANALYZE 명령으로 테이블의 크기, 레코드 수, 컬럼 값의 분포도와 같은 부가적인 정보를 제공해 주어야 정상적으로 잘 동작한다고 할 수 있는데 테이블이나 인덱스에 통계 정보가 있는지에 대한 여부는 DBA_TABLES, DBA_INDEXED와 같은 뷰의 last_analyzed 컬럼의 값을 통해 알 수 있으며 NULL 값을 가지는 경우에는 통계 정보가 없다고 보시면 됩니다. 이 시각이 아주 오래전이라 현재 테이블이나 인덱스와 생성되어 있는 통계정보가 일치하지 않는다면 정확한 비용기반의 실행 계획을 만들기는 어려울 것입니다.
대부분의 SQL 튜닝의 경우 비용 기반 옵티마이저를 기본으로 하고 있으므로 비용기반 옵티마이저를 모르고는 튜닝이 어렵습니다.
[비용기반 옵티마이저로 동작하기 위한 방법]
통계정보가 생성되어 있으며 설정 파라미터가 아래와 같이 설정 되어 있는 경우에 비용기반 옵티마이저로 동작 합니다.
initSID.ora or spfileSID.ora 파일의 OPTIMIZER_MODE=ALL_ROWS
initSID.ora or spfileSID.ora 파일의 OPTIMIZER_MODE=FIRST_ROWS
initSID.ora or spfileSID.ora 파일의 OPTIMIZER_MODE=CHOOSE
CHOOSE로 설정 되어 있는 경우에는 통계정보가 없다면 규칙기반 옵티마이저, 있다면 ALL_ROWS로 동작 합니다.
