Optimizing Oracle Optimizer (2009년)
Index Scan Cost 0 0 52,542

by 구루비스터디 Index Scan [2018.07.14]


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 값의 차이에 의해 발생
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3857

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입