PART-048

2013년 시간대별 메뉴 판매 집계가 두 테이블 모두 12칸만 읽는데도 TEMP를 22MB 쓴다

기초SELECT버거423 / 700

상황

버거 본사 메뉴개발팀은 해마다 1월에 지난해 「시간대별 메뉴 판매」를 뽑아 아침 메뉴 개편에 쓴다. 지난해 조민우가 주문상품 쪽에도 연도 조건을 넣어 두 테이블 모두 그해 12칸만 읽게 고쳤다. 올해(2014년 1월)도 3초 가까이 걸린다. 실행계획을 보니 칸은 12개씩만 열었는데, 해시 조인이 TEMP를 22MB 썼다.

테이블과 인덱스

ORD_RANGE 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_YM) 월별 25칸(P201201 ~ P201312과 빈 P_DEFAULT). 2013년은 13 ~ 24번째 칸이고 약 500,000행이다. 칸 하나 FULL ≈ 1,980블록, 12칸이면 23,760블록이다.
ORD_ITEM_RANGE 주문상품(파티션 복사본): ORD_ITEM 2,472,717행을 같은 키, 같은 경계(PARTITION BY RANGE (ORD_YM), 25칸)로 나눴다. 2013년은 약 1,236,000행이고 칸 하나 FULL ≈ 3,310블록, 12칸이면 39,720블록이다.
주문상품의 ORD_YM은 늘 그 주문의 ORD_YM과 같다(적재할 때 주문에서 가져왔다).
이 조회에 쓰이는 인덱스는 없다. 자동 PGA 관리를 쓰고, 이 세션의 해시 조인 작업 영역은 약 10MB까지 쓸 수 있다. 통계는 최신이다.

원본 SQL

sql

SELECT SUBSTR(o.ORD_HMS, 1, 2) AS hh, i.ITEM_ID,
       COUNT(*) AS line_cnt, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD_RANGE o, ORD_ITEM_RANGE i
 WHERE o.ORD_YM BETWEEN '201301' AND '201312'
   AND i.ORD_YM BETWEEN '201301' AND '201312'
   AND i.ORD_NO = o.ORD_NO
 GROUP BY SUBSTR(o.ORD_HMS, 1, 2), i.ITEM_ID
 ORDER BY hh, qty DESC

실행계획 학습용 예시 · 교재 14화 규모

xplan

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |      A-Time | Buffers | Used-Mem   | Used-Tmp | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |   1656 | 00:00:02.87 |   63480 |            |          |        |       |
|   1 |  SORT ORDER BY              |                |      1 |   1656 |   1656 | 00:00:02.87 |   63480 |  124K (0)  |          |        |       |
|   2 |   HASH GROUP BY             |                |      1 |   1656 |   1656 | 00:00:02.86 |   63480 | 1261K (0)  |          |        |       |
|*  3 |    HASH JOIN                |                |      1 |  1236K |  1236K | 00:00:02.41 |   63480 | 9883K (1)  |      22M |        |       |
|   4 |     PARTITION RANGE ITERATOR|                |      1 |   500K |   500K | 00:00:00.12 |   23760 |            |          |     13 |    24 |
|*  5 |      TABLE ACCESS FULL      | ORD_RANGE      |     12 |   500K |   500K | 00:00:00.09 |   23760 |            |          |     13 |    24 |
|   6 |     PARTITION RANGE ITERATOR|                |      1 |  1236K |  1236K | 00:00:00.31 |   39720 |            |          |     13 |    24 |
|*  7 |      TABLE ACCESS FULL      | ORD_ITEM_RANGE |     12 |  1236K |  1236K | 00:00:00.24 |   39720 |            |          |     13 |    24 |
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("I"."ORD_NO"="O"."ORD_NO")
   5 - filter(("O"."ORD_YM">='201301' AND "O"."ORD_YM"<='201312'))
   7 - filter(("I"."ORD_YM">='201301' AND "I"."ORD_YM"<='201312'))

[세션 통계]
  physical writes direct temporary tablespace  약 2,800블록 · physical reads direct temporary tablespace  약 2,800블록

문제

두 테이블 모두 12칸만 읽는데도 해시 조인이 TEMP를 쓴 까닭을 실행계획의 3번 줄과 조인 조건으로 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 두 테이블이 같은 달 칸끼리 조인하게 하고, 실행계획이 어떻게 바뀌는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 조인 조건에 파티션 키를 더한다 → 같은 달 칸끼리 짝지어 조인한다 (완전 파티션 와이즈 조인)
SELECT SUBSTR(o.ORD_HMS, 1, 2) AS hh, i.ITEM_ID,
       COUNT(*) AS line_cnt, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD_RANGE o, ORD_ITEM_RANGE i
 WHERE o.ORD_YM BETWEEN '201301' AND '201312'
   AND i.ORD_NO = o.ORD_NO
   AND i.ORD_YM = o.ORD_YM          -- i.ORD_YM 의 범위 조건은 옵티마이저가 조건 이행으로 만든다
 GROUP BY SUBSTR(o.ORD_HMS, 1, 2), i.ITEM_ID
 ORDER BY hh, qty DESC

