JOIN-033

본사 BI 야간 배치의 2013년 집계 세 문장 중 하나가 TEMP를 쓰고, 셋 다 같은 주문 헤더를 읽는다

실전SELECT버거183 / 700

상황

본사 BI팀의 야간 배치는 2013년 집계 세 문장(S1 상품별 판매 수량, S2 판매 수량 상위 10개 매장, S3 월별 상품 유형 판매)을 차례로 돌린다. 세 문장을 합쳐 12초 가까이 걸리고, S2를 돌릴 때마다 TEMP 경보가 울린다. S2의 힌트는 작년에 "상세가 더 많으니 상세부터 읽어야 한다"며 넣은 것이다. 오과장은 세 문장을 하나씩 보고 고칠 것과 그대로 둘 것을 숫자로 정해 달라고 했다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · 2013년 500,218행 · SHOP_NO 200곳
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · 2013년 주문의 상품 1,236,018줄 · FK ORD_ITEM.ORD_NO → ORD (ENABLE VALIDATE)
ORD_ITEM의 ORD_DT와 ORD_HMS는 POS가 주문을 넣을 때 헤더에서 복사하는 반정규화 컬럼이다. 지금 헤더와 다른 줄은 0건이지만, 이를 지키는 제약은 없다
ITEM 상품: 92행 · 5블록 · ITEM_TYPE_CD 4종 / SHOP 매장: 200행 · 5블록 · SHOP_NM · ADDR
통계 최신 · pga_aggregate_target 512M · AUTO (이 시간대 해시 조인 하나의 work area는 약 100MB)

원본 SQL

sql

-- [S1] 상품별 판매 수량
SELECT t.ITEM_ID, t.ITEM_NM, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD o, ORD_ITEM i, ITEM t
 WHERE o.ORD_DT BETWEEN '20130101' AND '20131231'
   AND i.ORD_NO  = o.ORD_NO
   AND t.ITEM_ID = i.ITEM_ID
 GROUP BY t.ITEM_ID, t.ITEM_NM;

-- [S2] 판매 수량 상위 10개 매장
SELECT *
  FROM (SELECT /*+ LEADING(i o) USE_HASH(o) */
               s.SHOP_NO, s.SHOP_NM, s.ADDR, SUM(i.ORD_ITEM_QTY) AS qty
          FROM ORD o, ORD_ITEM i, SHOP s
         WHERE o.ORD_DT BETWEEN '20130101' AND '20131231'
           AND i.ORD_NO  = o.ORD_NO
           AND s.SHOP_NO = o.SHOP_NO
         GROUP BY s.SHOP_NO, s.SHOP_NM, s.ADDR
         ORDER BY qty DESC)
 WHERE ROWNUM <= 10;

