JOIN-081

관심 고객 묶음을 1,000명까지 올릴 수 있게 바꾼 뒤 「묶음 고객 최근 주문」이 읽은 블록 수 상위에 올라왔다

중급SELECT한빛유통231 / 700

상황

상담 화면의 「묶음 고객 최근 주문」은 상담원이 고른 관심 고객 묶음의 고객마다 최근 30일 주문 건수와 금액, 마지막 주문일을 보여 준다. 화면이 고른 고객 번호를 임시 테이블 W_CUS_PICK에 넣은 뒤 아래 SQL을 돌린다. 지금까지 묶음은 30명이 상한이었다. 지난주부터 엑셀로 고객을 1,000명까지 올릴 수 있게 바뀌었고, 그 뒤 이 SQL이 읽은 블록 수 상위에 올라왔다. 오과장이 묶음 크기별로 지금 힌트 판과 해시 판을 재어 왔다. 박팀장은 "힌트를 뗄지 둘지, 언제 다시 볼지 숫자로 정하라"라고 했다.

테이블과 인덱스

W_CUS_PICK: 세션별 임시 테이블(GLOBAL TEMPORARY) · CUS_ID 한 칸 · 통계 없음(동적 샘플링으로 건수를 본다) · 묶음 크기 1 ~ 1,000명
T_ORD_BIG: 1,000,000행 · 5,826블록 · 고객당 500건 · 최근 30일은 고객당 약 13.7건(전체 27,397건)
인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT) · T_ORD_BIG은 적재 순서가 고객과 무관하다(한 고객의 주문이 여러 블록에 흩어져 있다)
화면은 묶음을 바꿀 때마다 같은 SQL 문장을 다시 실행한다(커서를 공유한다)

원본 SQL

sql

SELECT /*+ LEADING(w o) USE_NL(o) INDEX(o IX_ORD_BIG_CUS_DT) */
       o.CUS_ID, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt, MAX(o.ORD_DT) AS last_dt
  FROM W_CUS_PICK w, T_ORD_BIG o
 WHERE o.CUS_ID = w.CUS_ID
   AND o.ORD_DT >= :v_from
 GROUP BY o.CUS_ID

실행계획 학습용 예시

xplan

[측정표 · 같은 날 묶음 크기별 Buffers (오과장)]
 묶음 크기 N            10        30       100       300     1,000
 지금 힌트 (NL)        168       502     1,671     5,012    16,703
 해시 (FULL)         5,827     5,827     5,827     5,828     5,829

[지금 · N = 1,000]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |   1000 |00:00:00.21 |   16703 |          |
|   1 |  HASH GROUP BY                |                   |      1 |     29 |   1000 |00:00:00.21 |   16703 | 1380K (0)|
|   2 |   NESTED LOOPS                |                   |      1 |    397 |  13700 |00:00:00.20 |   16703 |          |
|   3 |    NESTED LOOPS               |                   |      1 |    397 |  13700 |00:00:00.04 |    3003 |          |
|   4 |     TABLE ACCESS FULL         | W_CUS_PICK        |      1 |     29 |   1000 |00:00:00.01 |       3 |          |
|*  5 |     INDEX RANGE SCAN          | IX_ORD_BIG_CUS_DT |   1000 |     14 |  13700 |00:00:00.03 |    3000 |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| T_ORD_BIG         |  13700 |     14 |  13700 |00:00:00.15 |   13700 |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("O"."CUS_ID"="W"."CUS_ID" AND "O"."ORD_DT">=:V_FROM)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

문제

측정표로 NL과 해시의 비용식을 세우고, 손익분기점이 되는 묶음 크기를 구하시오. 지금 힌트가 몇 명까지 맞는지도 쓰시오.
묶음이 1명이든 1,000명이든 맞는 계획이 돌도록 SQL을 고치시오. 힌트를 떼기만 하면 왜 모자란지 실행계획의 E-Rows로 설명하시오.
힌트 유효기간 대장에 남길 다음 재검토 조건을 숫자로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 화면이 묶음 크기(W_CUS_PICK 건수)를 세어 둘 중 하나를 연다. 문장이 다르므로 커서도 따로 생긴다
-- [Q-S] 300명 이하
SELECT /*+ LEADING(w o) USE_NL(o) INDEX(o IX_ORD_BIG_CUS_DT) */
       o.CUS_ID, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt, MAX(o.ORD_DT) AS last_dt
  FROM W_CUS_PICK w, T_ORD_BIG o
 WHERE o.CUS_ID = w.CUS_ID
   AND o.ORD_DT >= :v_from
 GROUP BY o.CUS_ID;

