BI 도구를 바꾼 뒤로 월 상품 판매 리포트가 판매 2만 건마다 판매상품을 찾아간다

중급SELECT유통619 / 700

상황

본사는 올해 BI 도구를 바꾸면서 날짜 조건을 모두 달력 차원(CF_CAL)으로 거르게 의미 계층을 새로 만들었다. 사용자가 「연월」을 고르면 BI가 달력 테이블을 조인하고 YM 조건을 리터럴로 거는 SQL을 만든다. 옛 도구에서 0.06초였던 「월 상품 판매」 리포트가 0.3초씩 걸린다. MD 20명이 하루 수백 번 연다. 아래는 2023년 6월을 골랐을 때다. BI 관리자는 리포트마다 SQL을 직접 고쳐 넣을 수 있다.

테이블과 인덱스

CF_CAL 달력(이 문제용 · BI 날짜 차원): 1950-01-01 ~ 2049-12-31 36,525행 · 176블록 · YMD VARCHAR2(8) PK(NUM_DISTINCT 36,525) · YM VARCHAR2(6)(NUM_DISTINCT 1,200) · DOW_NM · HOLI_YN
CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) '20220101' ~ '20231231'(NUM_DISTINCT 730, 판매가 없는 날은 없다) · 날짜순 적재 · 2023년 6월 20,953건 · IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393 · CF 9,921
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 판매 한 건에 평균 2.19줄 · 2023년 6월 판매상품 45,957줄
통계 최신 · 히스토그램 없음

원본 SQL

sql

SELECT s.ITM_NO, SUM(s.SLE_QTY) AS qty, SUM(s.SLE_QTY * s.SLE_UPRC) AS amt
  FROM CF_CAL c, CF10 h, CF11 s
 WHERE c.YM = '202306'
   AND h.SLE_DT = c.YMD
   AND s.SLE_NO = h.SLE_NO
 GROUP BY s.ITM_NO

실행계획 학습용 예시 · 판매일자 범위의 추정 5,177은 교재 10화 측정

xplan

Plan hash value: 2504718839

------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |     88 |00:00:00.31 |   63575 |
|   1 |  HASH GROUP BY                         |                |      1 |     88 |     88 |00:00:00.31 |   63575 |
|   2 |   NESTED LOOPS                         |                |      1 |    913 |  45957 |00:00:00.30 |   63575 |
|   3 |    NESTED LOOPS                        |                |      1 |    913 |  45957 |00:00:00.21 |   42622 |
|   4 |     NESTED LOOPS                       |                |      1 |    417 |  20953 |00:00:00.02 |     716 |
|*  5 |      TABLE ACCESS FULL                 | CF_CAL         |      1 |     30 |     30 |00:00:00.01 |     176 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| CF10          |     30 |    685 |  20953 |00:00:00.02 |     540 |
|*  7 |       INDEX RANGE SCAN                 | IX_CF10_SLE_DT |     30 |    685 |  20953 |00:00:00.01 |     120 |
|*  8 |     INDEX RANGE SCAN                   | CF11_PK        |  20953 |      2 |  45957 |00:00:00.12 |   41906 |
|   9 |    TABLE ACCESS BY INDEX ROWID         | CF11           |  45957 |      1 |  45957 |00:00:00.07 |   20953 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("C"."YM"='202306')
   7 - access("H"."SLE_DT"="C"."YMD")
   8 - access("S"."SLE_NO"="H"."SLE_NO")

문제

4번 줄의 E-Rows 417을 손으로 계산하시오. 6번 줄은 날짜 하나에 685건으로 맞게 보는데 4번 줄이 그 30배가 아니라 417인 이유를 설명하고, 그 숫자 때문에 무엇을 골랐는지 쓰시오.
결과가 바뀌지 않게 SQL을 고쳐 추정을 바로잡으시오. 달력 쪽을 고치는 방법, 동적 샘플링, 힌트와도 비교해 각각의 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 날짜 조건은 달력이 아니라 판매에 직접 건다 (그 달 첫날 이상, 다음 달 첫날 미만)
-- 이 리포트는 달력의 열을 쓰지 않으므로 달력 조인을 뺀다
SELECT s.ITM_NO, SUM(s.SLE_QTY) AS qty, SUM(s.SLE_QTY * s.SLE_UPRC) AS amt
  FROM CF10 h, CF11 s
 WHERE h.SLE_DT >= '20230601'
   AND h.SLE_DT <  '20230701'
   AND s.SLE_NO = h.SLE_NO
 GROUP BY s.ITM_NO;

-- 달력의 열(요일 이름 등)이 필요한 리포트라면 달력은 조건 없이 붙인다
--   ... FROM CF10 h, CF11 s, CF_CAL c
--      WHERE h.SLE_DT >= '20230601' AND h.SLE_DT < '20230701'
--        AND c.YMD = h.SLE_DT AND s.SLE_NO = h.SLE_NO      (c.YM 조건은 걸지 않는다)

개선 후 실행계획 학습용 예시 · 판매일자 범위의 추정 5,177은 교재 10화 측정

xplan

Plan hash value: 1189734502

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |     88 |00:00:00.06 |    8680 |          |
|   1 |  HASH GROUP BY                       |                |      1 |     88 |     88 |00:00:00.06 |    8680 |          |
|*  2 |   HASH JOIN                          |                |      1 |  11353 |  45957 |00:00:00.05 |    8680 | 1610K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10          |      1 |   5177 |  20953 |00:00:00.01 |     476 |          |
|*  4 |     INDEX RANGE SCAN                 | IX_CF10_SLE_DT |      1 |   5177 |  20953 |00:00:00.01 |      60 |          |
|   5 |    TABLE ACCESS FULL                 | CF11           |      1 |   1096K|   1096K|00:00:00.02 |    8204 |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."SLE_NO"="H"."SLE_NO")
   4 - access("H"."SLE_DT">='20230601' AND "H"."SLE_DT"<'20230701')

