연말연시 일주일 매장별 주문 수만 주문 테이블을 통째로 읽는다

중급SELECT버거620 / 700

상황

버거 본사 영업팀은 해마다 1월 초에 「연말연시 매장 실적」을 본다. 12월 26일부터 1월 1일까지 일주일의 매장별 주문 수다. 같은 화면에서 다른 일주일(예: 6월 10일 ~ 16일)을 넣으면 0.01초인데, 연말연시만 0.3초가 걸린다. 오과장은 이렇게 말한다. "연말이라 주문이 몰려서 그래요." 그러나 연말연시 일주일의 주문은 9,576건으로 다른 주와 같다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) '20120101' ~ '20131231'(NUM_DISTINCT 731) · 하루 약 1,368건 · 주문 일시 순으로 쌓였다
인덱스 ORD_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 3,774 · CF 22,223
통계 최신 · 히스토그램 없음

원본 SQL

sql

SELECT SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD
 WHERE ORD_DT BETWEEN '20121226' AND '20130101'
 GROUP BY SHOP_NO

실행계획 학습용 예시 · 문자 날짜의 수직선은 교재 04화 · 10화 측정과 같은 계산

xplan

[연말연시 · '20121226' ~ '20130101']
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    200 |00:00:00.29 |   22422 |          |
|   1 |  HASH GROUP BY     |      |      1 |    200 |    200 |00:00:00.29 |   22422 | 1201K (0)|
|*  2 |   TABLE ACCESS FULL| ORD  |      1 |    800K|   9576 |00:00:00.28 |   22422 |          |
-----------------------------------------------------------------------------------------------
   2 - filter(("ORD_DT">='20121226' AND "ORD_DT"<='20130101'))

[6월의 한 주 · '20130610' ~ '20130616']
--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |      1 |        |    200 |00:00:00.01 |     252 |
|   1 |  HASH GROUP BY                       |         |      1 |    200 |    200 |00:00:00.01 |     252 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |   3275 |   9576 |00:00:00.01 |     252 |
|*  3 |    INDEX RANGE SCAN                  | ORD_X01 |      1 |   3275 |   9576 |00:00:00.01 |      39 |
--------------------------------------------------------------------------------------------------------
   3 - access("ORD_DT">='20130610' AND "ORD_DT"<='20130616')

문제

두 계획의 E-Rows(800K · 3,275)를 손으로 계산하고, 같은 일주일인데 연말연시만 FULL을 고른 이유를 설명하시오.
SQL은 그대로 두고 통계로 고치시오. 그 처방의 대가와 근본적인 해결도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ORD_DT 에 히스토그램 (값 731종 > 254칸 → HYBRID) : 날짜가 실제로 어디에 몇 건 있는지 칸으로 기억한다
-- 선호도로 걸어 밤의 수집도 같은 규칙을 따르게 한다
BEGIN
  DBMS_STATS.SET_TABLE_PREFS('DEV', 'ORD', 'METHOD_OPT',
                             'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ORD_DT');
  DBMS_STATS.GATHER_TABLE_STATS('DEV', 'ORD', no_invalidate => FALSE);
END;
/

-- SQL 은 그대로
SELECT SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD
 WHERE ORD_DT BETWEEN '20121226' AND '20130101'
 GROUP BY SHOP_NO;

개선 후 실행계획 학습용 예시 · 문자 날짜의 수직선은 교재 04화 · 10화 측정과 같은 계산

xplan

--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |      1 |        |    200 |00:00:00.01 |     252 |
|   1 |  HASH GROUP BY                       |         |      1 |    200 |    200 |00:00:00.01 |     252 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |  10412 |   9576 |00:00:00.01 |     252 |
|*  3 |    INDEX RANGE SCAN                  | ORD_X01 |      1 |  10412 |   9576 |00:00:00.01 |      39 |
--------------------------------------------------------------------------------------------------------
   3 - access("ORD_DT">='20121226' AND "ORD_DT"<='20130101')

