꿈꾸는 개발자, DBA 커뮤니티 구루비

Index Scan Cost

Table Full Scan Cost

Table Full Scan Cost

Cost = Single Block I/O Count + Adjusted Multi Block I/O Count + Adjusted CPU Count

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

Blocks = 100, MBRC = 4, sreadtim = 5, mreadtim = 10

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 = Blevel + Leaf Blocks * Index Selectivity + Clustering Factor * Table Selectivity + Adjusted CPU Count

(예제) 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가 같아지는 지점은 얼마인가?

Total Rows = 100000, Total Blocks = 10000, MBRC = 10, Index Height = 3(Blevel = 2), Leaf Blocks = 100, Clustering Factor = 50000

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를 선택하는 경계지점\!\!*

Index Scan Cost에서 Clustering Factor가 차지하는 비중이 높다.
Index에 대해 넓은 범위의 Leaf Block을 Scan하는 경우에는 Cost가 높다.(즉, Selectivity가 차지하는 비중이 높다.)
Table Full Scan의 Cost는 MBRC값에 크게 의존한다.

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 값의 차이에 의해 발생