JOIN-083

공통 모듈의 주문 상세 SQL은 계획이 바뀔 때마다 상담 화면과 회계 배치 가운데 한쪽이 느려진다

실전SELECT한빛유통233 / 700

상황

공통 모듈의 「주문 상세」 SQL(PKG_ORD_COM.Q7)은 기간을 받아 주문과 주문상세를 주문번호 순으로 낸다. 상담 화면 「하루 주문 상세」가 하루치로 하루 3,000번 부르고, 회계팀의 월 배치 「연간 주문 상세 파일」이 한 달에 한 번 1년 치로 부른다. 두 곳은 SQL을 각자 복사해 써서 커서가 따로 생기고, 지금은 둘 다 힌트가 없다. 지난달 통계를 다시 모은 뒤 화면 쪽 계획이 해시로 바뀌어 화면이 느려졌다는 민원이 왔고, 작년에는 배치 쪽이 NL로 돌아 새벽 배치가 밀린 적이 있다. 조민우가 같은 SQL에 조인 방식 힌트만 바꿔 가며 두 기간을 재었다. 박팀장은 "세 방식을 비교한 의견서를 쓰고, 두 곳에 무엇을 쓸지 정해 달라"라고 했다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · IX_T_ORD_DT (ORD_DT) · ORD_SEQ 순으로 쌓였고 같은 날짜의 주문은 테이블 곳곳에 흩어져 있다(하루 137건이 137블록) · 2024년 50,000건(테이블의 절반)
T_ORD_DET 주문상세: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) (BLEVEL 1) · 주문당 1 ~ 3건(평균 2) · 주문 순서로 적재
pga_aggregate_target 512M · AUTO · 월 배치가 도는 새벽에는 다른 배치 다섯 개가 함께 돈다
측정은 모두 LEADING(o d)로 순서를 고정하고 조인 방식 힌트만 바꿨다

원본 SQL

sql

SELECT o.ORD_SEQ, o.ORD_DT, o.CUS_ID, d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY, d.UNT_PRC
  FROM T_ORD o, T_ORD_DET d
 WHERE o.ORD_DT >= :d1
   AND o.ORD_DT <  :d2
   AND d.ORD_SEQ = o.ORD_SEQ
 ORDER BY o.ORD_SEQ, d.ORD_DET_NO

실행계획 학습용 예시

xplan

