1. I/O 횟수: I/O(CALL) 호출 횟수
2. CPU : I/O (CALL) 횟수 + 시간
3. CHOOSE : 시스템 통계 있다면 CPU 모델, 없으면 I/O비용모델
기본값은 CHOOSE : ORACLE 9i 에서는 통계를 가지고 있지 않으므로 통계를 생성하면 CPU모델방식이 되며 ORACLE 10g에서는 CPU모델이 선택 되도록 NoWorkload 시스템 통계를 추가 도입
NoWorkload:오라클의 내부적인 설정을 기준으로 한 통계 정보 생성
힌트를 이용 하여 쿼리 레벨로도 비용 모델을 선택 할 수 있다.
**cpu_costing*
**no_cpu_costing*
(1) I/O 비용 모델
:디스크 I/O CALL 횟수(논리 물리가 아닌 입출력 CALL횟수)를 나타낸다.
인덱스를 경유한 테이블 엑세스 비용
:Single Block I/O방식을 사용(디스크에서 한 블록을 읽을때마다 한 번의 I/O을 일으키는 방식)하므로 물리적 블록 개수와 일치한다.
SQL> Create table t as select * from all_objects;
테이블이 생성되었습니다.
SQL> create index t_owner_idx on t(owner);
인덱스가 생성되었습니다.
SQL> begin
dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1');
end;
/
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> alter session set "_optimizer_cost_model"=io;
세션이 변경되었습니다.
SQL> set autotrace traceonly exp;
SQL> select /*+ index(t) */ * from t where owner='SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 910642575
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2368 | 215K| 69 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2368 | 215K| 69 |
|* 2 | INDEX RANGE SCAN | T_OWNER_IDX | 2368 | | 6 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Note
-----
- cpu costing is off (consider enabling it)
분석
1. 인덱스 엑세스는 6개 발생
2. 테이블 엑스스 + 인덱스 엑세스 = 69개
3. 테이블 ACCESS : 69 - 6 = 63개가 발생
4. 63개의 Single Block I/O가 발생 함.
5. 인덱스 단계를 살펴 보면 총 ROW가 2368개가 발생 이 되었으나 I/O 63 뿐인 이유는 클레스터링 팩터가 비용 계산식에 고려되었기 때문이다.
비용 = blevel + --수직 탐색 비용
(리프 블록의 수 X 유효 인덱스 선택도) + --수평탐색 비용
(클러스터링 팩터 X 유효 테이블 선택도) + --테이블 Random Access 발생 비용
:데이터의 군집성
1. blevel : 리프 블록에 도달 하기 전의 읽게될 블랜치 블록의 개수
2. 유효 인덱스 선택도: 조건절에 만족하는 레코드를 찾기 위한 예상 스캔 비율
리프 블록은 인덱스 레코드 정렬된 상태로 저장 되므로 다시 말해 방문 비율을 의미
3. 유효 테이블 선택도: 최종적으로 테이블을 방문할 것으로 예상되는 비율
유효 인덱스 선택도를 Predicate로 연결 해보자
유효 인덱스 선택도: Access Predicate에 의해 결정
유효 테이블 선택도: Access Predicate 와 Filter Preidcate에 의해 결정
최종 선택도 : 테이블 Filter predicate까지 포함한 모든 조건절
Access Predicate 제외
ex> 1. lower(owner) = 'sys': 좌변 컬럼 가공
2. OBJECT_NAME like '%_CD%':양쪽의 %를 사용
3. OBJECT_NAME BETWEEN A AND Z:같은 컬럼에 조건절이 두개 이상
Access Predicate:인덱스 스캔 범위를 결정 하는데 영향을 미치는 조건절)
Filter Preidcate: 테이블의 엑세스 여부를 결정 짓는 조건절
SELECT i.blevel AS BL
,i.leaf_blocks AS LF_BLKS
,c.num_distinct AS NUM_DISNT --선택도
,i.clustering_factor AS CLUS_FACTR
,1 + (i.leaf_blocks * 1/c.num_distinct) AS "IDX_SCAN_COST"
,1 + (i.leaf_blocks * 1/c.num_distinct)
+ (i.clustering_factor * 1/c.num_distinct) AS "TOT_TAB_ACC_COST"
FROM USER_INDEXES i, USER_TAB_COL_STATISTICS c
WHERE i.index_name = 'T_OWNER_IDX'
AND i.table_name = c.table_name
AND c.column_name= 'OWNER';
BL LF_BLKS NUM_DISNT CLUS_FACTR IDX_SCAN_COST TOT_TAB_ACC_COST
---------- ---------- ---------- ---------- ------------- ----------------
1 115 21 1322 6.47619048 69.4285714
Full Scan에 의한 테이블 엑세스 비용
: HWN아래쪽 블록을 순차적으로 읽어 들이는 과정에서 발생하는 I/OCALL 횟수로 비용을 계산한다.
FULL SCAN할 때는 한번여 여러 BLOCK을 읽어들이는 Multiblock I/O방식을 사용 하므로 총 블럭수 / db_file_multiblock_read_count = I/O CALL이 발생을 하지만 내부적 조정된 값으로 비용을 계산 하기 때문에 차이가 발생 한다.
--EX> T 테이블의 총블럭이 703
SQL> SELECT BLOCKS FROM USER_TABLES WHERE TABLE_NAME = 'T';
BLOCKS
----------
703
--블럭 계수 조절
SQL> ALTER SESSION SET db_file_multiblock_read_count = 10;
세션이 변경되었습니다.
SQL> select /*+ full(t) */ * from t where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 2368 | 215K| 94 |
|* 1 | TABLE ACCESS FULL| T | 2368 | 215K| 94 |
----------------------------------------------------------
--20 :ALTER SESSION SET db_file_multiblock_read_count = 20;
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 2368 | 215K| 60 |
|* 1 | TABLE ACCESS FULL| T | 2368 | 215K| 60 |
----------------------------------------------------------
--30::ALTER SESSION SET db_file_multiblock_read_count = 30;
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 2368 | 215K| 46 |
|* 1 | TABLE ACCESS FULL| T | 2368 | 215K| 46 |
----------------------------------------------------------
I/O비용 모델의 비현실적인 가정
디스크 I/OCALL 횟수로 테이블 엑세스 비용을 평가 할경우
1. Single Block I/O와 Multiblock I/o 비용은 같다
2. 캐싱 횩4ㅘ를 전혀 고려 하지 않는다.
optimzer_index_cost_adj
: 인덱스 탐색 비용을 조정 하고자 할때 사용
설정 범위값은 1~10,000
기본값이 100이란 수치는 한 번의 I/O CALL을 통해 Single Block Read 방식으로 한 블록을 읽는 비용과
Multiblock Read 방식으로 여러 블록을 읽는 비용을 같게 평가 하라는 의미
낮게 설정 할수록 옵티마이저는 테이블 스캔보다 인덱스를 이용한 테이블 엑세스를 선호
optimzer_index_caching
:NL 조인시 INNER 테이블 쪽을 매번 디스크에 읽는가정 하지만 이는 비현실적이므로
NL조인에서 inner쪽 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 알려주는 파라미터이다.
값의 범위는 0~100이며 값이 높게 설정 할 수록 옵티마이저는 인덱스를 이용한NL 조인을 선호
(2) CPU 비용 모델
:데이터 베이스 오퍼레이션은 CPU를 사용 하므로 경우에 따라 I/O보다 큰영향을 미친다.
I/O이 소량임에도 불구하고 쿼리 수행 시간이 오래걸리는 경우
1) 해시 조인할 때, 해시 체인에 달린 레코드가 많아 해시 체인을 스캔하는 부하가 심할 때
2) 캐싱된 블록을 반복적으로 읽는데, 한 블록 내에서 매번 비효율적으로 많은 레코드를 스캔할 때 ( NL조인 inner쪽 인덱스 선두 컬럼이 between 조건일 때)
3) 버퍼를 Pin한 상태에서 같은 블록을 반복 액세스할 때
4) 다량의 레코드를 읽으면서 건건이 여러 개의 사용자 정의 함수를 반복 호출할 때
5) 메모리 소트를 반복할 때
CPU 사용량이 다소 증가 하는 경우
6) 조건절 개수가 아주 많을 때
7) 조건절이나 select-list에 연산 집약적인 작업을 많이 포함할 때