-- [S3] 월별 상품 유형 판매
SELECT SUBSTR(i.ORD_DT, 1, 6) AS ym, t.ITEM_TYPE_CD, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD_ITEM i, ITEM t
 WHERE i.ORD_DT BETWEEN '20130101' AND '20131231'
   AND t.ITEM_ID = i.ITEM_ID
 GROUP BY SUBSTR(i.ORD_DT, 1, 6), t.ITEM_TYPE_CD;

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------
[S1]
|   0 | SELECT STATEMENT          |          |      1 |        |     92 |00:00:02.87 |   77692 |     0 |      0 |       |       |          |         |
|   1 |  HASH GROUP BY            |          |      1 |     92 |     92 |00:00:02.87 |   77692 |     0 |      0 |  1186K|  1186K| 1223K (0)|         |
|*  2 |   HASH JOIN               |          |      1 |   1236K|   1236K|00:00:02.41 |   77692 |     0 |      0 |  1376K|  1376K| 1270K (0)|         |
|   3 |    TABLE ACCESS FULL      | ITEM     |      1 |     92 |     92 |00:00:00.01 |       5 |     0 |      0 |       |       |          |         |
|*  4 |    HASH JOIN              |          |      1 |   1236K|   1236K|00:00:01.98 |   77687 |     0 |      0 |    37M|  6418K|   39M (0)|         |
|*  5 |     TABLE ACCESS FULL     | ORD      |      1 |    500K|    500K|00:00:00.24 |   22422 |     0 |      0 |       |       |          |         |
|   6 |     TABLE ACCESS FULL     | ORD_ITEM |      1 |   2472K|   2472K|00:00:00.45 |   55265 |     0 |      0 |       |       |          |         |
[S2]
|   0 | SELECT STATEMENT          |          |      1 |        |     10 |00:00:07.71 |   77692 | 11203 |  11203 |       |       |          |         |
|*  1 |  COUNT STOPKEY            |          |      1 |        |     10 |00:00:07.71 |   77692 | 11203 |  11203 |       |       |          |         |
|   2 |   VIEW                    |          |      1 |    200 |     10 |00:00:07.71 |   77692 | 11203 |  11203 |       |       |          |         |
|*  3 |    SORT ORDER BY STOPKEY  |          |      1 |    200 |     10 |00:00:07.71 |   77692 | 11203 |  11203 |  2048 |  2048 | 2048  (0)|         |
|   4 |     HASH GROUP BY         |          |      1 |    200 |    200 |00:00:07.70 |   77692 | 11203 |  11203 |  1542K|  1542K| 1490K (0)|         |
|*  5 |      HASH JOIN            |          |      1 |   1236K|   1236K|00:00:07.38 |   77692 | 11203 |  11203 |  1421K|  1421K| 1290K (0)|         |
|   6 |       TABLE ACCESS FULL   | SHOP     |      1 |    200 |    200 |00:00:00.01 |       5 |     0 |      0 |       |       |          |         |
|*  7 |       HASH JOIN           |          |      1 |   1236K|   1236K|00:00:06.95 |   77687 | 11203 |  11203 |   176M|    12M|   98M (1)|      88M|
|   8 |        TABLE ACCESS FULL  | ORD_ITEM |      1 |   2472K|   2472K|00:00:00.58 |   55265 |     0 |      0 |       |       |          |         |
|*  9 |        TABLE ACCESS FULL  | ORD      |      1 |    500K|    500K|00:00:00.30 |   22422 |     0 |      0 |       |       |          |         |
[S3]
|   0 | SELECT STATEMENT          |          |      1 |        |     48 |00:00:01.12 |   55270 |     0 |      0 |       |       |          |         |
|   1 |  HASH GROUP BY            |          |      1 |     48 |     48 |00:00:01.12 |   55270 |     0 |      0 |  1186K|  1186K| 1210K (0)|         |
|*  2 |   HASH JOIN               |          |      1 |   1236K|   1236K|00:00:00.88 |   55270 |     0 |      0 |  1376K|  1376K| 1270K (0)|         |
|   3 |    TABLE ACCESS FULL      | ITEM     |      1 |     92 |     92 |00:00:00.01 |       5 |     0 |      0 |       |       |          |         |
|*  4 |    TABLE ACCESS FULL      | ORD_ITEM |      1 |   1236K|   1236K|00:00:00.47 |   55265 |     0 |      0 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  [S1] 2 - access("T"."ITEM_ID"="I"."ITEM_ID")
       4 - access("I"."ORD_NO"="O"."ORD_NO")
       5 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))
  [S2] 1 - filter(ROWNUM<=10)
       3 - filter(ROWNUM<=10)
       5 - access("S"."SHOP_NO"="O"."SHOP_NO")
       7 - access("I"."ORD_NO"="O"."ORD_NO")
       9 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))
  [S3] 2 - access("T"."ITEM_ID"="I"."ITEM_ID")
       4 - filter(("I"."ORD_DT">='20130101' AND "I"."ORD_DT"<='20131231'))

문제

세 문장 각각의 일량과 문제(있다면)를 실행계획의 줄 번호와 숫자로 설명하시오.
고칠 문장은 개선 SQL을 작성하고, 그대로 둘 문장은 그 이유를 쓰시오. 조인을 빼는 개선에는 그 전제와 확인 방법을 함께 쓰시오.
고친 뒤 배치 전체의 Buffers와 시간, TEMP가 어떻게 바뀌는지 정리하시오.

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

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

모범답안

모범 답안 SQL

sql

-- [S1] ORD 는 기간을 거르려고만 읽었다: ORD_ITEM 의 ORD_DT 로 거르고, 상품별로 먼저 묶은 뒤 상품명을 붙인다
SELECT t.ITEM_ID, t.ITEM_NM, i.qty
  FROM (SELECT ITEM_ID, SUM(ORD_ITEM_QTY) AS qty
          FROM ORD_ITEM
         WHERE ORD_DT BETWEEN '20130101' AND '20131231'
         GROUP BY ITEM_ID) i,
       ITEM t
 WHERE t.ITEM_ID = i.ITEM_ID;

-- S1 전제 점검 (0 이어야 한다): 주문일이 헤더와 다른 주문상품. 헤더 없는 줄은 FK 가 막는다
SELECT COUNT(*)
  FROM ORD o, ORD_ITEM i
 WHERE i.ORD_NO = o.ORD_NO
   AND i.ORD_DT <> o.ORD_DT;