개선 후 실행계획 학습용 예시 · 교재 14화 규모

xplan

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |      A-Time | Buffers | Used-Mem   | Used-Tmp | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |   1656 | 00:00:01.32 |   63480 |            |          |        |       |
|   1 |  SORT ORDER BY              |                |      1 |   1656 |   1656 | 00:00:01.32 |   63480 |  124K (0)  |          |        |       |
|   2 |   HASH GROUP BY             |                |      1 |   1656 |   1656 | 00:00:01.31 |   63480 | 1261K (0)  |          |        |       |
|   3 |    PARTITION RANGE ITERATOR |                |      1 |  1236K |  1236K | 00:00:00.98 |   63480 |            |          |     13 |    24 |
|*  4 |     HASH JOIN               |                |     12 |  1236K |  1236K | 00:00:00.95 |   63480 | 1694K (0)  |          |        |       |
|*  5 |      TABLE ACCESS FULL      | ORD_RANGE      |     12 |   500K |   500K | 00:00:00.09 |   23760 |            |          |     13 |    24 |
|*  6 |      TABLE ACCESS FULL      | ORD_ITEM_RANGE |     12 |  1236K |  1236K | 00:00:00.24 |   39720 |            |          |     13 |    24 |
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("I"."ORD_NO"="O"."ORD_NO" AND "I"."ORD_YM"="O"."ORD_YM")
   5 - filter(("O"."ORD_YM">='201301' AND "O"."ORD_YM"<='201312'))
   6 - filter(("I"."ORD_YM">='201301' AND "I"."ORD_YM"<='201312'))

  (Used-Mem 은 마지막 칸 한 쌍의 값 · 칸마다 주문 약 4만 2천 행만 해시 테이블에 올린다)

모범 답안 서술

두 테이블 모두 2013년 12칸만 열었다(4번과 6번 줄 PARTITION RANGE ITERATOR, Pstart 13, Pstop 24). 문제는 그다음이다. 조인 조건이 i.ORD_NO = o.ORD_NO뿐이고 파티션 키 ORD_YM이 없어서, 옵티마이저는 1월 주문의 주문상품이 1월 칸에만 있다는 것을 모른다. 그래서 3번 줄 HASH JOIN이 한 번(Starts 1) 돌며 12칸의 주문 50만 행을 한 해시 테이블에 올리고 주문상품 124만 행을 대어 봤다. 해시 테이블이 작업 영역(약 10MB)을 넘어 일부를 TEMP에 쓰고 다시 읽었다(Used-Mem 9,883K (1) 원패스, Used-Tmp 22M). 조인 조건에 i.ORD_YM = o.ORD_YM을 더하면 두 테이블이 같은 키, 같은 경계로 나뉘어 있으므로 옵티마이저가 같은 달 칸끼리 짝지어 조인한다(완전 파티션 와이즈 조인). 계획에서 PARTITION RANGE ITERATOR가 HASH JOIN 위로 올라가고, HASH JOIN의 Starts가 12가 된다. 칸 한 쌍마다 주문 약 4만 2천 행만 해시 테이블에 올리므로 작업 영역 안에서 끝나고(1,694K (0)), TEMP가 사라진다. 경과 시간은 2.87 → 1.32초로 준다. 읽는 블록은 63,480 그대로다. 파티션 와이즈 조인은 읽는 양이 아니라 한 번에 메모리에 올리는 양을 줄인다. 주문상품의 ORD_YM은 늘 그 주문의 ORD_YM과 같으므로 조건을 더해도 결과는 바뀌지 않는다. 주문상품 쪽 연도 조건은 o.ORD_YM의 범위와 새 조인 조건에서 옵티마이저가 조건 이행으로 만들어 주므로 지워도 된다.

해설

완전 파티션 와이즈 조인의 조건: 두 테이블이 조인 키로, 같은 방식과 같은 경계로 나뉘어 있어야 한다. 주문상품이 분기 칸이었다면 칸끼리 짝이 맞지 않아 이 조인이 되지 않는다.
작업 영역을 키우는 처방(PGA를 늘린다)은 증상만 덮는다. 2년 치를 뽑는 날이나 데이터가 늘어난 해에 다시 넘친다. 칸끼리 조인하면 해시 테이블 크기가 칸 크기로 묶인다.
병렬로 돌리면 칸 한 쌍이 PX 서버 하나의 일이 되어 서버끼리 행을 주고받지 않는다(PART-049).
PART-013에서는 같은 조인 조건이 '한 달만 읽게' 하는 프루닝의 열쇠였다. 여기서는 여러 달을 읽을 때 '칸끼리 짝짓는' 열쇠가 된다. 자식 테이블을 조회할 때 파티션 키를 조인 조건에 넣는 규칙 하나가 두 일을 함께 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황