7. 비용

조회 수 2342 추천 수 0 2011.05.02 23:00:03
휘휘 *.38.213.66
  • I/O 비용모델
    • 예상되는 I/O 요청 횟수만을 퀴리 수행 비용으로 간주
  • CPU비용모델
    • I/O비용모델 + 시간개념


_optimizer_cost_model 파라미터


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


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

10g - NoWorkload 시스템통계의 추가


  • 쿼리 레벨로 비용모델 선택 힌트
    • 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>







Full Scan에 의한 테이블 액세스 비용


  • 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_countRowsBytesCost
22904 263K258
42904 263K164
82904263K105
162904263K67
322904263K43
642904263K28
1282904263K18





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
  • 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^