-- [S2] build 는 작은 쪽(2013년 주문 50만)으로. 매장별로 묶어 상위 10곳을 고른 뒤 매장명 · 주소를 붙인다
SELECT s.SHOP_NO, s.SHOP_NM, s.ADDR, x.qty
  FROM (SELECT SHOP_NO, qty
          FROM (SELECT o.SHOP_NO, SUM(i.ORD_ITEM_QTY) AS qty
                  FROM ORD o, ORD_ITEM i
                 WHERE o.ORD_DT BETWEEN '20130101' AND '20131231'
                   AND i.ORD_NO = o.ORD_NO
                 GROUP BY o.SHOP_NO
                 ORDER BY qty DESC, o.SHOP_NO)
         WHERE ROWNUM <= 10) x,
       SHOP s
 WHERE s.SHOP_NO = x.SHOP_NO
 ORDER BY x.qty DESC, x.SHOP_NO;

-- [S3] 그대로 둔다

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

xplan

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------
[S1 개선]
|   0 | SELECT STATEMENT          |          |      1 |        |     92 |00:00:00.95 |   55270 |     0 |      0 |       |       |          |         |
|*  1 |  HASH JOIN                |          |      1 |     92 |     92 |00:00:00.95 |   55270 |     0 |      0 |  1376K|  1376K| 1270K (0)|         |
|   2 |   TABLE ACCESS FULL       | ITEM     |      1 |     92 |     92 |00:00:00.01 |       5 |     0 |      0 |       |       |          |         |
|   3 |   VIEW                    |          |      1 |     92 |     92 |00:00:00.95 |   55265 |     0 |      0 |       |       |          |         |
|   4 |    HASH GROUP BY          |          |      1 |     92 |     92 |00:00:00.95 |   55265 |     0 |      0 |  1186K|  1186K| 1223K (0)|         |
|*  5 |     TABLE ACCESS FULL     | ORD_ITEM |      1 |   1236K|   1236K|00:00:00.47 |   55265 |     0 |      0 |       |       |          |         |
[S2 개선]
|   0 | SELECT STATEMENT          |          |      1 |        |     10 |00:00:01.71 |   77707 |     0 |      0 |       |       |          |         |
|   1 |  SORT ORDER BY            |          |      1 |     10 |     10 |00:00:01.71 |   77707 |     0 |      0 |  2048 |  2048 | 2048  (0)|         |
|   2 |   NESTED LOOPS            |          |      1 |     10 |     10 |00:00:01.71 |   77707 |     0 |      0 |       |       |          |         |
|   3 |    NESTED LOOPS           |          |      1 |     10 |     10 |00:00:01.71 |   77697 |     0 |      0 |       |       |          |         |
|   4 |     VIEW                  |          |      1 |     10 |     10 |00:00:01.71 |   77687 |     0 |      0 |       |       |          |         |
|*  5 |      COUNT STOPKEY        |          |      1 |        |     10 |00:00:01.71 |   77687 |     0 |      0 |       |       |          |         |
|   6 |       VIEW                |          |      1 |    200 |     10 |00:00:01.71 |   77687 |     0 |      0 |       |       |          |         |
|*  7 |        SORT ORDER BY STOPKEY|        |      1 |    200 |     10 |00:00:01.71 |   77687 |     0 |      0 |  2048 |  2048 | 2048  (0)|         |
|   8 |         HASH GROUP BY     |          |      1 |    200 |    200 |00:00:01.70 |   77687 |     0 |      0 |  1542K|  1542K| 1490K (0)|         |
|*  9 |          HASH JOIN        |          |      1 |   1236K|   1236K|00:00:01.38 |   77687 |     0 |      0 |    37M|  6418K|   39M (0)|         |
|* 10 |           TABLE ACCESS FULL| ORD     |      1 |    500K|    500K|00:00:00.24 |   22422 |     0 |      0 |       |       |          |         |
|  11 |           TABLE ACCESS FULL| ORD_ITEM|      1 |   2472K|   2472K|00:00:00.45 |   55265 |     0 |      0 |       |       |          |         |
|* 12 |     INDEX UNIQUE SCAN     | SHOP_PK  |     10 |      1 |     10 |00:00:00.01 |      10 |     0 |      0 |       |       |          |         |
|  13 |    TABLE ACCESS BY INDEX ROWID| SHOP |     10 |      1 |     10 |00:00:00.01 |      10 |     0 |      0 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------

[배치 합계]  Buffers 210,654 → 188,247 · 시간 11.70 → 3.78초 · TEMP 88M → 0 · 가장 큰 work area 98M (1) → 39M (0)

모범 답안 서술

