JOIN-026

시즌 메뉴 한 가지의 매장 · 요일 판매를 보는데 판매 50만 건을 메모리에 올린다

중급SELECT유통176 / 700

상황

상품팀의 「상품 판매 분포」는 상품 하나를 고르면 2년 치 판매를 매장과 요일로 나눠 보여 준다. 시즌 메뉴를 검토하는 회의 전에 상품을 바꿔 가며 돌리고, 매장 다섯 곳의 점장도 아침마다 연다. 개발팀 코딩 규칙에 "헤더 → 상세 순서로 조인한다"가 있어서 판매 헤더부터 읽도록 힌트가 붙어 있다. 아침 9시 무렵 여러 명이 함께 열면 PGA 사용량이 수백 MB로 뛴다는 DBA 보고가 있었다. 아래는 시즌 메뉴(ITM_NO = 1047) 하나로 조회한 실행계획이다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · SHP_NO 5곳 · SLE_DOW 1 ~ 7
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · ITM_NO만으로 찾는 인덱스는 없다 · ITM_NO = 1047은 2년 동안 8,512줄이다
통계 최신 · 히스토그램 없음 (옵티마이저는 상품 하나를 1,096,708 / 88 ≈ 12,463줄로 본다)

원본 SQL

sql

SELECT /*+ LEADING(a b) USE_HASH(b) */
       a.SHP_NO, a.SLE_DOW, COUNT(*) AS sle_lines, SUM(b.SLE_QTY) AS sle_qty
  FROM CF10 a, CF11 b
 WHERE b.ITM_NO = :v_itm
   AND b.SLE_NO = a.SLE_NO
 GROUP BY a.SHP_NO, a.SLE_DOW

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     35 |00:00:00.31 |   12787 |       |       |          |
|   1 |  HASH GROUP BY      |      |      1 |     35 |     35 |00:00:00.31 |   12787 |  1200K|  1200K|  961K (0)|
|*  2 |   HASH JOIN         |      |      1 |  12463 |   8512 |00:00:00.30 |   12787 |    29M|  4671K|   33M (0)|
|   3 |    TABLE ACCESS FULL| CF10 |      1 |    500K|    500K|00:00:00.04 |    4583 |       |       |          |
|*  4 |    TABLE ACCESS FULL| CF11 |      1 |  12463 |   8512 |00:00:00.07 |    8204 |       |       |          |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SLE_NO"="A"."SLE_NO")
   4 - filter("B"."ITM_NO"=:V_ITM)

문제

2번 줄 해시 조인의 build가 무엇이고 몇 행인지, 그래서 메모리를 얼마나 쓰는지 설명하시오. 코딩 규칙 "헤더 → 상세"가 해시 조인의 build 선택에도 맞는 기준인지 판단하시오.
build가 작은 집합이 되도록 SQL을 고치고, NL 조인(CF11 → CF10_PK)으로 바꾸는 안과 Buffers를 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 조건을 건 뒤 작은 쪽(시즌 메뉴 8,512줄)을 build 로, 판매 헤더 50만 행은 probe 로 흘린다
SELECT /*+ LEADING(b a) USE_HASH(a) */
       a.SHP_NO, a.SLE_DOW, COUNT(*) AS sle_lines, SUM(b.SLE_QTY) AS sle_qty
  FROM CF10 a, CF11 b
 WHERE b.ITM_NO = :v_itm
   AND b.SLE_NO = a.SLE_NO
 GROUP BY a.SHP_NO, a.SLE_DOW
-- 같은 뜻: LEADING(a b) USE_HASH(b) SWAP_JOIN_INPUTS(b) / 힌트를 떼도 옵티마이저가 같은 build 를 고른다

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

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     35 |00:00:00.16 |   12787 |       |       |          |
|   1 |  HASH GROUP BY      |      |      1 |     35 |     35 |00:00:00.16 |   12787 |  1200K|  1200K|  961K (0)|
|*  2 |   HASH JOIN         |      |      1 |  12463 |   8512 |00:00:00.15 |   12787 |  1517K|  1517K| 1563K (0)|
|*  3 |    TABLE ACCESS FULL| CF11 |      1 |  12463 |   8512 |00:00:00.07 |    8204 |       |       |          |
|   4 |    TABLE ACCESS FULL| CF10 |      1 |    500K|    500K|00:00:00.04 |    4583 |       |       |          |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SLE_NO"="A"."SLE_NO")
   3 - filter("B"."ITM_NO"=:V_ITM)

