PART-050

연간 상품 판매 집계를 DOP 2에서 8로 올렸더니 TEMP를 쓰며 오히려 느려졌다

실전SELECT유통425 / 700

상황

유통 본사 MD팀은 분기마다 「연간 상품별 매장별 판매」를 병렬로 뽑는다. 2021년에 만든 SQL에는 분배 방식을 고정한 힌트가 붙어 있고, 그때부터 DOP 2로 돌았다. 올해 분석 DB 서버를 늘리면서 DOP를 8로 올렸는데, 6.1초 걸리던 것이 8.4초로 늘었고 TEMP 사용량 경보가 울렸다. 판매상품은 지난해 데이터팀이 판매번호 해시 8칸 복사본으로 다시 만들어 두었다. 박팀장: "분배 방식 몇 가지를 비교하고, 판매 테이블 설계까지 바꿀 가치가 있는지 판단해서 가져와."

테이블과 인덱스

CF10_P 판매(파티션 복사본, 문제 설정): CF10 500,000행을 PARTITION BY RANGE (SLE_DT) 월별 24칸(P202201 ~ P202312)과 빈 PMAX로 나눴다. 2023년은 13 ~ 24번째 칸이고 약 250,000행, 2,292블록이다. 보관 기간이 지난 달은 DROP PARTITION으로 정리한다.
CF11_H 판매상품(분석 복사본, 문제 설정): CF11 1,096,708행을 PARTITION BY HASH (SLE_NO) PARTITIONS 8로 CTAS했다. 판매일 컬럼이 없어 연도로 칸을 고를 수 없다. 8칸 전체는 약 8,208블록이다. 2023년 판매의 판매상품은 약 548,000행이다.
조인 키는 SLE_NO다. 매장 5곳 × 상품 109개라서 결과는 545행이다. 인덱스는 이 조회에 쓰이지 않는다.
분석 DB는 EE이고 자동 PGA 관리를 쓴다. 병렬 작업에서는 PX 서버 하나가 쓸 수 있는 해시 작업 영역이 DOP가 커질수록 작아진다(이 DB에서 DOP 2이면 약 25MB, DOP 8이면 약 6MB). 판매 25만 행으로 지은 해시 테이블은 약 11MB다. 통계는 최신이다.

원본 SQL

sql

-- 2021.05 · DOP 2 에서 분배 방식을 고정했다
SELECT /*+ LEADING(s h) USE_HASH(h) PQ_DISTRIBUTE(h BROADCAST NONE) PARALLEL(8) */
       s.SHP_NO, h.ITM_NO, SUM(h.SLE_QTY) AS qty, SUM(h.SLE_QTY * h.SLE_UPRC) AS amt
  FROM CF10_P s, CF11_H h
 WHERE s.SLE_DT BETWEEN '20230101' AND '20231231'
   AND h.SLE_NO = s.SLE_NO
 GROUP BY s.SHP_NO, h.ITM_NO

실행계획 학습용 예시 · 병렬 계획은 EE 모양(교재 14화 §9.3) · FORMAT => 'ALLSTATS ALL'