S1: 4번 줄 해시 조인이 2013년 주문 50만 행(5번 줄, ORD FULL 22,422블록)을 build로 올리고 주문상품 247만 줄을 probe로 흘린다. build가 작은 쪽이라 39M (0)으로 optimal이고, 상품(92행)은 2번 줄에서 옵티마이저가 build로 바꿔 붙였다. 계획 자체는 맞다. 다만 ORD는 기간을 거르는 데만 쓰였다. 결과에 ORD의 컬럼이 하나도 없고, 같은 기간 조건을 ORD_ITEM의 ORD_DT에도 걸 수 있다. S1 개선과 전제: ORD 조인을 빼고 ORD_ITEM.ORD_DT로 거른 뒤 상품별로 먼저 묶어(1,236,018줄 → 92행) 상품명을 붙인다. Buffers는 77,692 → 55,270이고, 50만 행 build(39M)가 사라져 2.87 → 0.95초가 된다. 이 개선의 전제는 둘이다. 첫째, 주문상품마다 헤더가 있어야 한다. FK ORD_ITEM.ORD_NO → ORD가 ENABLE VALIDATE라 이것은 보장된다. 둘째, 반정규화 컬럼 ORD_DT가 헤더와 늘 같아야 한다. 지금은 다른 줄이 0건이지만 제약이 없으므로, 주문일이 헤더와 다른 줄을 세는 점검 SQL을 배치 앞에 두고, 주문일을 정정할 때 두 테이블을 함께 고치는 규칙을 정정 프로그램에 넣는다. 0건이 아니면 S1을 원래 SQL로 돌린다. S2: LEADING(i o) USE_HASH(o) 때문에 7번 줄 해시 조인의 build가 주문상품 247만 줄(8번 줄)이 됐다. OMem 176M가 work area(약 100MB)에 들어가지 않아 98M (1), 곧 onepass로 돌았고, Used-Tmp 88M를 TEMP에 썼다가 다시 읽었다(Reads/Writes 11,203). 7.71초의 대부분이 이 왕복이고, Buffers는 77,692로 S1과 같다. "상세가 더 많으니 상세부터"는 NL의 생각이고, 해시 조인에서는 많은 쪽을 build로 올리게 될 뿐이다. S2 개선: 힌트를 지워 build를 작은 쪽인 2013년 주문 50만 행으로 되돌린다(39M (0)). 그리고 매장별로 먼저 묶고 수량 상위 10곳을 고른 뒤에 매장명과 주소를 붙인다. 원본은 조인 결과 124만 줄 하나하나에 매장명과 주소를 달고, 그 긴 키로 묶었다. 고친 SQL은 묶는 키가 SHOP_NO 하나이고, 매장은 10곳만 PK로 찾아간다. 동률인 매장 사이의 순서가 실행마다 바뀌지 않게 정렬에 SHOP_NO를 더했다. TEMP는 0이 되고 7.71 → 1.71초다. Buffers는 77,692 → 77,707로 거의 같다. S3: 이미 맞는 계획이라 그대로 둔다. ORD를 조인하지 않고 ORD_ITEM의 ORD_DT로 걸렀고, 작은 ITEM(92행)이 build다(1,270K (0)). Buffers 55,270은 ORD_ITEM을 한 번 읽는 몫이라 이 SQL로는 더 줄일 수 없다. 상품 유형으로 묶기 전에 상품과 월로 먼저 묶으면 CPU가 조금 줄지만, 1.12초짜리 배치 문장에서 얻는 것이 작아 손대지 않았다. 배치 전체: Buffers 210,654 → 188,247, 시간 11.70 → 3.78초, TEMP 88M → 0이다. 가장 큰 work area는 98M (1)에서 39M (0)으로 내려와 같은 시간대의 다른 배치와 PGA를 다투지 않는다.

해설

고치지 않는 판단도 답이다: S3처럼 이미 작은 쪽이 build이고 큰 테이블을 한 번만 읽는 문장은 손대지 않는다. 의견서에는 "그대로 둔다"와 그 근거(Buffers가 테이블 한 번 읽기와 같고, 등급이 optimal)를 함께 적는다.
반정규화 컬럼으로 조인 빼기: 옵티마이저도 FK가 있고 부모 테이블의 컬럼을 쓰지 않으면 조인을 스스로 뺀다(Join Elimination). S1은 부모의 ORD_DT로 거르고 있어서 그 조건이 아니다. 사람이 ORD_ITEM.ORD_DT로 바꿔야 하고, 그러면 두 컬럼이 같다는 업무 규칙이 결과를 지킨다. 그래서 점검 SQL과 정정 규칙까지가 개선안이다 (JOIN-020의 CHECK 제약 논의와 같다).
TOP-N 뒤에 붙이기: 매장명과 주소처럼 결과를 꾸미는 컬럼은 묶고 자른 뒤에 붙인다. 붙일 쪽이 키마다 한 행(PK)이면 결과는 같고, 조인과 GROUP BY에 대는 행과 키가 줄어든다 (교재 09화 §2).
다른 정답: S2에서 LEADING(o i) USE_HASH(i)처럼 순서를 바로 적어도 된다. 힌트를 남긴다면 '2013년 주문 50만 행, 주문상품 124만 줄'이라는 판단 근거를 힌트 유효기간 대장에 적는다 (교재 06화 §8).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황