JOIN-001

결과 세 줄짜리 「고객 등급별 매출」 화면이 0.8초 걸리고 블록을 100만 개 넘게 읽는다

중급SELECT한빛유통151 / 700

상황

「고객 등급별 매출」 화면이 0.8초 걸린다. 소스 맨 위에는 -- 건드리지 말 것 (2018.11)이라는 주석이 있고, 그때 회의록에는 "조인 방식을 USE_NL로 고정 · 3초 → 0.4초"라고 적혀 있다. 회의록 첨부 문서의 마지막 줄은 "측정 조건: 운영 DB 복제본, 주문 20,000건"이다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 (인덱스 없음)
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD 분포 A 1% · B 9% · C 90%
M_RGN: 8행 · PK_M_RGN (RGN_ID)

원본 SQL

sql

SELECT /*+ LEADING(o c) USE_NL(c) */
       c.CUS_GD, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS amt,
       MAX((SELECT r.RGN_NM FROM M_RGN r WHERE r.RGN_ID = c.RGN_ID)) AS rgn_sample
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID
 GROUP BY c.CUS_GD

실행계획 실측 · 교재 06화

xplan

Plan hash value: 2289018667

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |      1 |        |      3 |00:00:00.77 |    1026K|
|   1 |  TABLE ACCESS BY INDEX ROWID  | M_RGN     |      8 |      1 |      8 |00:00:00.01 |      12 |
|*  2 |   INDEX UNIQUE SCAN           | PK_M_RGN  |      8 |      1 |      8 |00:00:00.01 |       4 |
|   3 |  HASH GROUP BY                |           |      1 |      3 |      3 |00:00:00.77 |    1026K|
|   4 |   NESTED LOOPS                |           |      1 |   1000K|   1000K|00:00:00.64 |    1026K|
|   5 |    NESTED LOOPS               |           |      1 |   1000K|   1000K|00:00:00.31 |   26837 |
|   6 |     TABLE ACCESS FULL         | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.04 |    5826 |
|*  7 |     INDEX UNIQUE SCAN         | PK_M_CUS  |   1000K|      1 |   1000K|00:00:00.15 |   21011 |
|   8 |    TABLE ACCESS BY INDEX ROWID| M_CUS     |   1000K|      1 |   1000K|00:00:00.21 |    1000K|
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("R"."RGN_ID"=:B1)
   7 - access("C"."CUS_ID"="O"."CUS_ID")

문제

1,026K 블록이 어디서 나오는지 실행계획의 Starts로 설명하시오. 2018년에는 이 힌트가 왜 옳았는지도 설명하시오.
개선안을 SQL로 작성하고, 이 규모에서 그 조인 방식이 맞는 이유를 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 2018.11 의 USE_NL 은 주문 20,000 건에서 내린 판단 — 지금 1,000,000 건에서는 해시가 맞다
SELECT c.CUS_GD, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS amt,
       MAX((SELECT r.RGN_NM FROM M_RGN r WHERE r.RGN_ID = c.RGN_ID)) AS rgn_sample
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID
 GROUP BY c.CUS_GD
-- 옵티마이저가 고르게 둔다. 굳이 적는다면 /*+ LEADING(c o) USE_HASH(o) */ (M_CUS 를 빌드)

개선 후 실행계획 실측 · 교재 06화

xplan

Plan hash value: 2292855265

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |      3 |00:00:00.25 |    5869 |          |
|   1 |  TABLE ACCESS BY INDEX ROWID | M_RGN     |      8 |      1 |      8 |00:00:00.01 |      12 |          |
|*  2 |   INDEX UNIQUE SCAN          | PK_M_RGN  |      8 |      1 |      8 |00:00:00.01 |       4 |          |
|   3 |  HASH GROUP BY               |           |      1 |      3 |      3 |00:00:00.25 |    5869 |          |
|*  4 |   HASH JOIN                  |           |      1 |   1000K|   1000K|00:00:00.16 |    5857 | 1685K (0)|
|   5 |    TABLE ACCESS FULL         | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|   6 |    TABLE ACCESS FULL         | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5827 |          |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("R"."RGN_ID"=:B1)
   4 - access("C"."CUS_ID"="O"."CUS_ID")

모범 답안 서술

LEADING(o c) USE_NL(c) 때문에 주문 테이블을 먼저 읽고, 주문 한 건마다 PK_M_CUS 인덱스와 M_CUS 테이블을 찾아간다. 7번과 8번 줄의 Starts가 1000K이므로 100만 번 왕복한 것이고, 테이블 액세스에서만 1,000K 블록을 읽었다. 2018년 회의록의 측정 조건은 주문 20,000건이었다. 그 규모에서는 NL의 손해가 수십 ms에 그쳐 드러나지 않았고, 3초 → 0.4초도 그 서버에서는 사실이었다. 데이터가 50배로 늘자 같은 힌트가 왕복 횟수도 50배로 늘렸다. 힌트는 그때의 규모를 보고 내린 판단이라 유효기간이 있다. 고객은 2,000명(30블록)뿐이므로 힌트를 떼고 해시 조인으로 푼다. 작은 M_CUS를 메모리에 빌드하고, 주문을 한 번 훑으면서 대어 본다. Buffers는 1,026,849 → 5,869로 준다.

해설

힌트를 뗀 뒤에는 옵티마이저가 해시 조인을 고르는지까지 확인한다. 굳이 고정하려면 LEADING(c o) USE_HASH(o)를 쓴다. 하지만 이것도 오늘 규모에서 내린 판단이므로 힌트 유효기간 대장에 규모와 재검토 조건을 함께 적는다 (교재 06화).
남은 5,869블록은 T_ORD_BIG을 한 번 읽는 몫이다. 더 줄이려면 먼저 묶고 붙이는 모양(고객별 집계 → 등급 조인)으로 바꾼다. 해시 조인에 대는 행이 100만 → 2,000으로 줄어, Buffers는 같아도 CPU가 준다 (교재 18화 Q2 rev02).
해시 조인은 PGA를 쓴다. work area가 모자라면 같은 계획이라도 TEMP로 넘친다 (교재 06화, 09화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 06화 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 06화 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황