분기 가맹점 집계가 지난주부터 3,253블록에서 22,239블록을 읽는다

기초SELECT버거104 / 700

상황

1년 전 운영본 ORD_RANDOM의 월마감 일(ACC-002) 뒤로, 버거원 개발팀에는 이런 표준이 생겼다. "주문 테이블의 기간 조회가 2주를 넘으면 FULL 힌트를 넣는다(손익분기 16일)." 본사 분석팀의 「분기 가맹점별 주문 건수와 영업일 수」는 운영에 부하를 주지 않으려고 이관 전 보관본 ORD에서 돈다. 지난주 표준 점검에서 이 SQL에도 FULL 힌트가 들어갔는데, 그 뒤로 한 번에 0.02초이던 리포트가 0.1초 가까이 걸린다. 아래는 2012년 1분기(1 ~ 3월)다.

테이블과 인덱스

ORD(보관본): 1,000,000행 · 22,422블록 · 주문 시각 순으로 쌓였다 · 2012년 1분기 125,027건(12.5%) · SHOP_NO 200개
인덱스 ORD_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 3,774 · CLUSTERING_FACTOR 22,223 · 통계 최신
참고: 표준의 근거가 된 ORD_RANDOM(운영본)은 같은 정의의 인덱스 CF가 999,958이다

원본 SQL

sql

SELECT /*+ FULL(o) */
       o.SHOP_NO, COUNT(*) AS ord_cnt, COUNT(DISTINCT o.ORD_DT) AS sale_days
  FROM ORD o
 WHERE o.ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY o.SHOP_NO

실행계획 실측 · 교재 04화 (ORD 91일 범위)

xplan

----------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    200 |00:00:00.09 |   22239 |
|   1 |  HASH GROUP BY     |      |      1 |    200 |    200 |00:00:00.09 |   22239 |
|*  2 |   TABLE ACCESS FULL| ORD  |      1 |  22050 |   125K |00:00:00.07 |   22239 |
----------------------------------------------------------------------------------------
   2 - filter(("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<=:V_TO))

[힌트를 넣기 전 · 같은 범위]  TABLE ACCESS BY INDEX ROWID BATCHED + INDEX RANGE SCAN ORD_X01 · Buffers 3,253

문제

운영본에서 만든 '2주' 규칙이 보관본 ORD에서는 왜 맞지 않는지, 두 테이블의 CLUSTERING_FACTOR와 손익분기 계산으로 설명하시오.
SQL을 고치고, 표준을 어떻게 바꿔야 하는지 설명하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- FULL 힌트를 뗀다 — 이 테이블의 손익분기는 85.5% · 한 분기(12.5%)는 인덱스가 싸다
SELECT o.SHOP_NO, COUNT(*) AS ord_cnt, COUNT(DISTINCT o.ORD_DT) AS sale_days
  FROM ORD o
 WHERE o.ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY o.SHOP_NO;

개선 후 실행계획 실측 · 교재 04화 (ORD 91일 범위)

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |      1 |        |    200 |00:00:00.02 |    3253 |
|   1 |  HASH GROUP BY                       |         |      1 |    200 |    200 |00:00:00.02 |    3253 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |  22050 |   125K |00:00:00.02 |    3253 |
|*  3 |    INDEX RANGE SCAN                  | ORD_X01 |      1 |  22050 |   125K |00:00:00.01 |     474 |
-------------------------------------------------------------------------------------------------------------
   3 - access("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<=:V_TO)

모범 답안 서술

'2주' 규칙은 운영본 ORD_RANDOM의 손익분기에서 나왔다. 손익분기 선택도는 (리프 + CF) × s = 테이블 블록 수로 푼다. ORD_RANDOM은 (3,774 + 999,958) × s = 22,234에서 s ≈ 2.2%, 16 ~ 17일이다. 행마다 다른 블록에 있어서(CF가 행 수와 비슷) 조금만 넓어져도 FULL이 이긴다. 보관본 ORD는 주문 시각 순으로 쌓여서 CF가 22,223, 곧 테이블 블록 수와 비슷하다. 같은 식으로 (리프 3,774 + CF 22,223) × s = 22,239를 풀면 s ≈ 85.5%다. 한 블록에 같은 날짜의 주문 약 45건이 모여 있어 인덱스로 읽어도 블록을 거의 한 번씩만 방문하기 때문이다. 한 분기(12.5%)는 손익분기보다 훨씬 작으므로 인덱스가 싸다. FULL 힌트를 떼면 옵티마이저가 ORD_X01을 고른다. 3번 줄 인덱스 474블록과 테이블 약 2,779블록으로 Buffers가 22,239 → 3,253이 된다(교재 04화 실측, 91일). 잘 정렬된 테이블은 이웃 블록을 미리 읽어 오므로 디스크 읽기 요청 수도 적다. 표준은 '며칠이 넘으면 FULL'이 아니라 '테이블마다 CF로 손익분기를 구해 적는다'로 바꾼다. 같은 100만 행, 같은 인덱스 정의라도 손익분기가 2.2%와 85.5%로 39배 차이 난다. 힌트를 넣기 전에는 그 테이블의 CF와 블록 수를 확인하고, 힌트 대장에 계산한 손익분기를 함께 적는다.

해설

함정: 운영본과 보관본은 컬럼, 행 수, 인덱스 정의가 모두 같아서 같은 규칙을 써도 될 것처럼 보인다. 다른 것은 쌓인 순서 하나이고, 그 차이가 CF로 나타난다(교재 04화 §1).
옵티마이저도 손익분기를 대체로 안다. ORD에서는 50 ~ 63% 사이에서 FULL로 갈아탔다. ORD_DT가 문자라서 E-Rows가 틀리는 탓에 정확한 지점은 다르다(교재 04화 §5). 힌트가 없으면 이 정도 오차는 옵티마이저가 견딘다.
이 리포트는 ORD_DT와 SHOP_NO만 쓰므로 (ORD_DT, SHOP_NO) 커버링 인덱스를 두면 약 470블록까지 준다. 분석팀이 하루 몇 번 돌리는 보관본 리포트라 그 인덱스의 값어치는 작다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 04화 (ORD 91일 범위) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 04화 (ORD 91일 범위) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황