-- [Q-L] 300명 초과
SELECT /*+ LEADING(w o) USE_HASH(o) FULL(o) */
       o.CUS_ID, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt, MAX(o.ORD_DT) AS last_dt
  FROM W_CUS_PICK w, T_ORD_BIG o
 WHERE o.CUS_ID = w.CUS_ID
   AND o.ORD_DT >= :v_from
 GROUP BY o.CUS_ID

개선 후 실행계획 학습용 예시

xplan

[Q-L · N = 1,000]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |   1000 |00:00:00.12 |    5829 |          |
|   1 |  HASH GROUP BY                |                   |      1 |   1000 |   1000 |00:00:00.12 |    5829 | 1380K (0)|
|*  2 |   HASH JOIN                   |                   |      1 |  13700 |  13700 |00:00:00.11 |    5829 | 1520K (0)|
|   3 |    TABLE ACCESS FULL          | W_CUS_PICK        |      1 |   1000 |   1000 |00:00:00.01 |       3 |          |
|*  4 |    TABLE ACCESS FULL          | T_ORD_BIG         |      1 |  27400 |  27397 |00:00:00.08 |    5826 |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUS_ID"="W"."CUS_ID")
   4 - filter("O"."ORD_DT">=:V_FROM)

모범 답안 서술

측정표를 두 식으로 읽는다. NL은 묶음 고객 한 명마다 IX_ORD_BIG_CUS_DT 약 3블록과 최근 30일 주문 약 13.7건의 테이블 블록을 읽어 약 16.7블록씩 늘어난다(NL ≈ 16.7 × N). 해시는 T_ORD_BIG을 한 번 FULL로 읽는 약 5,830블록으로 N과 상관없이 거의 일정하다. 16.7 × N = 5,830에서 손익분기점은 약 349명이다. 지금 힌트(NL)는 300명 묶음(5,012 대 5,828)까지는 맞고, 1,000명 묶음에서는 해시보다 약 2.9배(16,703 대 5,829) 더 읽는다. 힌트만 떼서는 모자란다. 지금 계획의 4번 줄 E-Rows가 29인데 실제는 1,000명이다. 이 SQL은 묶음이 바뀌어도 문장이 같아서 커서를 공유하고, 동적 샘플링은 처음 하드 파싱할 때 한 번만 돈다. 처음 파싱할 때 묶음이 29명이었으므로 그 뒤 1,000명이 와도 29명 기준 계획을 그대로 쓴다. 힌트를 떼도 처음 파싱한 묶음 크기가 모든 실행의 계획을 정한다. 그래서 문장을 둘로 나눈다. 화면이 W_CUS_PICK 건수를 센 뒤, 300명 이하면 지금 NL 판(Q-S)을, 300명을 넘으면 USE_HASH(o) FULL(o) 판(Q-L)을 연다. 기준을 분기점 349명보다 조금 앞인 300명으로 둔 것은 두 판의 차이가 작은 구간에서 NL 쪽에 여유를 주기 위해서다. 문장이 다르므로 커서도 따로 생긴다. 1,000명 묶음은 Buffers 16,703 → 5,829로 준다. 30명 이하 묶음은 지금처럼 502블록 이하다. 대장에는 두 판을 한 줄씩 올리고 전제와 재검토 조건을 숫자로 적는다. 분기점은 해시의 상수항(T_ORD_BIG 5,826블록)과 NL의 기울기(고객당 30일 주문 약 13.7건)로 정해지므로, 「T_ORD_BIG이 8,000블록을 넘거나 4,000블록 아래로 줄 때, 고객당 30일 주문이 20건을 넘을 때, 묶음 상한이 1,000명에서 바뀔 때, 또는 6개월」에 분기점을 다시 잰다.

해설

문장을 나누는 대가: 화면에 분기 코드가 생기고 관리할 SQL이 둘이 된다. 대신 두 판 모두 자기 구간에서 맞는 계획을 지킨다. 분기 기준(300명)은 화면 코드의 상수이므로 대장의 재검토 조건이 걸리면 코드도 함께 고친다.
힌트를 떼고 맡기는 길이 모자란 이유: 동적 샘플링도, 12c 이상의 적응형 계획도 처음 실행할 때 정한 계획을 커서에 남긴다. 묶음 크기가 실행마다 크게 달라지는 SQL에는 크기별로 문장을 나누는 것이 확실하다.
분기점은 이 조인의 숫자다: 06화의 21건은 M_CUS 30블록을 상수항으로 한 값이고, 여기서는 T_ORD_BIG 5,826블록이 상수항이라 약 349명이다. 상수항(안쪽을 한 번 읽는 값)과 기울기(바깥 한 건의 일)를 재면 어떤 조인이든 분기점을 구할 수 있다 (교재 06화 §5).
1,000명 묶음에 해시를 쓰면 묶음과 상관없는 고객의 30일 주문(27,397건)까지 읽고 버린다. 묶음이 수천 명으로 커지는 요구가 오면 고객별 30일 요약 테이블을 검토한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황