I/O

- I/O 요청 횟수

CPU

- I/O 요청 횟수 + CPU 시간

비고

if 9i and optimizer_cost_mode=CHOOSE /*CHOOSE가 기본값<->IO */

       if (시스템통계 있으면) then CPU 비용 모델

       else I/O 비용 모델

if 10g then CPU 비용 모델 + 시스템 통계(없으면 NoWorkload 시스템 통계)

* query 레벨에서 비용 모델 선택하는 힌트

   => cpu_costing, no_cpu_costing

# ch03_07.txt 실험 => 결과는 첨부파일 ch03_07.hwp 참조

SQL> create table t

2 as

3 select * from all_objects;

테이블이 생성되었습니다.

SQL> create index t_owner_idx on t(owner);

인덱스가 생성되었습니다.

SQL> begin

2 dbms_stats.gather_table_stats(user, 'T'

3 , method_opt=>'for all columns size 1');

4 end;

5 /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> alter session set "_optimizer_cost_model" = io;

세션이 변경되었습니다.

SQL> set autotrace traceonly exp;

SQL>

SQL> select /*+ index(t) */ * from t where owner = 'SYS';

Execution Plan

---------------------------------------------------------------------------

| Id | Operation                  | Name        | Rows  | Bytes| Cost |

---------------------------------------------------------------------------

| 0  | SELECT STATEMENT           |             | 2385  | 216K | 70   |

| 1  | TABLE ACCESS BY INDEX ROWID| T           | 2385  | 216K | 70   |

|* 2 | INDEX RANGE SCAN           | T_OWNER_IDX | 2385  |      | 6    |

---------------------------------------------------------------------------

(설명) 비용=6(인덱스)+64(테이블)=70, Rows=2385

Rows가 2385번이므로 2385번의 call이 발생해야하지만 클러스터링 팩터가 비용계산에

반영되었기 때문에 64번 발생

비용 = blevel +

            (리프블록수 * 유효인덱스 선택도) +

            (클러스터링 팩터 * 유효 테이블 선택도)

blevel : 브랜치 레벨

유효인덱스선택도 : 방문할 리프 블록 비율(Access Predicate에 의해 결정)

유효테이블선택도 : 방문할 테이블 블록 비율(Access Predicate와 Filter Predicate에 의해 결정)

인덱스 Access Predicate와 인덱스 Filter Predicate가 다른 경우

- 좌변컬럼을 가공한 조건절

- 안쪽 % 또는 양쪽 % 기호를 사용한 like 조건절

- 같은 컬럼에 대한 조건절이 2개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한 다른 조건절

(예제의 비용 계산) - 교재 436 페이지 값을 가정할 경우(실험마다 다름)

비용 = 1 + 116*(1/21) + 1337*(1/21) = 70.12

db_file_multiblock_read_count

rows

bytes

cost

2

2385

216K

269

4

2385

216K

171

8

2385

216K

109

16

2385

216K

70

32

2385

216K

45

64

2385

216K

29

128

2385

216K

19

가정

Single Block I/O와 Multiblock I/O는 비용이 같다.

캐싱 효과를 전혀 고려하지 않는다.

수정

파라

메터

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비용(Single Block Read)을 Full Table Scan할 때의

  물리적 I/O 비용(Multiblock Read)과 대비한 상대적 비용을 표현한다.

- 이 값을 낮게 설정할수록 옵티마이저는 테이블 스캔보다 인덱스를 이용한 테이블 액세스를

  선호하게 된다.

optimizer_index_caching

- NL조인에서 inner쪽 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 알려주는 파라미터이다.

- 값의 범위는 0~100이다.(0은 캐싱이 안되있어서 디스크에서 읽을 경우 가정)

- 이 값을 높게 설정할수록 옵티마이저는 인덱스를 이용한 NL조인을 선호하게 된다.

- 해시 조인할 때, 해시 체인에 달린 레코드가 많아 해시 체인을 스캔하는 부하가 심할 때

- 캐싱된 블록을 반복적으로 읽는데, 한 블록 내에서 매번 비효율적으로 많은 레코드를 스캔할 때

    ( NL조인 inner쪽 인덱스 선두 컬럼이 between 조건일 때)

- 버퍼를 Pin한 상태에서 같은 블록을 반복 액세스할 때

- 다량의 레코드를 읽으면서 건건이 여러 개의 사용자 정의 함수를 반복 호출할 때

- 메모리 소트를 반복할 때

- 조건절 개수가 아주 많을 때

- 조건절이나 select-list에 연산 집약적인 작업을 많이 포함할 때

비용 = ( #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 : Multi Block I/O에 소요되는 시간(ms)

cpuspeed : 초당 처리할 수 있는 CPU 사이클 수

* sreadtim, mreadtim, cpuspeed 값은 시스템 통계수집시 결정

- Single Block I/O 요청 횟수, Multiblock I/O 요청 횟수에 미리 측정해 놓은

   평균 소요시간(sreadtim, mreadtim)을 각각 곱합으로써 I/O일량을 시간으로 표현하였다.

- 인덱스 스캔에 의한 테이블 액세스 비용과 테이블 스캔 비용 간에 상대적인 시간 차이가 있음을

   계산식에 포함시켰다.

- CPU 비용은 쿼리 수행에 필요한 예상 CPU 사이클 수를 초당 처리할 수 있는 CPU 사이클 수로

   나눈 값으로 CPU비용을 계산하였다.

- I/O 시간과 CPU연산 시간을 더한 시간 개념을 빌어 쿼리 수행 비용을 평가하는 것이

   CPU 비용 모델의 핵심이다.

- I/O 시간과 CPU 연산 시간을 더한 값을 Single Block I/O에 소요되는 시간으로 나눔으로써

   Single Block I/O에 소요되는 시간간의 상대적인 시간 비용을 표현한다.

- 즉, CPU 비용 모델의 비용은 쿼리의 예상 총 수행 시간을 Single Block I/O 시간 단위로

   표현한 것이라고 할 수 있다.

- 10g에서는 user_tab_statistics테이블을 조회하면 테이블별로 캐싱된 블록 수와 캐시 히트율을 얻을 수

   있다.  오라클은 캐싱 효과를 비용 계산식에 포함하려는 움직임을 보이고 있으나 아직 반영되지는 않았다.

     select table_name, avg_cached_blocks, avg_cache_hit_ratio

     from user_tab_statistics;