[측정 요약 · LEADING(o d) 고정, 조인 방식 힌트만 바꿈]
 기간                 방식                    Buffers   A-Time   조인 줄의 메모리                          마지막 정렬
 A 하루 (주문 137)    NL    USE_NL(d)             688   0.01초   없음                                      275줄 · 2048 (0)
                      해시  USE_HASH(d)         1,035   0.02초   HASH JOIN build 주문 137행 · 1,190K (0)    275줄 · 2048 (0)
                      머지  USE_MERGE(d)        1,035   0.14초   SORT JOIN 주문상세 20만 줄 · 8,960K (0)    275줄 · 2048 (0)
 B 1년 (주문 50,000)  NL    USE_NL(d)            201K   0.71초   없음                                      10만 줄 · 6,420K (0)
                      해시  USE_HASH(d)         1,792   0.26초   HASH JOIN build 주문 5만 행 · 3,480K (0)   10만 줄 · 6,420K (0)
                      머지  USE_MERGE(d)        1,792   0.44초   SORT JOIN 주문 5만 행 · 2,310K (0)         10만 줄 · 6,420K (0)
                                                                 + SORT JOIN 주문상세 20만 줄 · 8,960K (0)
 (A는 T_ORD를 IX_T_ORD_DT로, B는 T_ORD 절반을 읽으므로 세 방식 모두 FULL로 읽었다)

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
[B 1년 · NL]
|   0 | SELECT STATEMENT                       |              |      1 |        |   100K |00:00:00.71 |    201K |          |
|   1 |  SORT ORDER BY                         |              |      1 |   100K |   100K |00:00:00.71 |    201K | 6420K (0)|
|   2 |   NESTED LOOPS                         |              |      1 |   100K |   100K |00:00:00.58 |    201K |          |
|   3 |    NESTED LOOPS                        |              |      1 |   100K |   100K |00:00:00.29 |    101K |          |
|*  4 |     TABLE ACCESS FULL                  | T_ORD        |      1 |  50000 |  50000 |00:00:00.02 |     896 |          |
|*  5 |     INDEX RANGE SCAN                   | PK_T_ORD_DET |  50000 |      2 |   100K |00:00:00.21 |    100K |          |
|   6 |    TABLE ACCESS BY INDEX ROWID         | T_ORD_DET    |   100K |      2 |   100K |00:00:00.24 |    100K |          |
[B 1년 · 소트 머지]
|   0 | SELECT STATEMENT                       |              |      1 |        |   100K |00:00:00.44 |    1792 |          |
|   1 |  SORT ORDER BY                         |              |      1 |   100K |   100K |00:00:00.44 |    1792 | 6420K (0)|
|   2 |   MERGE JOIN                           |              |      1 |   100K |   100K |00:00:00.36 |    1792 |          |
|   3 |    SORT JOIN                           |              |      1 |  50000 |  50000 |00:00:00.05 |     896 | 2310K (0)|
|*  4 |     TABLE ACCESS FULL                  | T_ORD        |      1 |  50000 |  50000 |00:00:00.02 |     896 |          |
|*  5 |    SORT JOIN                           |              |  50000 |   200K |   100K |00:00:00.27 |     896 | 8960K (0)|
|   6 |     TABLE ACCESS FULL                  | T_ORD_DET    |      1 |   200K |   200K |00:00:00.03 |     896 |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  [NL]   4 - filter(("O"."ORD_DT">=:D1 AND "O"."ORD_DT"<:D2))
         5 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
  [머지] 4 - filter(("O"."ORD_DT">=:D1 AND "O"."ORD_DT"<:D2))
         5 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
             filter("D"."ORD_SEQ"="O"."ORD_SEQ")

문제

세 방식 각각이 어떤 순서로 일을 하고 일량이 어디서 나오는지 측정값과 실행계획의 Starts, Used-Mem으로 비교해 서술하시오. 소트 머지 계획 5번 줄의 Starts 50,000이 무엇을 뜻하는지도 쓰시오.
상담 화면과 회계 배치에 각각 어느 방식을 쓸지 정하고 SQL을 작성하시오. 이번에 지는 방식이 이기는 조건도 쓰시오.
고른 방식의 부작용과, 힌트 유효기간 대장에 남길 재검토 조건을 숫자로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- [A] 상담 화면 「하루 주문 상세」: NL (그날 주문 137건마다 주문상세를 PK로 찾는다)
SELECT /*+ LEADING(o d) USE_NL(d) */
       o.ORD_SEQ, o.ORD_DT, o.CUS_ID, d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY, d.UNT_PRC
  FROM T_ORD o, T_ORD_DET d
 WHERE o.ORD_DT >= :d1
   AND o.ORD_DT <  :d2
   AND d.ORD_SEQ = o.ORD_SEQ
 ORDER BY o.ORD_SEQ, d.ORD_DET_NO;

-- [B] 회계 월 배치 「연간 주문 상세 파일」: 해시 (그해 주문 5만 건을 build, 주문상세는 한 번만 읽는다)
SELECT /*+ LEADING(o d) USE_HASH(d) */
       o.ORD_SEQ, o.ORD_DT, o.CUS_ID, d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY, d.UNT_PRC
  FROM T_ORD o, T_ORD_DET d
 WHERE o.ORD_DT >= :d1
   AND o.ORD_DT <  :d2
   AND d.ORD_SEQ = o.ORD_SEQ
 ORDER BY o.ORD_SEQ, d.ORD_DET_NO

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