xplan

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Starts | A-Rows | Buffers | Used-Mem  | Used-Tmp | Pstart | Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |      1 |    545 |   10500 |           |          |        |       |        |      |            |
|   1 |  PX COORDINATOR                  |          |      1 |    545 |   10500 |           |          |        |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)            | :TQ10002 |      8 |    545 |   10500 |           |          |        |       |  Q1,02 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                 |          |      8 |    545 |   10500 |     389K  |          |        |       |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                   |          |      8 |   4360 |   10500 |           |          |        |       |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH                | :TQ10001 |      8 |   4360 |   10500 |           |          |        |       |  Q1,01 | P->P | HASH       |
|   6 |       HASH GROUP BY              |          |      8 |   4360 |   10500 |     412K  |          |        |       |  Q1,01 | PCWP |            |
|*  7 |        HASH JOIN                 |          |      8 |   548K |   10500 | 5952K (1) |      11M |        |       |  Q1,01 | PCWP |            |
|   8 |         PX RECEIVE               |          |      8 |  2000K |    2292 |           |          |        |       |  Q1,01 | PCWP |            |
|   9 |          PX SEND BROADCAST       | :TQ10000 |      8 |  2000K |    2292 |           |          |        |       |  Q1,00 | P->P | BROADCAST  |
|  10 |           PX BLOCK ITERATOR      |          |      8 |   250K |    2292 |           |          |     13 |    24 |  Q1,00 | PCWC |            |
|* 11 |            TABLE ACCESS FULL     | CF10_P   |    104 |   250K |    2292 |           |          |     13 |    24 |  Q1,00 | PCWP |            |
|  12 |         PX BLOCK ITERATOR        |          |      8 |  1096K |    8208 |           |          |      1 |     8 |  Q1,01 | PCWC |            |
|  13 |          TABLE ACCESS FULL       | CF11_H   |    104 |  1096K |    8208 |           |          |      1 |     8 |  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("H"."SLE_NO"="S"."SLE_NO")
  11 - filter(("S"."SLE_DT">='20230101' AND "S"."SLE_DT"<='20231231'))

  (Used-Mem · Used-Tmp 은 서버 하나의 값 · 서버 여덟이 같은 크기의 해시 테이블을 따로 짓는다)

[같은 SQL · DOP 2 (지난 분기) 와 DOP 8 (이번 분기)]
                                     DOP 2          DOP 8
  경과                                6.1초          8.4초
  TQ10000 이 보낸 판매 행             500,000        2,000,000   (25만 × 서버 수)
  서버 하나의 해시 작업 영역            약 25 MB       약 6 MB
  해시 조인                           메모리 안 (0)   원패스 (1) · 서버마다 TEMP 약 11 MB · 합계 약 88 MB

문제

DOP를 2에서 8로 올렸는데 오히려 느려지고 TEMP를 쓰게 된 까닭을 실행계획의 9번 줄과 7번 줄, 서버 하나가 쓸 수 있는 해시 작업 영역으로 설명하시오.
ⓐ 힌트를 떼고 두 테이블을 해시로 다시 나눠 보내는 안, ⓑ 판매만 판매상품의 칸 기준에 맞춰 보내는 부분 파티션 와이즈 조인, ⓒ 판매를 판매일 Range와 판매번호 Hash로 다시 나눠 완전 파티션 와이즈 조인을 하는 안을 보내는 행 수, 서버마다의 해시 테이블 크기, 설계 변경의 대가로 비교하시오.
고른 안의 SQL을 작성하고, 그 힌트가 앞으로 언제 다시 틀릴 수 있는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 2021년 DOP 2 에서 고정한 BROADCAST 를 뗀다
-- 판매만, 판매상품(CF11_H)의 해시 칸 기준에 맞춰 한 번씩 보낸다 : 부분 파티션 와이즈 조인
SELECT /*+ LEADING(s h) USE_HASH(h) PQ_DISTRIBUTE(h PARTITION NONE) PARALLEL(8) */
       s.SHP_NO, h.ITM_NO, SUM(h.SLE_QTY) AS qty, SUM(h.SLE_QTY * h.SLE_UPRC) AS amt
  FROM CF10_P s, CF11_H h
 WHERE s.SLE_DT BETWEEN '20230101' AND '20231231'
   AND h.SLE_NO = s.SLE_NO
 GROUP BY s.SHP_NO, h.ITM_NO;

-- 힌트를 모두 떼어도 옵티마이저가 같은 분배를 고르면 그쪽이 낫다.
-- 고정한다면 힌트 대장에 'DOP 8 · 판매 25만 행 · 판매상품 HASH(SLE_NO) 8칸' 을 재검토 조건으로 적는다

개선 후 실행계획 학습용 예시 · 병렬 계획은 EE 모양(교재 14화 §9.3) · FORMAT => 'ALLSTATS ALL'

xplan