모범 답안 서술

417의 출처: 5번 줄의 달력은 YM = '202306' 하나로 36,525 / 1,200 = 30.4행이다(실제 30). 판매와 조인하면 조인 카디널리티는 30.4 × 500,000 / MAX(730, 36,525) = 416.7, 곧 417이다. 분모가 판매의 날짜 종류(730)가 아니라 달력의 날짜 종류 36,525로 나누어지기 때문이다. 옵티마이저는 날짜 종류가 적은 쪽(판매 730일)의 값이 모두 많은 쪽(달력)에 있다고 가정하고, 달력 한 행이 판매와 맞는 몫을 500,000 / 36,525 = 13.7건으로 본다. 그러나 달력의 날짜 3만 6천여 개 가운데 판매가 있는 날은 730일뿐이고, 고른 6월의 30일은 모두 판매가 있는 날이다. 그래서 실제는 30 × 685에 가까운 20,953건이다. 6번 줄의 E-Rows 685(500,000 / 730)는 날짜 하나를 인덱스로 찾을 때의 추정이라 맞고, 조인 결과인 4번 줄만 50배 작게 봤다. 그 숫자로 고른 것: 판매 417건이라면 판매마다 CF11_PK로 판매상품을 찾아가는 NL이 판매상품을 통째로 읽는 해시 조인보다 싸다. 실제로는 8번 줄의 Starts가 20,953이라 CF11_PK와 판매상품 테이블에서만 62,859블록을 읽었다(전체 63,575). 고침: 날짜 조건을 달력이 아니라 판매에 직접 건다. 이 리포트는 달력의 열을 쓰지 않으므로 달력 조인을 뺀다. 그러면 SLE_DT의 통계로 셈해 5,177건으로 보고, 판매상품을 한 번 읽는 해시 조인을 고른다. 5,177도 문자 날짜를 수직선 위에서 셈한 값이라 실제의 4분의 1이지만, NL과 해시를 가르는 문턱(수백 건)보다 훨씬 커서 계획은 맞다. Buffers는 63,575 → 8,680이다. BI 관리자는 「연월」 객체를 판매일자 범위로 바꿔 다른 리포트도 같은 모양을 쓰게 한다. 함정: 판매일자 조건을 더하면서 달력의 YM 조건을 그대로 남기면 5,177 × 30.4 / 36,525 = 4.3, 곧 4건으로 더 작게 본다. 두 테이블에 각각 건 조건이 다시 곱해지기 때문이다. 요일 이름처럼 달력의 열이 필요하면 달력은 조건 없이 조인해서 판매 쪽 건수가 그대로 이어지게 한다. 다른 방법과 대가: ① 달력을 업무 기간(2022 ~ 2024년, 1,096행)만 남긴 테이블로 바꾸면 분모가 1,096이 되어 13,889건으로 맞아진다. 하지만 해마다 늘려 줘야 하고, 생년월일처럼 먼 날짜를 쓰는 다른 리포트가 깨진다. 같은 달력에 조건만 더한 뷰는 분모(달력의 NDV)를 바꾸지 못한다. ② 테이블 하나의 조건 건수를 표본으로 세는 동적 샘플링(DYNAMIC_SAMPLING 힌트)은 이미 맞는 달력 30행을 다시 셀 뿐 조인 선택도는 그대로다. 12c부터의 자동 동적 통계(레벨 11)는 조인 결과까지 표본으로 볼 수 있지만, 하드 파싱마다 표본 질의가 더 돌고 시스템 설정이라 다른 SQL에도 퍼진다. ③ USE_HASH 힌트는 BI가 만드는 SQL마다 넣어야 하고, 그날 규모에 맞춘 판단을 굳힌다.

해설

같은 일이 코드 테이블에서도 생긴다. 공통코드처럼 여러 분류의 코드를 한 테이블에 담고 이름으로 거른 뒤 조인하면, 코드 열의 NDV가 다른 분류의 코드까지 세어 커지고 조인 추정이 무너진다. 거르는 조건이 '키를 많이 가진 쪽'에 걸리고 그 키 대부분이 상대 테이블에 없을 때 생기는 일이다.
히스토그램이나 컬럼 그룹으로는 고쳐지지 않는다. 달력 안의 분포는 이미 고르고 정확하다(한 달에 30일). 틀린 것은 '달력의 날짜가 모두 판매에도 있다'는 두 테이블 사이의 가정이고, 한 테이블의 통계는 그 가정을 바꾸지 못한다.
6번 줄과 4번 줄을 함께 읽는다. NL 안쪽 줄의 E-Rows는 한 번 찾을 때의 추정(685)이고, 조인 줄의 E-Rows는 조인 공식의 결과(417)다. 둘을 곱해 보면(30 × 685 = 20,550) 어느 줄의 추정이 무너졌는지 바로 보인다(교재 10화 §1의 추적 순서).
판매일자 범위의 추정 5,177이 실제의 4분의 1인 이유와 SLE_DT 히스토그램으로 맞추는 방법은 교재 10화 §12에 있다. 이 리포트는 계획이 이미 맞으므로 거기까지 가지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 판매일자 범위의 추정 5,177은 교재 10화 측정 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 판매일자 범위의 추정 5,177은 교재 10화 측정 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황