OPTIMIZER_MODE 의 개념#
초기화 파라미터인 OPTIMIZER_MODE는 오라클 인스턴스가 최적의 Access 경로를 선택하는 기본 설정이 된다. 즉 옵티마이저가 어떠한 기준으로 최적화된 Access 경로를 추출 하는지 결정하는 방법에 대해 제시 한다고 볼 수 있다.
옵티마이저(Optimizer) : #
- Rule-based – SQL에 대한 Execution Plan이 여러 개 있다고 할 때, 가장 낮은 순위의 실행계획을 항상 사용
- Cost-based – 가장 cost가 적은 실행계획을 선택
실행환경#
- CHOOSE : Optimizer가 CBO와 RBO 두 가지가 모두 선택 가능할 경우에 사용
- ALL_ROWS : CBO 환경경에서 전체적인 처리, 즉 Batch 환경에서 주로 사용
- FIRST_ROWS : CBO 환경에서 빠른 응답시간을 얻고자 할 때, 즉 ON-LINE 환경에서 주로 사용
- RULE : RBO 환경에서 사용
| MODE | 장점 | 단점 |
|---|---|---|
| RULE | Analyze작업이 불필요, 일정한 응답속도를 기대 |
전문가에게 의존도가 매우 높음, 옵티마이저 대부분의 기능을 사용 못함 |
| CHOOSE | 옵티마이저 기능을 최대한 활용 | 주기적인 Analyze작업이 필요, 비효율적인 실행계획수립 발생 |
| FIRST_ROWS | Nested Loop 위주의 실행계획수립 | 일부 Hash Join으로 바꾸는 작업 필요 |
| ALL_ROWS | Hash Join 위주의 실행계획 수립 | 일부를 Nested Loop로 바꾸는 작업 필요 |
Rule-Based Optimizer (규칙기반 최적화)#
- RBO는 규칙에 의해서 실행되므로 예측가능하고 통제 가능하므로 대부분의 DBA, 프로그래머들이 선호
- RBO는 구문 중심으로, SQL 구문을 변경할 경우 성능이 향상될 수 있음(통계정보, 비용계산 비사용). 오직 규칙에 의해서만 실행계획을 결정
- 테이블의 row수가 작거나 query가 일반적으로 row의 대부분을 반활할 때에도 가능하면, 항상 인덱스를 사용함으로써 비효율적이 될 수도 있음. 테이블명에 대한 파싱은 우에서 좌로 진행되기 때문에 최후 기술된 테이블이 먼저 처리
RBO 순위표(규칙)#
- 문장의 WHERE절에 ROWID나 Oracle Precompiler에 지원되는 확장된 SQL 구문 구조의 CURRENT OF CURSOR에 의해 지정된 ROW들이 식별될 수 있을 경우에만 사용이 가능
- 문장의 WHERE 절에 join이 단지 하나의 row를 return하는 것을 보증하는 조건절을 가지고 있어야 함. 이러한 조건절은 unique하거나 primary key인 칼럼의 ‘=’ 조건절과 유사함. 이러한 조건들은 반드시 AND 연산자를 통해 합쳐져야 하고, 이러한 문장을 수행하기 위해 Oracle은 nested loops 연산을 수행
- WHERE 절이 ‘=’조건절에 HASH CLUSTER KEY의 모든 칼럼을 사용하여야 하며, 합성된 CLUSTER KEYS인 경우에는 ‘=’ 조건절이 반드시 AND 연산자에 의해 연결되어야 함. 문장은 칼럼이 HASH CLUSTER KEY를 만들어 내듯이 UNIQUE나 PRIMARY KEY를 만들어내기 때문에 단지 하나의 ROW만을 RETURN해야 함
- WHERE절이 ‘=’ 조건절에서 칼럼들의 unique나 primary key를 사용할 경우에 사용
- 이 접근 경로는 두 Join 테이블이 같은 cluster에 저장되고, 문장의 Where절에 한 테이블의 각 칼럼에 대해 대응하는 테이블의 칼럼을 같게 하는 조건절을 포함하는 경우 사용함.
- WHERE절에 hash cluster key인 칼럼의 ‘=’조건절이 있을 경우에 사용
- WHERE절에 Index cluster key인 칼럼의 ‘=’조건절이 있을 경우에 사용
- WHERE절에 결합 INDEX의 칼럼이 있을 경우에 사용
- WHERE절에 single row index의 칼럼이 있을 경우에 사용
- index column이 bound range 된 것을 Where 조건에서 있을 경우에 사용
- index column이 unbound range 된 것을 Where조건에서 있을 경우에 사용
- where조건에 있는 칼럼들이 각각의 index로 구성되었을 경우에 사용
- MAX와 MIN함수의 argument는 칼럼, 상수, +연산자, ||연산자나 CONCAT함수 등을 포함하는 어떠한 수식도 올 수 있음. 단, select 절에 다른 수식이 오지 않아야 하며 문장은 WHERE절이나 GROUP BY절이 없어야 함.
- index의 칼럼이 order by절에서 이용할 경우에 사용
- 마지막으로 테이블 전체를 읽게 됨
Cost-Based Optimizer (비용기반 최적화)#
- 이론적으로 RBO에 비해 진보됨, 전문지식이 없어도 악성계획의 회피 가능
- 논리적인 한계성이 존재, 원하는 경로로 유도하기 어려운 단점
CBO 환경 설정 Parameter#
- Optimizer Mode = CHOOSE : 비용 기반 옵티마이저 환경을 의미
- ALL_ROWS : 비용 기반 옵티마이저 환경을 의미하며, SQL문의 WHERE 조건을 만족하는 모든 행을 가장 빠르게 검색하는 실행 계획을 결정함
- FIRST_ROWS : SQL문의 WHERE 조건을 만족하는 첫 번째 행을 가장 빠르게 검색하는 실행계획을 결정함
- FIRST_ROWS_10 : ~~ 10개 행을
- FIRST_ROWS_100 : ~~ 100개 행을
- FIRST_ROWS_1000 : ~~ 1000개 행을
CBO 특징#
- 좌에서 우로 파싱되므로 최초 기술된 테이블이 먼저 처리됨
- ANALYZE 명령은 과부하를 초래하기 때문에 항상 최선은 아님
- CHOOSE Mode에서 SQL문에 포함된 테이블 중 어느 하나라도 이미 분석된 상태라면, 그 SQL문은 기본적으로 CBO(비용기반 최적화)에 맞춰지게 됨
Optimizer Modes 설정 방법#
- Optimizer Mode = ( CHOOSE, ALL_ROWS, FIRST_ROWS, RULE
- 시스템 전체에 지정하기 (instance level) SQL> SET AUTOTRACE TRACE EXPLAIN
- 세션에서 지정 (Seesion level) ALTER SESSION SET OPTIMIZE_MODE = ( CHOOSEM ALL_ROWS, FIRST_ROWS, RULE )
- SQL문장에서 지정 (Statement level)
- SELECT /*+ (ALL_ROWS, FIRST_ROWS, RULE) * /
/*+ CHOOSE */ |
- Hint를 사용하지 않는 경우 Session 혹은 System 전체에 정해진 Rule로 실행계획을 생성한 상태
옵티마이저를 이용한 SQL 튜닝#
인덱스 정보 이용법#
SELECT DISTINCT B.CLASS, COUNT(B.CLASS) // DISTINCT는 아래의 GROUP BY에 의해 의미가 없음 |
CHOOSE Mode와 CBO#
- 조합된 테이블 중 어느 하나라도 이미 분석된 상태라면, 기본적으로 CBO로 작동함
- SELECT /** RULE **/ ROUND (NUV(SUM(C.REQR_QTY Z.MIN_COMP_QTY * …
비용 기반 옵티마이저를 이용한 sql튜닝#
- 아무런 인덱스가 없는 테이블 EMP의 znalyze 되기 전의 실행계획
SELECT ename |
현재는 아무런 인덱스가 없으며 분석되지 않았으며, 비용기반이 아님
- 테이블 emp만 analyze 되었을 경우의 실행계획
SQL> ANALYZE TABLE emp COMPUTE STATISTICS; |
분석 이후 Cost, Card, Byte등이 출력됨. (Cost가 큰 수일수록 비효율적임)
- 테이블 emp의 칼럼 deptno에 대해서 인덱스 생성시의 실행계획
SQL> CREATE INDEX EMP_DEMPTNO_IDX ON EMP(DEPTNO); |
초기의 COST보다 18이 줄은 값이 나옴 인덱스 사용이 미사용보다 COST가 높았다면 인덱스를 사용하지 않았을 것임
예제#
SQL>conn / as sysdba |
사용된 optimizer mode 알아내기#
select sql_text, optimizer_mode, optimizer_cost, parsing_schema_name |