모범 답안 서술

2번 줄 해시 조인의 첫 번째 자식(3번 줄)인 CF10이 build다. CF10에는 조건이 하나도 없어서 판매 헤더 50만 행이 전부 PGA의 해시 테이블로 올라갔다. OMem 29M, Used-Mem 33M다. 정작 짝이 되는 행은 CF11에서 시즌 메뉴 조건으로 걸러진 8,512줄뿐이다. 한 사람이 열 때는 optimal (0)로 끝나지만, 점장 다섯 명과 상품팀이 아침에 함께 열면 33M씩 곱해져 PGA가 수백 MB로 뛴다. 코딩 규칙 "헤더 → 상세"는 NL 조인의 드라이빙 순서에서 나온 말이다. NL에서는 헤더 한 건마다 상세를 인덱스로 찾아가는 순서가 자연스럽다. 해시 조인의 build는 그 기준으로 정하지 않는다. build로 쓸 작은 쪽은 테이블의 크기가 아니라 조건을 적용한 뒤에 남는 집합의 크기로 정한다. 여기서는 CF11이 110만 줄짜리 큰 테이블이지만 조건 뒤에 8,512줄만 남으므로 이쪽이 작다. LEADING(b a) USE_HASH(a)로 CF11의 8,512줄을 build로 올리고 판매 헤더 50만 행을 probe로 흘린다. 두 테이블을 한 번씩 읽으므로 Buffers는 12,787로 그대로지만, Used-Mem은 33M → 1,563K로 약 20분의 1이 되고 A-Time은 0.31 → 0.16초로 준다. 여러 명이 함께 열어도 PGA 부담이 거의 없다. NL 조인으로 바꾸면 CF11을 FULL로 한 번 읽어 8,512줄을 얻은 뒤, 그 8,512줄마다 CF10_PK로 판매 헤더를 찾아간다. 한 번에 인덱스 2 ~ 3블록과 테이블 1블록이면 약 25,000 ~ 34,000블록이 더해져 전체 약 3만 3천 ~ 4만 2천 블록이다. 해시 조인(12,787블록)보다 비싸다. 바깥 집합이 8,512건이나 되므로 왕복 횟수가 곱해지는 NL은 맞지 않는다.

해설

추정과 실제: 옵티마이저는 히스토그램이 없어 상품 하나를 12,463줄로 봤다. 실제(8,512줄)와 차이가 있어도 50만 행보다 훨씬 작다는 판단은 같으므로, 힌트를 떼면 옵티마이저가 스스로 CF11을 build로 고른다. 이 SQL의 문제는 추정이 아니라 규칙에서 나온 힌트다.
probe 쪽을 더 줄이려면: 이 상품의 판매 헤더만 읽을 길은 없다(헤더에는 상품이 없다). CF11 쪽을 줄이려고 CF11 (ITM_NO) 인덱스를 만들어도, 흩어진 8,512줄을 찾아가는 테이블 액세스가 FULL 8,204블록과 비슷해 얻는 것이 적다 (JOIN-016과 같은 손익분기).
코딩 규칙 고치기: "헤더 → 상세"는 NL 화면에서는 맞다. 규칙 문서에 "해시 조인은 조건을 적용한 뒤 작은 쪽이 build"라는 줄을 따로 두고, 조인 방식이 정해지지 않은 SQL에는 순서 힌트를 넣지 않게 한다.
동시 실행과 메모리: AUTO 정책에서는 함께 도는 work area가 많아지면 하나에 주는 크기가 줄어든다. 33M짜리 build는 혼자일 때 optimal이어도 아침 시간대에는 TEMP로 넘칠 수 있다 (교재 09화 §6).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황