• I/O 비용모델
• 예상되는 I/O 요청 횟수만을 퀴리 수행 비용으로 간주
• CPU비용모델
• I/O비용모델 + 시간개념

_optimizer_cost_model 파라미터

• IO
• I/O 비용모델
• CPU
• CPU 비용모델
• CHOOSE
• 시스템 통계가 있으면 CPU, 없으면 I/O비용모델

※ 9i - 관리자가 시스템 통계를 생성해줄때 cpu비용모델로 작동

• 쿼리 레벨로 비용모델 선택 힌트
• cpu_costing
• no_cpu_costing

(1) I/O 비용 모델

• 비용
• 디스크 I/O Call 횟수

• 인덱스를 경유한 테이블 액세스 비용
• single block I/O 방식 사용
• 디스크에서 한블록을 읽을때마다 한번의 I/O Call을 일으킴
• 물리적 블록 개수가 액세스비용(=I/O Call 횟수) 과 일치
  

SQL> create table t as 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 autot 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            |             |  2904 |   263K|    84 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |  2904 |   263K|    84 |
|*  2 |   INDEX RANGE SCAN          | T_OWNER_IDX |  2904 |       |     7 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OWNER"='SYS')

Note
-----
  - cpu costing is off (consider enabling it)

SQL>

인덱스 스캔단계 7
테이블 액세스 77 ( 84 - 7)
번의 Single Block I/O Call 이 발생할것으로 예상

인덱스를 이용한 테이블 액세스 비용

비용 = blevel +                                 -- 인덱스 수직적탐색 비용
        (리프 블록 수 * 유효 인덱스 선택도 ) +  -- 인덱스 수평적 탐색 비용
    (클러스터링 팩터 * 유효 테이블 선택도 )     -- 테이블 Random 액세스 비용

• blevel
• 브랜치 레벨, 리프블록에 도달하기까지의 브랜치 블륵수
• 유효인덱스 선택도
• 전체인덱스 조건절에 만족할것으로 예상되는 비율(%), 방문할 리프 블록 비율
• 인덱스 Access Predicate에 의해 결정
• 유효 테이블 선택도
• 전체 레코드 중 인덱스스캔후 최종적으로 테이블을 방문할것으로 예상되는 비율 (%)
• 클러스터링 팩터와 곱하여 예상되는 테이블 블록 개수를 구할수 있음
• 인덱스 Access Predicate와 Filter Predicate에 의해 결정
• 최종 테이블 선택도
• 테이블 Filter Predicate까지 포함한 모든 조건절에 의해 결정

• 인덱스 Access Predicate 와 인덱스 Filter Predicate 가 다른경우
• 좌변 컬럼을 가공한 조건절
• 왼쪽 ‘%’ 또는 양쪽 ‘%’ 기호를 사용한 like 조건절
• 동일 컬럼에 조건절이 두개일때, 인덱스 엑세스로 사용되지 못한 조건절
  

• 선택도는 SQL이 위 조건절을 포함하는 경우를 제외하고 유효인텍스 선택도와 유효 테이블 선택도는 항상 같음

• 선택도는 num_distinct 컬럼 통계를 이용해 구하고
  
• 브랜치 레벨, 리프블록수, 클러스터링 팩터는 인덱스 통계를 통해서 얻을수 잇음

SQL> select i.blevel, i.leaf_blocks, c.num_distinct, i.clustering_factor
 2     ,1+(i.leaf_blocks * 1/c.num_distinct) "인덱스 스캔 비용"
       ,1+(i.leaf_blocks * 1/c.num_distinct)
       + (i.clustering_factor * 1/c.num_distinct) "총 테이블 액세스 비용"
from    user_indexes i, user_tab_col_statistics c
where i.index_name='T_OWNER_IDX'
and     c.table_name=i.table_name
and     c.column_name='OWNER';
 3    4    5    6    7    8

    BLEVEL LEAF_BLOCKS NUM_DISTINCT CLUSTERING_FACTOR 인덱스 스캔 비용 총 테이블 액세스 비용
---------- ----------- ------------ ----------------- ----------------------- ------------------------------
        1         115           17              1303              7.76470588                     84.4117647

SQL>

• Multiblock I/O 방식 사용
• db_file_multiblock_read_count 파라미터에 영향을 받음

SQL> select blocks from user_tables where table_name='T';

    BLOCKS
----------
      678

SQL>


 1* select /*+ full(t) */ * from t where owner='SYS'
SQL> /

22909 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2904 |   263K|   258 |
|*  1 |  TABLE ACCESS FULL| T    |  2904 |   263K|   258 |
----------------------------------------------------------

db_file_multiblock_read_count 값에 따른 cost 변화