[USER_TAB_COL_STATISTICS · ORD_DT]  HISTOGRAM HYBRID · NUM_BUCKETS 254 (E-Rows 는 표본에 따라 수집마다 조금씩 다르다)

모범 답안 서술

ORD_DT는 문자지만 옵티마이저는 숫자 모양의 문자열을 수직선 위의 점처럼 계산한다. 전체 폭은 20131231 − 20120101 = 11,130칸이다. 연말연시 범위는 20130101 − 20121226 = 8,875칸인데, 그 대부분은 해가 바뀌는 하루(20121231 → 20130101, 8,870칸)가 차지한다. 그래서 8,875 / 11,130 + 2/731(닫힌 끝 둘) = 0.80, 1,000,000 × 0.80 = 800K로 봤다. 실제로는 7일치 9,576건이다. 6월의 한 주는 반대로 6칸뿐이라 6 / 11,130 + 2/731 = 0.0033, 3,275로 작게 본다. 80만 건이라면 인덱스로 찾아 테이블을 오가는 것보다 FULL이 싸므로 연말연시만 FULL(22,422블록)을 골랐다. 실제 9,576건은 ORD_X01로 범위를 읽으면 날짜순으로 쌓여 있어 252블록이면 된다. 6월의 한 주는 3배 작게 봤지만 어차피 인덱스가 맞는 쪽이라 계획에는 문제가 없었다. 오과장의 말과 달리 주문이 몰린 것이 아니라, 연말이 수직선의 큰 빈칸 위에 있었던 것이다. SQL을 고치지 않고 고치려면 ORD_DT에 히스토그램을 모은다. 값이 731종이라 254칸의 HYBRID가 되고, 칸마다 실제 건수(약 2.9일치)를 기억하므로 해가 바뀌는 빈칸이 한 칸 안에 갇힌다. E-Rows는 1만 건 안팎이 되고 인덱스를 골라 22,422 → 252블록이다. 대가도 있다. HYBRID는 표본으로 만들어 수집할 때마다 숫자가 조금씩 다르고(교재 10화 §12: 같은 달이 19,630 ~ 21,780), ORD_DT를 쓰는 모든 SQL의 추정이 바뀌므로 반영 전후로 그 SQL들의 계획 해시를 비교한다. 근본 해결은 날짜를 DATE로 두는 것이다. 열 타입을 바꾸기 어렵다면 TO_DATE(ORD_DT, 'YYYYMMDD') 날짜형 가상 열을 두고 새 SQL부터 그 열로 묻게 한다(03화).

해설

교재 04화의 같은 현상: 버거원에서 범위 끝을 '20121231'에서 '20130101'로 하루 늘리자 E-Rows가 102K → 899K로 뛰었다. 하루가 8,870칸이기 때문이다(04화 §5, 10화 §12).
범위를 해가 바뀌는 지점에서 나누는 안(BETWEEN '20121226' AND '20121231' OR ORD_DT = '20130101')도 추정을 맞춘다. 그러나 화면이 날짜를 받을 때마다 해가 바뀌는지 따져 SQL을 만들어야 하므로 유지하기 어렵다.
표현식 통계(TO_DATE(ORD_DT, 'YYYYMMDD'))는 SQL이 그 식으로 물을 때만 쓰이고, 식으로 물으면 ORD_X01을 범위로 읽지 못한다. 이 화면에는 맞지 않다.
INDEX 힌트로 이 SQL만 고정하면 화면이 1년치를 물을 때도 인덱스로 테이블을 오간다. 날짜 범위가 사용자 입력인 화면에는 통계를 고치는 편이 맞다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 문자 날짜의 수직선은 교재 04화 · 10화 측정과 같은 계산 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 문자 날짜의 수직선은 교재 04화 · 10화 측정과 같은 계산 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황