[ⓑ 부분 파티션 와이즈 조인 · DOP 8]
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Starts | A-Rows | Buffers | Used-Mem  | Used-Tmp | Pstart | Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |      1 |    545 |   10500 |           |          |        |       |        |      |            |
|   1 |  PX COORDINATOR                  |          |      1 |    545 |   10500 |           |          |        |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)            | :TQ10002 |      8 |    545 |   10500 |           |          |        |       |  Q1,02 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                 |          |      8 |    545 |   10500 |     389K  |          |        |       |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                   |          |      8 |   4360 |   10500 |           |          |        |       |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH                | :TQ10001 |      8 |   4360 |   10500 |           |          |        |       |  Q1,01 | P->P | HASH       |
|   6 |       HASH GROUP BY              |          |      8 |   4360 |   10500 |     412K  |          |        |       |  Q1,01 | PCWP |            |
|*  7 |        HASH JOIN                 |          |      8 |   548K |   10500 | 1650K (0) |          |        |       |  Q1,01 | PCWP |            |
|   8 |         PX RECEIVE               |          |      8 |   250K |    2292 |           |          |        |       |  Q1,01 | PCWP |            |
|   9 |          PX SEND PARTITION (KEY) | :TQ10000 |      8 |   250K |    2292 |           |          |        |       |  Q1,00 | P->P | PART (KEY) |
|  10 |           PX BLOCK ITERATOR      |          |      8 |   250K |    2292 |           |          |     13 |    24 |  Q1,00 | PCWC |            |
|* 11 |            TABLE ACCESS FULL     | CF10_P   |    104 |   250K |    2292 |           |          |     13 |    24 |  Q1,00 | PCWP |            |
|  12 |         PX PARTITION HASH ALL    |          |      8 |  1096K |    8208 |           |          |      1 |     8 |  Q1,01 | PCWC |            |
|  13 |          TABLE ACCESS FULL       | CF11_H   |      8 |  1096K |    8208 |           |          |      1 |     8 |  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("H"."SLE_NO"="S"."SLE_NO")
  11 - filter(("S"."SLE_DT">='20230101' AND "S"."SLE_DT"<='20231231'))

[세 안 비교 · DOP 8 (어림)]
  안                      조인 전에 보내는 행          서버마다 해시 테이블     TEMP     경과     설계 변경
  지금 BROADCAST          25만 × 8 = 200만             판매 25만 행 (약 11MB)    약 88MB  8.4초    -
  ⓐ 해시 해시             25만 + 110만 = 약 135만       약 3만 1천 행             0        약 3.9초  힌트만
  ⓑ 부분 와이즈           25만                         약 3만 1천 행             0        약 3.2초  힌트만 (또는 힌트 없음)
  ⓒ 완전 와이즈           0                            약 3만 1천 행             0        약 2.9초  판매를 RANGE 24 × HASH 8 = 192조각으로 다시 짓기

모범 답안 서술

