JOIN-021

탈퇴 고객 주문까지 세는 「등급별 주문 상태」 집계가 해시 조인 하나에 77MB를 쓴다

기초SELECT한빛유통171 / 700

상황

경영지원팀의 「등급별 주문 상태」 리포트는 3년 치 주문을 고객 등급과 주문 상태로 묶는다. 다음 달부터 탈퇴 고객을 M_CUS에서 지우기로 해서, 개발자가 마스터에 없는 고객의 주문도 '탈퇴'로 세도록 외부 조인으로 바꿨다. 이 리포트는 매월 1일 06시 배치에서 다른 리포트 여섯 개와 함께 돈다. DBA가 V$SQL_WORKAREA에서 이 SQL의 해시 조인 하나가 77MB를 쓰는 것을 보고 물어 왔다. 개발자는 "외부 조인은 보존할 쪽(주문)부터 읽어야 해서 LEADING을 그렇게 줬다"라고 한다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 인덱스 없음 · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2%
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD A 1% · B 9% · C 90%
지금은 마스터에 없는 고객의 주문이 0건이다 · 통계 최신 · pga_aggregate_target 512M · workarea_size_policy AUTO

원본 SQL

sql

SELECT /*+ LEADING(o c) USE_HASH(c) */
       NVL(c.CUS_GD, '탈퇴') AS cus_gd, o.ORD_ST,
       COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID(+) = o.CUS_ID
 GROUP BY NVL(c.CUS_GD, '탈퇴'), o.ORD_ST

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

xplan

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |      9 |00:00:00.62 |    5856 |       |       |          |
|   1 |  HASH GROUP BY      |           |      1 |      9 |      9 |00:00:00.62 |    5856 |  1116K|  1116K|  764K (0)|
|*  2 |   HASH JOIN OUTER   |           |      1 |   1000K|   1000K|00:00:00.44 |    5856 |    61M|  8436K|   77M (0)|
|   3 |    TABLE ACCESS FULL| T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5826 |       |       |          |
|   4 |    TABLE ACCESS FULL| M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUS_ID"(+)="O"."CUS_ID")

문제

2번 줄의 해시 조인에서 어느 쪽이 build인지 찾고, Buffers는 두 테이블을 한 번씩 읽은 만큼인데 메모리가 77MB나 드는 이유를 설명하시오.
외부 조인의 결과('탈퇴' 포함)는 그대로 두고 M_CUS가 build가 되도록 SQL을 고치시오. "외부 조인은 보존할 쪽부터 읽어야 한다"는 말이 해시 조인에서도 맞는지 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 조인 순서 힌트는 두고 build 만 M_CUS 로 바꾼다 → HASH JOIN RIGHT OUTER (외부 조인의 뜻은 그대로)
SELECT /*+ LEADING(o c) USE_HASH(c) SWAP_JOIN_INPUTS(c) */
       NVL(c.CUS_GD, '탈퇴') AS cus_gd, o.ORD_ST,
       COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID(+) = o.CUS_ID
 GROUP BY NVL(c.CUS_GD, '탈퇴'), o.ORD_ST
-- 힌트를 모두 떼도 통계가 맞으면 옵티마이저가 같은 계획을 고른다

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

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |      1 |        |      9 |00:00:00.47 |    5856 |       |       |          |
|   1 |  HASH GROUP BY          |           |      1 |      9 |      9 |00:00:00.47 |    5856 |  1116K|  1116K|  764K (0)|
|*  2 |   HASH JOIN RIGHT OUTER |           |      1 |   1000K|   1000K|00:00:00.31 |    5856 |  1423K|  1423K| 1679K (0)|
|   3 |    TABLE ACCESS FULL    | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |       |       |          |
|   4 |    TABLE ACCESS FULL    | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5826 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUS_ID"(+)="O"."CUS_ID")

모범 답안 서술

2번 줄 HASH JOIN OUTER의 첫 번째 자식(3번 줄)이 build다. LEADING(o c) 때문에 build는 주문 T_ORD_BIG 100만 행이 됐고, 이 100만 행이 PGA에 해시 테이블로 올라갔다. 해시 테이블의 크기는 build의 행 수에 비례하므로 OMem 61M, Used-Mem 77M에 이르렀다. 고객 2,000명은 probe로 흘려보냈을 뿐이다. 두 테이블을 한 번씩 읽는 것은 어느 쪽이 build든 같아서 Buffers는 5,856으로 그대로다. 차이는 메모리에서만 난다. 리포트 일곱 개가 함께 도는 배치에서는 이 SQL 하나가 work area를 크게 차지하고, 메모리가 모자라는 날에는 같은 계획이 TEMP로 넘친다. "외부 조인은 보존할 쪽부터 읽는다"는 바깥 한 행마다 안쪽을 찾아가는 NL 조인의 규칙이다. 해시 조인은 inner 쪽(M_CUS)을 build로 올리고 보존할 쪽(주문)을 probe로 흘리는 HASH JOIN RIGHT OUTER를 할 수 있다. probe 쪽 주문이 짝을 못 찾으면 고객 컬럼을 NULL로 채워 내보내므로, 외부 조인의 뜻은 바뀌지 않는다. 그래서 조인 순서 힌트는 두고 SWAP_JOIN_INPUTS(c)를 더해 M_CUS를 build로 바꾼다. 결과는 그대로 9줄이고, 마스터에 없는 고객의 주문도 '탈퇴'로 세어진다. Used-Mem은 77M (0) → 1,679K (0)으로 약 46분의 1이 되고, A-Time은 0.62 → 0.47초로 준다.

해설

build는 작은 쪽: 06화에서 같은 두 테이블을 LEADING(o c)와 LEADING(c o)로 재 보면 Buffers는 같고 메모리만 45배 차이가 났다. 외부 조인에서도 원리는 같다 (교재 06화 §4).
다른 정답: 힌트를 모두 떼면 통계가 맞는 한 옵티마이저가 스스로 HASH JOIN RIGHT OUTER를 고른다. LEADING(c o) USE_HASH(o)로 써도 같은 계획이 된다. 힌트를 남긴다면 '고객 2,000명, 주문 100만 건'이라는 판단의 근거를 힌트 유효기간 대장에 적는다 (교재 06화 §8).
NL이라면: 외부 조인을 NL로 풀 때는 보존할 쪽이 반드시 바깥(드라이빙)이어야 한다. 이 제약 때문에 "보존할 쪽부터"라는 말이 생겼고, 해시 조인에는 그대로 옮겨 오지 않는다.
함정: 탈퇴 처리를 위해 외부 조인을 넣었다면, 등급 조건 같은 M_CUS 쪽 조건을 WHERE에 더할 때 (+)를 빠뜨리지 않는다. 빠뜨리면 '탈퇴' 줄이 사라진다 (JOIN-005).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황