xplan

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
[A 하루 · NL]
|   0 | SELECT STATEMENT                       |              |      1 |        |    275 |00:00:00.01 |     688 |          |
|   1 |  SORT ORDER BY                         |              |      1 |    274 |    275 |00:00:00.01 |     688 | 2048  (0)|
|   2 |   NESTED LOOPS                         |              |      1 |    274 |    275 |00:00:00.01 |     688 |          |
|   3 |    NESTED LOOPS                        |              |      1 |    274 |    275 |00:00:00.01 |     413 |          |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD        |      1 |    137 |    137 |00:00:00.01 |     139 |          |
|*  5 |      INDEX RANGE SCAN                  | IX_T_ORD_DT  |      1 |    137 |    137 |00:00:00.01 |       2 |          |
|*  6 |     INDEX RANGE SCAN                   | PK_T_ORD_DET |    137 |      2 |    275 |00:00:00.01 |     274 |          |
|   7 |    TABLE ACCESS BY INDEX ROWID         | T_ORD_DET    |    275 |      2 |    275 |00:00:00.01 |     275 |          |
[B 1년 · 해시]
|   0 | SELECT STATEMENT                       |              |      1 |        |   100K |00:00:00.26 |    1792 |          |
|   1 |  SORT ORDER BY                         |              |      1 |   100K |   100K |00:00:00.26 |    1792 | 6420K (0)|
|*  2 |   HASH JOIN                            |              |      1 |   100K |   100K |00:00:00.17 |    1792 | 3480K (0)|
|*  3 |    TABLE ACCESS FULL                   | T_ORD        |      1 |  50000 |  50000 |00:00:00.02 |     896 |          |
|   4 |    TABLE ACCESS FULL                   | T_ORD_DET    |      1 |   200K |   200K |00:00:00.03 |     896 |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  [A]  5 - access("O"."ORD_DT">=:D1 AND "O"."ORD_DT"<:D2)
       6 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
  [B]  2 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
       3 - filter(("O"."ORD_DT">=:D1 AND "O"."ORD_DT"<:D2))

모범 답안 서술