9번 줄 PX SEND BROADCAST는 2023년 판매 25만 행을 PX 서버마다 한 벌씩 보낸다. 서버가 여덟이므로 TQ10000이 보낸 행은 25만 × 8 = 200만 행이고, 7번 줄 HASH JOIN에서는 서버 여덟이 저마다 판매 25만 행 전체로 같은 해시 테이블(약 11MB)을 짓는다. DOP 2일 때는 서버 하나의 해시 작업 영역이 약 25MB라 그 안에 들어갔다. 그런데 자동 PGA 관리는 병렬 작업의 작업 영역을 서버 수로 나눠 주므로 DOP 8에서는 서버 하나에 약 6MB만 돌아간다. 해시 테이블 크기는 서버 수와 상관없이 11MB 그대로라서 서버마다 원패스로 TEMP에 넘쳤고(Used-Tmp 11M × 8 = 약 88MB), 서버를 늘렸는데도 6.1 → 8.4초로 느려졌다. 판매 쪽이 작다는 2021년의 판단이 DOP를 바꾸자 틀린 셈이다. 세 안을 비교한다. ⓐ 힌트를 떼고 해시 해시로 보내면 판매 25만 행과 판매상품 약 110만 행을 모두 조인 키로 다시 나눠 보낸다(약 135만 행). 서버마다 해시 테이블은 판매의 8분의 1(약 3만 1천 행)이라 TEMP를 쓰지 않는다. ⓑ 판매상품 CF11_H는 이미 조인 키 SLE_NO로 8칸에 나뉘어 있다. PQ_DISTRIBUTE(h PARTITION NONE)이면 판매상품은 칸 하나를 서버 하나가 그대로 읽고(PX PARTITION HASH ALL), 판매 25만 행만 판매상품의 칸 기준에 맞춰 한 번씩 보낸다(PX SEND PARTITION (KEY), PQ Distrib PART (KEY)). 보내는 행이 200만 → 25만으로 가장 적고, 해시 테이블은 서버마다 약 3만 1천 행이다. 힌트 하나만 바꾸면 된다. ⓒ 판매까지 RANGE (SLE_DT) 칸 안을 HASH (SLE_NO) 8조각으로 나누면 두 테이블이 조각끼리 짝을 지어 조인 전에 보낼 행이 없어진다. 하지만 판매를 다시 지어야 하고, 판매 테이블이 24 × 8 = 192조각이 된다. 조각마다 첫 익스텐트 8MB를 잡지 않도록 STORAGE (INITIAL …)를 챙겨야 하고(교재 14화 §8.5), 조각 하나는 약 2,600행으로 작아져 다른 조회와 월 정리에도 영향을 확인해야 한다. ⓑ보다 줄어드는 것은 판매 25만 행을 보내는 일뿐이다. 그래서 ⓑ를 고른다. 판매 테이블은 그대로 두고 힌트를 PQ_DISTRIBUTE(h PARTITION NONE)으로 바꾼다. 경과 시간은 8.4 → 약 3.2초, TEMP는 88MB → 0이 된다. 힌트를 모두 떼어도 옵티마이저가 같은 분배를 고른다면 그쪽이 더 낫다. 이 힌트도 언제든 다시 틀릴 수 있다. 판매상품의 칸 수가 바뀌거나 해시 키가 SLE_NO가 아니게 되면 부분 와이즈 조인이 성립하지 않는다. DOP를 칸 수(8)보다 크게 올리면 판매상품 쪽은 칸이 일의 단위라 남는 서버가 논다. 그래서 힌트 대장에 DOP, 판매 행 수, 판매상품의 칸 설계를 재검토 조건으로 적어 둔다.

해설

BROADCAST가 맞는 때: 작은 쪽이 아주 작을 때다(매장 5행, 상품 109행 같은 차원 테이블). 수십만 행을 서버 수만큼 복제하면 보내는 양과 메모리가 DOP에 비례해 는다.
DOP를 올리면 서버 하나의 메모리 몫이 준다. 같은 계획이라도 DOP만 바꿔 TEMP로 넘칠 수 있다. 병렬 계획의 Used-Mem과 Used-Tmp는 서버 하나의 값이라는 점도 함께 읽는다.
힌트는 그때의 규모와 DOP에서 내린 판단이다(JOIN-001의 힌트 유효기간). 2021년의 DOP 2와 판매 행 수가 바뀌자 같은 힌트가 손해가 되었다.
판매상품을 판매와 같은 월 기준으로 나누는 길(판매일을 붙이거나 참조 파티션)은 보관 정리까지 맞출 수 있지만 판매상품을 다시 지어야 한다(PART-025).
교재 14화처럼 Free에서는 병렬이 돌지 않는다. 비교는 운영의 EE에서 V$PQ_TQSTAT의 보낸 행 수와 서버별 TEMP로 확인한다(교재 14화 §9).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 병렬 계획은 EE 모양(교재 14화 §9.3) · FORMAT => 'ALLSTATS ALL' ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 병렬 계획은 EE 모양(교재 14화 §9.3) · FORMAT => 'ALLSTATS ALL' ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황