by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

7. 비용




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

1. I/O 비용 모델

1. 인덱스를 경유한 테이블 액세스 비용

  • 인덱스를 경유한 테이블 액세스 시에는 Single Block I/O 방식으로 사용되며, 디스크에서 한 블록을 읽을 때마다 한번의 I/O call이 발생한다.
  • 읽게 될 물리적 블록 개수가 액세스 비용과 일치한다.
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
----------------------------------------------------------
Plan hash value: 910642575

---------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  2118 |   200K|    62 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |  2118 |   200K|    62 |
|*  2 |   INDEX RANGE SCAN          | T_OWNER_IDX |  2118 |       |     5 |
---------------------------------------------------------------------------

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

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

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

[ I/O 비용 모델의 비용 계산식 ]

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

* blevel : 리프 블록에 도달하기 전에 읽게 될 브랜치 블록 개수
* 유효 인덱스 선택도 : 전체 인덱스 레코드중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율 (인덱스 Access Predicate에 의해 결정)
* 유효 테이블 선택도 : 인덱스 스캔 후 최종적으로 테이블을 방문할 것으로 예상되는 비율 (인덱스 Access Predicate와 Filter Predicate에 의해 결정)
* 클러스터링 팩터 : 인덱스 스캔 후 전체 로우를 액세스할때 읽힐 것으로 예상되는 테이블 블록 개수 
     

* 최종 테이블 선택도 : 테이블 Filter Predicate까지 포함한 모든 조건절에 의해 결정
* SQL조건절이 인덱스 Access Predicate와 인덱스 Filter Predicate 가 다른 경우를 제외하면 유효 인덱스 선택도와 유효 테이블 선택도는 항상 같다
 [참고] 인덱스 Access Predicate와 인덱스 Filter Predicate 가 다른 경우 
 1) 좌변 컬럼을 가공한 조건절
 2) 왼쪽 % 또는 양쪽 % 기호를 사용한 like 조건절
 3) 같은 컬럼에 대한 조건절이 두 개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한 다른 조건절

* 참조하는 통계 정보
 - 선택도 : num_distinct 컬럼 통계
 - blevel, 리프 블록 수, 클러스터링 팩터 : 인덱스 통계

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       스캔 비용  액세스 비용
---------- ----------- ------------ ---------- ----------- -----------
         1         169           34       1923     5.97059    62.52941

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
    ----------
          1026
    
    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
    ----------------------------------------------------------
    Plan hash value: 1601196873
    
    ----------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost  |
    ----------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |  2118 |   200K|   389 |
    |*  1 |  TABLE ACCESS FULL| T    |  2118 |   200K|   389 |
    ----------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("OWNER"='SYS')
    
    Note
    -----
       - cpu costing is off (consider enabling it)
    
    SQL> alter session set db_file_multiblock_read_count = 4;
    
    Session altered.
    
    SQL> select /*+ full(t) */ * from t where owner = 'SYS';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1601196873
    
    ----------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost  |
    ----------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |  2118 |   200K|   247 |
    |*  1 |  TABLE ACCESS FULL| T    |  2118 |   200K|   247 |
    ----------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("OWNER"='SYS')
    
    Note
    -----
       - cpu costing is off (consider enabling it)
    
    SQL> alter session set db_file_multiblock_read_count = 8;
    
    Session altered.
    
    SQL> select /*+ full(t) */ * from t where owner = 'SYS';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1601196873
    
    ----------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost  |
    ----------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |  2118 |   200K|   157 |
    |*  1 |  TABLE ACCESS FULL| T    |  2118 |   200K|   157 |
    ----------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("OWNER"='SYS')
    
    Note
    -----
       - cpu costing is off (consider enabling it)
    
    SQL> alter session set db_file_multiblock_read_count = 16;
    
    Session altered.
    
    SQL> select /*+ full(t) */ * from t where owner = 'SYS';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1601196873
    
    ----------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost  |
    ----------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |  2118 |   200K|   100 |
    |*  1 |  TABLE ACCESS FULL| T    |  2118 |   200K|   100 |
    ----------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("OWNER"='SYS')
    
    Note
    -----
       - cpu costing is off (consider enabling it)
    
    SQL> alter session set db_file_multiblock_read_count = 32;
    
    Session altered.
    
    SQL> select /*+ full(t) */ * from t where owner = 'SYS';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1601196873
    
    ----------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost  |
    ----------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |  2118 |   200K|    64 |
    |*  1 |  TABLE ACCESS FULL| T    |  2118 |   200K|    64 |
    ----------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("OWNER"='SYS')
    
    Note
    -----
       - cpu costing is off (consider enabling it)
    
    SQL> alter session set db_file_multiblock_read_count = 64;
    
    Session altered.
    
    SQL> select /*+ full(t) */ * from t where owner = 'SYS';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1601196873
    
    ----------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost  |
    ----------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |  2118 |   200K|    41 |
    |*  1 |  TABLE ACCESS FULL| T    |  2118 |   200K|    41 |
    ----------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("OWNER"='SYS')
    
    Note
    -----
       - cpu costing is off (consider enabling it)
    
    SQL> alter session set db_file_multiblock_read_count = 128;
    
    Session altered.
    
    SQL> select /*+ full(t) */ * from t where owner = 'SYS';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1601196873
    
    ----------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost  |
    ----------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |  2118 |   200K|    27 |
    |*  1 |  TABLE ACCESS FULL| T    |  2118 |   200K|    27 |
    ----------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("OWNER"='SYS')
    
    Note
    -----
       - cpu costing is off (consider enabling it)
    
    

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조인을 선호하게 된다.

2. CPU 비용 모델

 * 블록 I/O가 소량인데도 쿼리 수행 시간이 오래 걸리는 경우 ( CPU사용량이 증가하는 경우 )
 1) 해시 조인할 때, 해시 체인에 달린 레코드가 많아 해시 체인을 스캔하는 부하가 심할 때
 2) 캐싱된 블록을 반복적으로 읽는데, 한 블록 내에서 매번 비효율적으로 많은 레코드를 스캔할 때 ( NL조인 inner쪽 인덱스 선두 컬럼이 between 조건일 때)
 3) 버퍼를 Pin한 상태에서 같은 블록을 반복 액세스할 때
 4) 다량의 레코드를 읽으면서 건건이 여러 개의 사용자 정의 함수를 반복 호출할 때
 5) 메모리 소트를 반복할 때
 6) 조건절 개수가 아주 많을 때
 7) 조건절이나 select-list에 연산 집약적인 작업을 많이 포함할 때

=> 이에 쿼리 수행에 필요한 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테이블을 조회하면 테이블별로 캐싱된 블록 수와 캐시 히트율을 얻을 수 있다. 오라클은 캐싱 효과를 비용 계산식에 포함하려는 움직임을 보이고 있으나 아직 반영되지는 않았다.

문서에 대하여

  • 최초작성자 : 이신재
  • 최초작성일 : 2010년 11월 11일
  • 이 문서는 오라클클럽 [대용량 데이터베이스 스터디] 모임에서 작성하였습니다.
  • 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.