db_file_multiblock_read_count	Rows	Bytes	Cost
2	2904	263K	258
4	2904	263K	164
8	2904	263K	105
16	2904	263K	67
32	2904	263K	43
64	2904	263K	28
128	2904	263K	18

I/O 비용 모델의 비현실적인 가정

• Single Block I/O와 Multiblock I/O는 비용이 같다
• 캐싱 효과를 전혀 고려하지 않는다
  

를 보정하기 위한 파라미터 제공 (8 부터)


optimizer_index_cost_adj

• 인덱스 탐색 비용을 조정
• 값의 범위
• 1~10,000
• 100
• 기본값
• 한번의 i/o call을 통해 single block read방식으로 한븕 읽는 비용과
• Multiblock Read 방식으로 여러블록을 읽는 비용을 같게 평가
• 25
• Single Block Read 방식의 I/O Call 비용을
• Multiblock Read 방식의 I/O call 비용의 25%로 간주
• 인덱스를 경유할때의 물리적 I/O비용 ( Single Block Read)을
  
• Full Table Scan 할때의 물리적 I/O비용(Multiblock Read) 과 대비, 상대적 비용을 표현
• 이 값이 낮을수록 옵티마이저는 테이블 스캔보다 인덱스 액세스를 선호

optimizer_index_caching

• NL 조인시 inner 쪽 인덱스 블록을 매번 디스크에서 읽는다고 가정 ( optimizer_index_caching=0)
• NL 조인에서 inner 쪽 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 알려주는 파라미터
• IN-List Iterator 방식으로 인덱스를 탐색할때 읽는 인덱스 블록 액세스 비용에도 영향을 미침
• 값의 범위
• 1~100
• 높게 설정할수록 인덱스를 이용한 NL조인을 선호

(2) CPU 비용 모델

• 블록 I/O 소량, 쿼리 수행 시간이 상당히 오래 걸리는 경우
• 해시 조인할때, 해시 체인에 달린 레코드가 많이 해시 체인을 스캔하는 부하가 심할때
• 캐싱된 블록을 반복적으로 읽을대, 한블록에서 매법 비효율적으로 많은 레코드를 스캔할때
  (NL 조인 Inner쪽 인덱스 선두 컬럼이 between 조건일때)
• 버퍼를 Pin한 상태에서 같은 블록을 반복 액세스 할때
• 다량의 레코드를 읽으면서 건건이 여러개의 사용자 정의 함수를 반복 호출할때
• 메모리 소트를 반복할때

• CPU 사용량이 증가 할때
• 조건절 개수가 아주 많을때
• 조건절이나 select-list 에 연산 집약적인 작업을 많이 포함할때
  

• CPU 비용 모델에서의 비용 계산식

Cost=( #SRds * sreadtim +
    #MRds * mreadtim +
    #CPUCycles / cpuspeed

) /sreadtim

• #SRds
  
• Single Block I/O 요청 횟수
• #MRds
  
• Multiblock I/O 요청횟수
• #CPUCycles
  
• 쿼리 수행에 필요한 CPU 사이클 수
• sreadtim
  
• Single Block I/O에 소요되는 시간 (ms), 통계수집시 측정
• mreadtim
  
• Multiblock I/O에 소요되는 시간 (ms), 통계수집시 측정
• cpuspeed
  
• 초당 처리할 수 있는  CPU 사이클 수, 통계수집시 측정
• CPU 속도를 초당 사이클 수로 표현한것
• 사용자의 플랫폼에서 오라클이 수행해 본 몇 가지 표준 오퍼레이션의 초당 수행 횟수를 표현한것

I/O 비용

• #SRds(Single Block I/O 요청 횟수)와 #MRds(Multiblock I/O 요청횟수)에
  
• 미리 측정해 놓은 평균 소요시간 (sreadtim, mreadtim)을 각각 곱함으로써 I/O 일략을 시간으로 표현

cpu비용

• I/O 비용과 함께 CPU비용을 계산식에 포함
• '예상 cpu 사이클수 #CPUCucles' 를 초당 처리할수있는 CPU사이클수 (cpuspeed)로 나눈 값으로 계산
• 예상CPU사이클 수
• 순수한 CPU 연산 비용과 버퍼 캐시에서 데이터를 읽는 CPU 비용까지 포함
  

• I/O 시간과 cpu연산 시간을 더한값을 Single clock I/O 평균소요시간 (sreadtim)으로 나눔
  -> single block i/o에 소요되는 시간과의 상대적인 시간 비용으로 표현
  

• 쿼리의 예상 총 수행 시간을 single block i/o 시간 단위로 표현한것

• 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
• 작성자: 남송휘 (tofriend)
• 최초작성일: 2011년 5월 1일
• 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
• 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^