NL: 바깥(그 기간의 주문)을 읽고, 주문 한 건마다 PK_T_ORD_DET로 주문상세를 찾아간다. 일량은 바깥 건수에 비례한다. B의 NL 계획 5번 줄 Starts 50,000이 주문 한 건마다 인덱스를 찾아간 횟수다. 한 번에 인덱스 2블록과 테이블 2줄이 들어 주문 한 건에 약 4블록이므로, A는 바깥 139블록에 137건 × 4 ≈ 549블록을 더해 688블록, B는 바깥 896블록에 5만 건 × 4 ≈ 20만 블록을 더해 201K블록이다. 메모리는 거의 쓰지 않고, 정렬만 없다면 첫 행을 바로 낼 수 있다. 해시: 작은 쪽(그 기간의 주문)을 PGA에 해시 테이블로 올리고(build), 주문상세를 한 번 FULL로 읽으며 맞춰 본다(probe). 일량은 주문상세 896블록이라는 상수항에 바깥을 읽는 몫을 더한 값이라 기간과 거의 상관없다(A 139 + 896 = 1,035, B 896 + 896 = 1,792블록). 대신 build 크기만큼 PGA를 쓴다. B의 3,480K (0)은 5만 행 build가 메모리 안에서 끝났다는 뜻이다. 소트 머지: 양쪽을 조인 키(ORD_SEQ)로 정렬해 두고 나란히 훑는다. 주문상세에는 기간 조건이 없어서, 기간이 하루든 1년이든 주문상세 20만 줄을 통째로 정렬한다(SORT JOIN 8,960K). 그래서 A에서는 블록이 해시와 같은 1,035인데도 0.14초로 가장 느리다. 머지 계획 5번 줄의 Starts 50,000은 정렬을 5만 번 했다는 뜻이 아니라, 한 번 정렬해 둔 주문상세에 주문 한 건마다 짝을 찾으러 들어간 횟수다. 정렬은 한 번뿐이다. 고른 방식: A 상담 화면은 NL이다. 하루 137건이면 NL 688블록이 해시 1,035블록보다 싸고 PGA도 쓰지 않는다. 바깥을 읽는 몫은 두 방식이 같으므로, 이 조인의 손익분기점은 해시의 상수항 896블록 ÷ 주문당 약 4블록 ≈ 224건이다. 하루 137건은 NL 구간이다. B 회계 배치는 해시다. 1년 5만 건이면 NL은 201K블록으로 해시의 약 112배를 읽고, 머지는 블록은 해시와 같지만 20만 줄 정렬 때문에 1.7배 느리다(0.44 대 0.26초). 커서가 이미 따로이므로 두 SQL에 각각 힌트로 방식을 적는다. 지는 방식이 이기는 조건: 소트 머지는 양쪽이 이미 조인 키 순서로 정렬돼 들어올 때(두 테이블을 PK 인덱스 순서로 읽어 SORT JOIN이 사라질 때)나, 조인 조건이 부등호(BETWEEN, <)라서 해시를 쓸 수 없을 때 이긴다. 해시는 바깥이 분기점(약 224건)보다 적은 화면에서 진다. NL은 바깥이 분기점을 넘는 순간부터 진다. 부작용과 재검토 조건: NL(A)은 화면 기간이 늘면 일량도 그만큼 늘어난다. 해시(B)는 build를 PGA에 올리므로, 파일 기간이 늘어 build가 work area에 들어가지 않으면 블록은 그대로인 채 TEMP를 쓰며 느려진다. 새벽에는 배치 다섯 개가 PGA를 나눠 쓰므로 Used-Mem 칸의 (0)이 (1)로 바뀌는지 본다. 대장에는 A를 「하루 137건, 주문상세 896블록」 전제로 올리고 재검토 조건을 「화면 기간이 이틀 이상으로 바뀌거나 하루 주문이 200건을 넘을 때」로 적는다. B는 「1년 5만 건, build 3,480K (0)」 전제로 올리고 「파일 기간이 3년 이상으로 바뀔 때, Used-Mem이 (1)로 바뀔 때, pga_aggregate_target이 바뀔 때」로 적는다. 둘 다 6개월마다 다시 잰다.

해설

SORT JOIN의 Starts: 머지 조인에서 두 번째 SORT JOIN 줄의 Starts는 바깥 행 수만큼 찍힌다. 정렬 횟수가 아니라 정렬된 집합에 들어간 횟수다. 이것을 정렬 반복으로 읽고 NL로 바꾸면 크게 손해를 본다(JOIN-037의 함정).
머지를 살리는 인덱스: T_ORD를 PK_T_ORD 순서로, T_ORD_DET를 PK_T_ORD_DET 순서로 읽으면 SORT JOIN 없이 머지할 수 있고, 결과가 이미 ORD_SEQ, ORD_DET_NO 순이라 마지막 정렬도 사라질 수 있다. 두 테이블 모두 ORD_SEQ 순으로 쌓여 PK 순서와 물리 순서가 같으므로 B에서는 재어 볼 만한 후보다. 다만 T_ORD의 1년 조건은 PK를 처음부터 끝까지 읽으며 걸러야 하므로, 블록과 시간을 재어 보고 정한다.
둘로 나누는 이유: SQL 하나에 방식 하나를 박으면 한쪽이 늘 진다. 커서가 이미 따로라서 글자를 나누는 비용도 없다. 통계에 따라 계획이 흔들리던 원인(분기점 근처의 추정)도 함께 사라진다.
세 방식은 "바깥 한 건에 맞는 안쪽을 어디서 찾나"라는 같은 질문의 세 가지 답이다. 의견서는 06화 §1의 표에 이 SQL의 숫자를 채워 쓰면 된다 (교재 06화 §1, §5, §6).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황