JOIN-027

회계 감사용 2013년 주문 상품 추출이 TEMP를 100MB 가까이 쓰고 8초 걸린다

중급SELECT버거177 / 700

상황

회계법인이 감사 자료로 2013년 주문 상품 내역을 파일로 달라고 했다. 요구한 칸은 주문번호, 주문일, 주문시각, 매장, 상품, 수량 여섯 개다. 개발자는 "나중에 칸을 더 달라고 할지 몰라서" 주문 헤더를 o.*로 뽑았다. 추출을 돌릴 때마다 TEMP 사용 경보가 울린다. 오과장은 "블록은 두 테이블을 한 번씩 읽은 만큼이라 정상인데, PGA를 늘려야 하나"라고 묻는다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · 한 행이 약 180바이트 (ORD_NO · ORD_DT · ORD_HMS · SHOP_NO 말고 문자열 채움 컬럼이 여럿 있다) · 2013년 500,218행
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · 2013년 주문의 상품 1,236,018줄
pga_aggregate_target 512M · AUTO · 이 시간대에 해시 조인 하나가 받을 수 있는 work area는 약 100MB · 통계 최신

원본 SQL

sql

SELECT o.*, i.ITEM_ID, i.ORD_ITEM_QTY
  FROM ORD o, ORD_ITEM i
 WHERE o.ORD_DT BETWEEN '20130101' AND '20131231'
   AND i.ORD_NO = o.ORD_NO

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |   1236K|00:00:07.93 |   77687 | 11950 |  11950 |       |       |          |         |
|*  1 |  HASH JOIN         |          |      1 |   1236K|   1236K|00:00:07.93 |   77687 | 11950 |  11950 |   131M|  9952K|   97M (1)|      94M|
|*  2 |   TABLE ACCESS FULL| ORD      |      1 |    500K|    500K|00:00:00.29 |   22422 |     0 |      0 |       |       |          |         |
|   3 |   TABLE ACCESS FULL| ORD_ITEM |      1 |   2472K|   2472K|00:00:00.61 |   55265 |     0 |      0 |       |       |          |         |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("I"."ORD_NO"="O"."ORD_NO")
   2 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))

문제

build는 작은 쪽(2013년 주문)으로 맞게 잡혔다. 그런데도 1번 줄이 onepass로 TEMP를 쓰는 이유를 build에 올라가는 것으로 설명하시오.
요구한 여섯 칸만 내도록 SQL을 고치고, 고친 뒤의 메모리와 시간을 어림하시오. PGA를 늘리자는 제안에 대한 의견도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 감사 요구는 여섯 칸이다. build(2013년 주문)에 채움 컬럼까지 올리지 않는다
SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS, o.SHOP_NO, i.ITEM_ID, i.ORD_ITEM_QTY
  FROM ORD o, ORD_ITEM i
 WHERE o.ORD_DT BETWEEN '20130101' AND '20131231'
   AND i.ORD_NO = o.ORD_NO

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

xplan

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |   1236K|00:00:02.38 |   77687 |     0 |      0 |       |       |          |         |
|*  1 |  HASH JOIN         |          |      1 |   1236K|   1236K|00:00:02.38 |   77687 |     0 |      0 |    49M|  7398K|   52M (0)|         |
|*  2 |   TABLE ACCESS FULL| ORD      |      1 |    500K|    500K|00:00:00.27 |   22422 |     0 |      0 |       |       |          |         |
|   3 |   TABLE ACCESS FULL| ORD_ITEM |      1 |   2472K|   2472K|00:00:00.58 |   55265 |     0 |      0 |       |       |          |         |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("I"."ORD_NO"="O"."ORD_NO")
   2 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))

모범 답안 서술

build는 2013년 주문 50만 행으로 맞게 잡혔다(2번 줄). 문제는 행 수가 아니라 폭이다. 해시 테이블에는 조인 키만 올라가지 않고, build 쪽에서 SELECT 절과 조인에 쓰는 컬럼이 모두 함께 올라간다. o.*이므로 ORD의 채움 컬럼까지 50만 행 × 약 180바이트가 올라가 OMem이 131M가 됐다. 이 시간대에 해시 조인 하나가 받을 수 있는 work area(약 100MB)를 넘으므로 1번 줄은 97M (1), 곧 onepass로 돌았다. 넘친 파티션과 그 짝인 probe 쪽 파티션 94M(Used-Tmp)를 TEMP에 썼다가 다시 읽었고(Reads/Writes 11,950), 7.93초의 대부분이 이 왕복에 들었다. 두 테이블을 한 번씩 읽는 것은 같아서 Buffers는 77,687로 정상처럼 보인다. 블록만 보면 이 문제가 보이지 않는다. 요구한 여섯 칸만 적으면 ORD 쪽에서 build에 올라가는 것은 ORD_NO, ORD_DT, ORD_HMS, SHOP_NO 네 컬럼(한 행 약 40바이트)뿐이다. OMem은 131M → 49M로 줄어 work area 안에 들어가고, Used-Mem 52M (0), 곧 optimal로 끝난다. TEMP는 0이 되고 A-Time은 7.93 → 2.38초로 준다. 남은 시간은 124만 줄을 파일로 내보내는 몫이다. PGA를 늘리자는 제안은 답이 아니다. 필요한 메모리는 build의 행 수와 폭에 비례하므로, o.*를 두고 메모리를 늘리면 다음 해 감사 자료가 늘 때 또 모자란다. 쓰지 않을 컬럼을 build에 올리지 않는 것이 먼저다. build를 ORD_ITEM으로 바꾸는 것도 답이 아니다. 그쪽은 124만 줄로 더 크다.

해설

"나중에 쓸지 몰라서"의 대가: SELECT *는 화면에서는 네트워크 비용으로 끝나지만, 해시 조인의 build 쪽에서는 메모리와 TEMP 비용이 된다. 정렬(ORDER BY)도 같다. 정렬 영역에는 정렬 키뿐 아니라 SELECT 절의 컬럼이 함께 올라간다 (교재 09화 §4).
onepass와 multipass: work area가 1Mem(9,952K)보다는 커서 onepass로 끝났다. 1Mem보다도 작으면 multipass가 되어 TEMP 왕복이 몇 배로 늘어난다 (교재 06화 §6).
다른 정답: 칸을 줄이고도 모자라면 연도를 반기로 나눠 두 번 추출한다. 이때는 큰 쪽(ORD_ITEM)을 두 번 FULL로 읽게 되므로 Buffers가 두 배가 된다. ORD_ITEM의 ORD_DT로 probe 쪽도 걸러 주면 그 비용을 줄일 수 있지만, 반정규화 컬럼이 헤더와 늘 같은지 먼저 확인해야 한다.
감사 파일의 정합성: 추출 뒤에는 줄 수(1,236,018)와 수량 합계를 원장과 맞춰 본다. 칸을 줄인 것은 결과의 행 수를 바꾸지 않는다 (교재 08화 §10).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황