\* 옵티마이저에는 다음의 두 가지 형태가 있음
\- 규칙기준 옵티마이저(RBO)
\- 비용기준 옵티마이저(CBO)
\- RBO는 다음의 우선 순위에 따라 연산을 수행한다.
1) ROWID로 1로우 엑세스
2) 클러스터 조인에 의한 1로우 엑세스
3) Unique HASH Cluster에 의한 1로우 엑세스
4) Unique INDEX에 의한 1로우 엑세스
5) CLUSTER 조인
6) Non Unique HASH Cluster Key
7) Non Unique Cluster Key
8) Non Unique 결합 인덱스
9) Non Unique 한 컬럼 인덱스
10) 인덱스에 의한 범위 처리
11) 인덱스에 의한 전체 범위 처리
12) Sort Merge Join
13) 인덱스 컬럼의 MIN, MAX 처리
14) 인덱스 컬럼의 ORDER BY
15) 전체 테이블 스캔
가) 규칙기준 옵티마이저의 단점
| SELECT * FROM EMP e, DEPT d WHERE e.deptno = d.deptno |
|---|
\- Query 2
| SELECT * FROM DEPT d, EMP e WHERE d.deptno = e.deptno |
|---|
예2) 분포도를 고려하지 않으므로 인덱스 선정 시 판단 착오가 있을 수 있다.
\* RBO는 Equal(=) 연산을 Range연산보다 우위에 둔다. 분포도를 고려한다면 Range연산 쪽이 효율이 더 좋을 수도 있다. 그러나 RBO는 분포도를 알지 못한다.
\- Query
| SELECT * FROM emp WHERE A='10' and B LIKE '123%' |
|---|
B like '123%' 이 A='10' 보다 분포도가 더 좋을 수가 있다. 그러나 RBO는 알지 못하므로 무조건 후자의 방법인 A='10'을 택한다.
나) 규칙기준 옵티마이저의 장점
\- CBO: 관계형 데이터베이스가 추구하는 이상형이며, 대부분의 관계형 데이터베이스 제품은 CBO만 보유하고 있음. 처리방법들에 대한 비용을 산정 후, 가장 적은 비용이 들어가는 처리방법을 선택함.
\- 통계정보에는 다음과 같은 많은 정보가 있다
테이블의 로우 수, 블록 수, 블록 당 평균 로우 수, 로우의 평균 길이, 컬럼별 상수 값의 종류, 분포도, 컬럼 내 NULL 값 수, 클러스터링 팩터, 인덱스의 깊이, 최대-최소값, 리프 블록 수 등
가) 비용기준 옵티마이저의 장점
나) 비용기준 옵티마이저의 단점
라) 통계정보 관리를 위한 제언
\- 효과적인 통계정보의 수집과 관리를 위해 DBMS_STATS 패키지 이용
3.1.2.3. 옵티마이저 목표(Goal)의 선택
가) 옵티마이저 모드의 종류
나) 옵티마이저 모드의 결정 기준
다) 옵티마이저 모드와 관련된 파라미터 지정
- 테스트 환경: Windows 2003, Oracle 10g R2(10.2.0.3)
\ DB_FILE_MULTIBLOCK_READ_COUNT*
ALL_ROWS
DB_FILE_MULTIBLOCK_READ_COUNT=16
OPTIMIZER_INDEX_COST_ADJ=100
쿼리: select * from test_tab2 a where col1 = :a1;
* Ful Scan 쿼리: select /*\+ FULL(a) \*/ * from test_tab2 a where col1 = :a1;
\
- DB_FILE_MULTIBLOCK_READ_COUNT 갑에 따른 Cost 변화는? | ||||
| 파라미터 값 | Full Scan 비용 | Index Scan 비용 | 수행 방식 | |
|---|---|---|---|---|
| 1 | 2067 | 466 | Index Scan | |
| 2 | 1231 | 466 | Index Scan | |
| 3 | 821 | 466 | Index Scan | |
| 4 | 603 | 466 | Index Scan | |
| 16 | 499 | 466 | Index Scan | |
| 32 | 447 | 466 | Full Scan | |
| 64 | 420 | 466 | Full Scan | |
| 128 | 407 | 466 | Full Scan | - 해당 파라미터의 값에 따라서 Full Scan 비용이 달라짐. 이이 대한 결정 기준은? 결정하는 요소는 테이블의 전체 블록 갯수(DBA_TABLE의 BLOCKS 컬럼 |
| SQL> select blocks from USER_TABLES where table_name='TEST_TAB2'; BLOCKS \ -\- 2008 | 파라미터 값이 1일 때, Full Table Scan의 비용과 통계 상의 Block 갯수와 거의 동일 이 값을 기반으로 다른 값일 때 비용이 정해짐. \ OPTIMIZER_INDEX_COST_ADJ* ( 가정: DB_FILE_MULTIBLOCK_READ_COUNT 값은 32) | |||
| 파라미터 값 | Full Scan 비용 | Index Scan 비용 | 수행 방식 | |
| 200 | 447 | 932 | Full Scan | |
| 100 | 447 | 466 | Full Scan | |
| 50 | 447 | 233 | Index Scan | |
| 25 | 447 | 116 | Index Scan | |
N |
\- 파라미터 값이 50인 경우 실행 게획
3.1.2.4. 실행계획의 고정화
\- 아우트 라인 이란? 요약본이라는 의미를 가지며, 쉽게 변할 수 있는 쿼리의 실행 계획을 아우트 라인을 참조하게 만들어 변하지 않게 한다.
아우트 라인은 통계 자료의 갱신이나 DBMS Upgrade 등으로 쉽게 변할 수 있다. 이 경우 세션 레벨 또는 시스템 전체적으로 이미 만들어진 아우트 라인을 참조하여 변경을 막을수 있다.
가) 아우트 라인 생성과 조정
\- DBMS_OUTLN, DBMS_OUTLN_EDIT 패키지에는 다음과 같은 프로시저로 OUTLINE 제어
CREATE_OUTLINEL 지정된 건을 공유캐시에서 찾아 아우트 라인을 생성한다.
CLEAR_USED: 지정된 아우트 라인을 제거한다.
DROP_BY_CAT: 지정한 카테고리에 속한 아우트 라인을 제거한다.
DROP_UNUSED: SQL 파싱에 사용된 적이 없는 아우트 라인을 제거한다.
UPDATE_BY_CAT: 어떤 카테고리를 새로운 카테고리로 변경한다.
GENERATE_SIGNATURE: 지정한 SQL 문에 대한 식별자를 생성한다.
나) 아우트 라인의 관찰
\- USER_OUTLINES: 생성되어 있는 아우트 라인에 대한 정보를 가지고 있다.
\- USER_OUTLINE_HINTS: 생성되어 있는 아우트 라인에 대한 힌트 정보를 가지고 있다.
예제) 수행 쿼리: select * from test_tab2 where col1 = 11
- USER_OUTLINE 정보
| NAME CATEGORY USED TIMESTAM \ -\--\--\--\--\--\--\- --\--\ -\--\--\--\--\--\--\- \-\--\--\- --\--\---\- VERSION \ ---\- SQL_TEXT SIGNATURE COMPATIBLE \ ---\--\--\--\--\--\--\- --\--\ --\- ENABLED FORMAT \-\--\--\--\- --\---\- SYS_OUTLINE_08030809063457720 DEFAULT01 UNUSED 08/03/08 10.2.0.3.0 select * from test_tab2 where col1 = :"SYS_B_0" 300EF8AD9364D515D939DFA4952CA69D COMPATIBLE ENABLED NORMAL |
- USER_OUTLINE_HINTS 정보
| NAME NODE STAGE JOIN_POS HINT \ -\--\--\--\--\--\--\- --\--\-\--\--\- --\--\-\--\--\- --\--\-\--\--\- --\--\ ---\- SYS_OUTLINE_08030809063457720 1 1 1 INDEX_RS_ASC(@"SEL$1" "TEST_TAB2"@"SEL$1" ("TEST_TAB2"")) SYS_OUTLINE_08030809063457720 1 1 0 OUTLINE_LEAF(@"SEL$1") SYS_OUTLINE_08030809063457720 1 1 0 ALL_ROWS SYS_OUTLINE_08030809063457720 1 1 0 OPT_PARAM('optimizer_index_caching' 50) SYS_OUTLINE_08030809063457720 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.3') SYS_OUTLINE_08030809063457720 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS |
다) 오라클 업그레이드 시에 적용
\- Outline 수집 방식
(1) 현재의 규칙기준으로 수행되는 SQL에 대하여 다음과 같이 특정 카테고리를 주고 아우트 라인을 생성한다.
ALTER SESSION SET CREATE_STORED_OUTLINES= OUTLN_TEST
이 때, ALTER SYSTEM 구문으로 하면 시스템 전역으로 적용이 가능하다.
(2) 대부분의 중요한 SQL에 대해 아우트 라인이 생성되도록 가능하다면 오랜기간 동안 수집하는 것이 좋다.
또는 월 단위 이상이나 특정한 기간에만 수행되는 어플리케이션은 별도의 처리를 해주는 것이 좋다.
(3) 아우트 라인 생성을 종료 시키려면 CREATE_STORED_OUTLINES 파라미터를 FALSE로 지정한다.
ALTER SESSION SET CREATE_STORED_OUTLINES= FALSE
\* RULE 방식에서 CBO 전환 적용 시, 이렇게 아우트 라인을 생성한 후, DBMS_STATS를 이용해 통계를 생성
UPGRADE 시 또한 생성 후 어플리케이션 테스트 수행
\* 이상 발생 시, USR_STORED_OUTLINES 를 이용해 복원
ALTER SESSION SET USE_STORED_OUTLINES = OUTLN_TEST
3.1.2.5. 옵티마이저의 한계규칙기준 옵티마이저(RBO)의 한계: 내부 통계 값을 고려하지 않고 예측하므로 효율적인 실행 계획이 만들어질 수 없음