| {code} 1. 비용 모델 - I/O 비용 모델 : IO요청 횟수(논리적/물리적으로 읽은 블록 개수가 아님)만을 수행 비용으로 간주하여 실행계획 평가 - CPU 비용 모델 : IO요청 횟수에 시간을 더해 비용을 산정하여 실행계획 평가 2. 비용 모델 선택 파라미터 : _optimizer_code_model - IO : I/O 비용 모델 - CPU : CPU 비용 모델 - CHOOSE : 시스템 통계가 있으면 CPU 비용 모델, 없으면 I/O비용 모델 - 기본값 : CHOOSE - 9i에서는 시스템 통계를 생성해 줄 때만 CPU비용 모델 방식으로 작동한다. - 10g에서는 NoWorkLoad 시스템 통계를 도입하여 시스템 통계를 생성하지 않더라도 CPU비용 모델이 선택된다. 3. 쿼리 레벨 단위 비용 모델을 선택하는 힌트 - cpu_costing - no_cpu_costing {code} |
| {code} SQL> create table t 2 as 3 select * from all_objects; |
Table created.
SQL> create index t_owner_idx on t(owner);
Index created.
SQL> begin
2 dbms_stats.gather_table_stats(user, 'T'
3 , method_opt=>'for all columns size 1');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> alter session set "_optimizer_cost_model" = io;
Session altered.
SQL> set autotrace traceonly exp;
SQL> select /*+ index(t) */ * from t where owner = 'SYS';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | 2118 | 200K | 62 | |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 2118 | 200K | 62 |
| INDEX RANGE SCAN | T_OWNER_IDX | 2118 | 5 |
Predicate Information (identified by operation id):
2 - access("OWNER"='SYS')
Note
|
*[ I/O 비용 모델의 비용 계산식 ]*
|
비용 = blevel + -- 인덱스 수직적 탐색 비용
(리프 블록 수 * 유효 인덱스 선택도) + -- 인덱스 수평적 탐색 비용
(클러스터링 팩터 * 유효 테이블 선택도) -- 테이블 Random 액세스 비용
SQL> column index_name format a12
SQL> column clustering_factor format 999999 heading "CLUSTERING|_FACTOR "
SQL> column "인덱스 스캔 비용" format 9999.99999 heading "인덱스 |스캔비용"
SQL> column "총 테이블 액세스 비용" format 9999.99999 heading "총 테이블 |액세스 비용"
SQL> select i.blevel, i.leaf_blocks, c.num_distinct, i.clustering_factor
2 , 1 + (i.leaf_blocks * 1/c.num_distinct) "인덱스 스캔 비용"
3 , 1 + (i.leaf_blocks * 1/c.num_distinct)
4 + (i.clustering_factor * 1/c.num_distinct) "총 테이블 액세스 비용"
5 from user_indexes i, user_tab_col_statistics c
6 where i.index_name = 'T_OWNER_IDX'
7 and c.table_name = i.table_name
8 and c.column_name = 'OWNER';
CLUSTERING 인덱스 총 테이블
BLEVEL LEAF_BLOCKS NUM_DISTINCT _FACTOR 스캔 비용 액세스 비용
|
h2. 2. Full Scan에 의한 테이블 액세스 비용
- HWM 아래쪽 블록을 순차적으로 읽어들이는 과정에서 발생하는 I/O 호출 횟수로 비용을 계산한다.
- 한번의 I/O 호출로써 여러 블럭을 읽어들이는 multiblock I/O방식을 사용하여 총블럭수를 db_file_multiblock_read_count파라메터로 나눈 만큼 I/O호출이 발생한다.( 내부적으로 조정된 값으로 비용을 계산하기 때문에 예상치와 정확하게 일치하지 않음)
* Multiblock I/O단위 증가에 따른 예상비용
: db_file_multiblock_read_count 파라메터를 2부터 128까지 증가시키면서 I/O비용을 측정한 결과 cost가 계속 감소한다.
|
SQL> select blocks from user_tables where table_name = 'T';
BLOCKS
SQL> set autotrace traceonly exp;
SQL> alter session set db_file_multiblock_read_count = 2;
Session altered.
SQL> select /*+ full(t) */ * from t where owner = 'SYS';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | 2118 | 200K | 389 | |
| TABLE ACCESS FULL | T | 2118 | 200K | 389 |
Predicate Information (identified by operation id):
1 - filter("OWNER"='SYS')
Note
SQL> alter session set db_file_multiblock_read_count = 4;
Session altered.
SQL> select /*+ full(t) */ * from t where owner = 'SYS';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | 2118 | 200K | 247 | |
| TABLE ACCESS FULL | T | 2118 | 200K | 247 |
Predicate Information (identified by operation id):
1 - filter("OWNER"='SYS')
Note
SQL> alter session set db_file_multiblock_read_count = 8;
Session altered.
SQL> select /*+ full(t) */ * from t where owner = 'SYS';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | 2118 | 200K | 157 | |
| TABLE ACCESS FULL | T | 2118 | 200K | 157 |
Predicate Information (identified by operation id):
1 - filter("OWNER"='SYS')
Note
SQL> alter session set db_file_multiblock_read_count = 16;
Session altered.
SQL> select /*+ full(t) */ * from t where owner = 'SYS';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | 2118 | 200K | 100 | |
| TABLE ACCESS FULL | T | 2118 | 200K | 100 |
Predicate Information (identified by operation id):
1 - filter("OWNER"='SYS')
Note
SQL> alter session set db_file_multiblock_read_count = 32;
Session altered.
SQL> select /*+ full(t) */ * from t where owner = 'SYS';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | 2118 | 200K | 64 | |
| TABLE ACCESS FULL | T | 2118 | 200K | 64 |
Predicate Information (identified by operation id):
1 - filter("OWNER"='SYS')
Note
SQL> alter session set db_file_multiblock_read_count = 64;
Session altered.
SQL> select /*+ full(t) */ * from t where owner = 'SYS';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | 2118 | 200K | 41 | |
| TABLE ACCESS FULL | T | 2118 | 200K | 41 |
Predicate Information (identified by operation id):
1 - filter("OWNER"='SYS')
Note
SQL> alter session set db_file_multiblock_read_count = 128;
Session altered.
SQL> select /*+ full(t) */ * from t where owner = 'SYS';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | 2118 | 200K | 27 | |
| TABLE ACCESS FULL | T | 2118 | 200K | 27 |
Predicate Information (identified by operation id):
1 - filter("OWNER"='SYS')
Note
|
h2. 3. I/O 비용 모델의 비현실적인 가정
- 가정1 : Single Block I/O와 Multiblock I/O는 비용이 같다.
- 가정2 : 캐싱 효과를 전혀 고려하지 않는다.
위 두가지 가정은 비현실적인 가정이므로, 이를 보정하기 위해 두 파라미터를 8버젼부터 제공하기 시작하였다.
* optimizer_index_cost_adj
- 인덱스 탐색 비용을 조정할 때 사용한다.
- 값의 범위는 1~10000 이다
- 기본값 : 100 ( 한번의 I/O call을 통해 Single Block Read 방식으로 한 블록을 읽는 비용과 Multiblock Read 방식으로 여러 블럭을 읽는 비용을 같게 평가한다.)
- 예) optimizer_index_cost_adj = 25 : Single Block Read 방식의 I/O call비용을 Multiblock Read 방식의 I/O call비용의 25% 간주한다.
- 인덱스를 경유할 때의 물리적 I/O비용을 Full Table Scan할 때의 물리적 I/O 비용과 대비한 상태적 비용을 표현한다.
- 이 값을 낮게 설정할수록 옵티마이저는 테이블 스캔보다 인덱스를 이용한 테이블 액세스를 선호하게 된다.
* optimizer_index_caching
- NL조인에서 inner쪽 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 알려주는 파라미터이다.
- 값의 범위는 0~100이다.
- 이 값을 높게 설정할수록 옵티마이저는 인덱스를 이용한 NL조인을 선호하게 된다.
h1. 2. CPU 비용 모델
|
|
=> 이에 쿼리 수행에 필요한 I/O뿐만 아니라 예상되는 CPU리소스 사용량까지 비용 계산식에 포함하는 CPU비용 모델을 9i부터 선보였다.
*[ CPU 비용 모델의 비용 계산식 ]*
|
비용 = ( Single Block I/O 요청 횟수 * Single Block I/O에 소요되는 시간 (ms) +
Multiblock I/O 요청 횟수 * Multiblock I/O에 소요되는 시간 (ms) +
쿼리 수행에 필요한 예상 CPU 사이클 수 / 초당 처리할 수 있는 CPU 사이클 수
) / Single Block I/O에 소요되는 시간
1) Single Block I/O 요청 횟수, Multiblock I/O 요청 횟수에 미리 측정해 놓은 평균 소요시간을 각각 곱합으로써 I/O일량을 시간으로 표현하였다.
2) 인덱스 스캔에 의한 테이블 액세스 비용과 테이블 스캔 비용 간에 상대적인 시간 차이가 있음을 계산식에 포함시켰다.
3) 쿼리 수행에 필요한 예상 CPU 사이클 수를 초당 처리할 수 있는 CPU 사이클 수로 나눈 값으로 CPU비용을 계산하였다.
4) I/O 시간과 CPU연산 시간을 더한 시간 개념을 빌어 쿼리 수행 비용을 평가하는 것이 CPU 비용 모델의 핵심이다.
5) I/O 시간과 CPU 연산 시간을 더한 값을 Single Block I/O에 소요되는 시간으로 나눔으로써 Single Block I/O에 소요되는 시간간의 상대적인 시간 비용을 표현한다.
6) 즉, CPU 비용 모델의 비용은 쿼리의 예상 총 수행 시간을 Single Block I/O 시간 단위로 표현한다.
|
[참고]
* 10g에서는 user_tab_statistics테이블을 조회하면 테이블별로 캐싱된 블록 수와 캐시 히트율을 얻을 수 있다. 오라클은 캐싱 효과를 비용 계산식에 포함하려는 움직임을 보이고 있으나 아직 반영되지는 않았다.
h2. 문서에 대하여
* 최초작성자 : [이신재]
* 최초작성일 : 2010년 11월 11일
* 이 문서는 [오라클클럽|http://www.gurubee.net] [대용량 데이터베이스 스터디] 모임에서 작성하였습니다.
* {color:blue}{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*{color}