Index Scan Cost

Table Full Scan Cost

• (예제) Table Full Scan Cost가 얼마쯤 계산될까?

• • Single Block I/O Count = 0
  • Adjusted Multi Block I/O Count = Multi Block I/O Count * 가중치 = (Blocks/*MBRC*) * (mreadtim/sreadtim)
  • Cost = (100/*4*)*(10/2) = 50 (약 50정도의 Cost가 필요)
  • Full Table Scan의 Cost는 Table의 Block수, MBRC, Multi Block Read의 가중치에 의해 결정돤다.
    => MBRC를 제어함으로써 Table Full Scan Cost 제어가능

Index Scan Cost

• Index Scan을 통해 Row를 읽는 과정
  • 조건을 만족하는 최초의 Index Leaf Block까지 찾아간다.
  • 조건을 만족하는 Index Key를 순서대로 Fetch 하면서
  • ROWID를 이용해 Table Block을 하나씩 읽으면서
  • 조건에 해당하는 Row를 Fetch한다.

• (예제) Index Scan Cost가 얼마쯤 계산될까?
  • Cost = 2 + (4*1/4) + (8*1/5)
    => Index Scan Operation의 Cost는 Index Height, Selectivity, Leaf Block수, Clustering Factor에 의해 결정됨

• Index Selectivity와 Table Selectivity가 다른 경우
  => 다중 Column으로 이루어진 Index에 대해서 Key값의 일부만 조건으로 사용된 경우

Dirty Estimation

• (예제) Index Scan의 Cost와 Table Scan의 Cost가 같아지는 지점은 얼마인가?

• • Blevel + Leaf Blocks*Selectivity + Clustering Factor*Selectivity = Table Blocks/MBRC
  • 2 + 100*S + 50000*S = 10000/10
  • S = 0.0019 = 2%
    => 2%가 Index Scan의 Cost와 Table Scan를 선택하는 경계지점\!\!

Excution Plan

• 실행계획에서 INDEX RANGE SCAN Operation은 Root Block에서 Leaf Block까지 찾아가는 Cost와 Index Leaf Block을 읽는 Cost의 합을 의미한다.
• 실행계획에서 TABLE ACCESS BY INDEX ROWID Operation은 Index Key값을 확인한 후 ROWID를 이용해 Table을 찾아는 Cost를 의미한다.

• (예제) 두 실행계획에서 TABLE ACCESS BY INDEX ROWID Operation의 Cost가 큰 차이가 나는 이유는 무얼까?(page 116~117 참조)

EXPLAIN PLAN FOR
SELECT /*+ GOOD CLSF */
       *
FROM   T_CLSF
WHERE  C1 BETWEEN 1
          AND     100
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   100 |   700 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_CLSF    |   100 |   700 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_CLSF_I1 |   100 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
;

EXPLAIN PLAN FOR
SELECT /*+ BAD CLSF INDEX(T_CLSF) */
       *
FROM   T_CLSF
WHERE  C2 BETWEEN 1
          AND     100
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   100 |   700 |    97   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_CLSF    |   100 |   700 |    97   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | T_CLSF_I2 |   100 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

• • Clustering Factor가 지나치게 높거나 Table Selectivity가 지나치게 크다는 의미
    => 이런 현상은 대부분 Clustering Factor 값의 차이에 의해 발생

문서에 대하여

• 최초작성자 : 오정희
• 최초작성일 : 2009년 2월 28일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.*