PART-049

해시 8칸으로 나눈 두 분석 테이블을 병렬로 조인하는데 주문상품 247만 행이 서버 사이를 오간다

중급SELECT버거424 / 700

상황

버거 본사 데이터팀은 병렬 분석용으로 주문과 주문상품의 복사본을 해시 8칸으로 나눠 두었다. 분기마다 「전 기간 매장별 메뉴 판매」를 DOP 8로 돌리는데 12초 가까이 걸린다. 계획을 본 선배: "둘 다 8칸이면 칸끼리 짝지어 조인할 텐데, PX SEND가 왜 있지?" 데이터팀은 주문상품을 나눌 때 "PK 컬럼으로 나눠야 고르게 퍼진다"며 PK 두 컬럼을 해시 키로 썼다.

테이블과 인덱스

ORD_H 주문(분석 복사본, 문제 설정): ORD 1,000,000행을 PARTITION BY HASH (ORD_NO) PARTITIONS 8로 CTAS했다. 칸 하나는 약 125,000행이고 8칸 전체는 약 22,510블록이다.
ORD_ITEM_H 주문상품(분석 복사본, 문제 설정): ORD_ITEM 2,472,717행을 PARTITION BY HASH (ORD_NO, ITEM_ID) PARTITIONS 8로 CTAS했다. 8칸 전체는 약 55,384블록이다.
매장 200개 × 상품 92개라서 결과는 18,400행이다. 인덱스는 없다.
분석 DB는 EE이고 병렬 실행이 켜져 있다(parallel_max_servers 32). 이 시각에 다른 병렬 작업은 없다. 통계는 최신이다.

원본 SQL

sql

SELECT /*+ PARALLEL(8) */
       o.SHOP_NO, i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS qty, COUNT(*) AS line_cnt
  FROM ORD_H o, ORD_ITEM_H i
 WHERE i.ORD_NO = o.ORD_NO
 GROUP BY o.SHOP_NO, i.ITEM_ID

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

xplan

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | A-Rows | Buffers | Used-Mem | Pstart | Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |      1 |  18400 |   77894 |          |        |       |        |      |            |
|   1 |  PX COORDINATOR                  |            |      1 |  18400 |   77894 |          |        |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)            | :TQ10002   |      8 |  18400 |   77894 |          |        |       |  Q1,02 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                 |            |      8 |  18400 |   77894 |    1124K |        |       |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                   |            |      8 |   147K |   77894 |          |        |       |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH                | :TQ10001   |      8 |   147K |   77894 |          |        |       |  Q1,01 | P->P | HASH       |
|   6 |       HASH GROUP BY              |            |      8 |   147K |   77894 |    1610K |        |       |  Q1,01 | PCWP |            |
|*  7 |        HASH JOIN                 |            |      8 |  2472K |   77894 |    5120K |        |       |  Q1,01 | PCWP |            |
|   8 |         PX PARTITION HASH ALL    |            |      8 |  1000K |   22510 |          |      1 |     8 |  Q1,01 | PCWC |            |
|   9 |          TABLE ACCESS FULL       | ORD_H      |      8 |  1000K |   22510 |          |      1 |     8 |  Q1,01 | PCWP |            |
|  10 |         PX RECEIVE               |            |      8 |  2472K |   55384 |          |        |       |  Q1,01 | PCWP |            |
|  11 |          PX SEND PARTITION (KEY) | :TQ10000   |      8 |  2472K |   55384 |          |        |       |  Q1,00 | P->P | PART (KEY) |
|  12 |           PX BLOCK ITERATOR      |            |      8 |  2472K |   55384 |          |      1 |     8 |  Q1,00 | PCWC |            |
|  13 |            TABLE ACCESS FULL     | ORD_ITEM_H |    104 |  2472K |   55384 |          |      1 |     8 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("I"."ORD_NO"="O"."ORD_NO")

[V$PQ_TQSTAT · 한 번 실행]
  TQ_ID  SERVER_TYPE  서버 수  NUM_ROWS 합계
  0      Producer     8        2,472,717    (주문상품 전부를 ORD_H 의 칸 기준으로 다시 나눠 보냈다)
  1      Producer     8          147,200    (서버마다 부분 집계)
  2      Producer     8           18,400
  경과 11.8초 · PX 서버 대기 1위 PX Deq Credit: send blkd

문제

실행계획의 어느 줄에서 주문상품 행이 서버 사이를 오가는지 줄 번호와 PQ Distrib로 쓰고, 주문 쪽은 왜 옮기지 않는지 설명하시오.
행을 주고받지 않고 칸끼리 조인하게 하려면 무엇을 바꿔야 하는지 DDL로 작성하고, 바뀐 계획의 모양을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 주문상품을 조인 키 ORD_NO 하나로, 주문(ORD_H)과 같은 8칸으로 다시 나눈다
CREATE TABLE ORD_ITEM_H2
PARTITION BY HASH (ORD_NO) PARTITIONS 8
PARALLEL 8
AS SELECT * FROM ORD_ITEM;

ALTER TABLE ORD_ITEM_H2 NOPARALLEL;        -- 만들 때만 병렬 · 테이블 차수는 되돌린다
DROP TABLE ORD_ITEM_H PURGE;
ALTER TABLE ORD_ITEM_H2 RENAME TO ORD_ITEM_H;

-- 조회는 그대로
SELECT /*+ PARALLEL(8) */
       o.SHOP_NO, i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS qty, COUNT(*) AS line_cnt
  FROM ORD_H o, ORD_ITEM_H i
 WHERE i.ORD_NO = o.ORD_NO
 GROUP BY o.SHOP_NO, i.ITEM_ID;

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

xplan

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | A-Rows | Buffers | Used-Mem | Pstart | Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |      1 |  18400 |   77894 |          |        |       |        |      |            |
|   1 |  PX COORDINATOR                  |            |      1 |  18400 |   77894 |          |        |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)            | :TQ10001   |      8 |  18400 |   77894 |          |        |       |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                 |            |      8 |  18400 |   77894 |    1124K |        |       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE                   |            |      8 |   147K |   77894 |          |        |       |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH                | :TQ10000   |      8 |   147K |   77894 |          |        |       |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY              |            |      8 |   147K |   77894 |    1610K |        |       |  Q1,00 | PCWP |            |
|   7 |        PX PARTITION HASH ALL     |            |      8 |  2472K |   77894 |          |      1 |     8 |  Q1,00 | PCWC |            |
|*  8 |         HASH JOIN                |            |      8 |  2472K |   77894 |    5120K |        |       |  Q1,00 | PCWP |            |
|   9 |          TABLE ACCESS FULL       | ORD_H      |      8 |  1000K |   22510 |          |      1 |     8 |  Q1,00 | PCWP |            |
|  10 |          TABLE ACCESS FULL       | ORD_ITEM_H |      8 |  2472K |   55384 |          |      1 |     8 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access("I"."ORD_NO"="O"."ORD_NO")

[V$PQ_TQSTAT]  TQ 0 (부분 집계) 147,200 · TQ 1 18,400 · 조인 앞에서 보낸 행 0 · 경과 11.8 → 약 5.1초

모범 답안 서술

11번 줄 PX SEND PARTITION (KEY)(PQ Distrib PART (KEY))에서 주문상품 247만 행이 서버 사이를 오간다. V$PQ_TQSTAT의 TQ 0이 2,472,717행인 것이 그 흔적이다. 반면 주문 쪽은 8번 줄 PX PARTITION HASH ALL로 칸 하나를 PX 서버 하나가 그대로 읽고 옮기지 않는다. ORD_H는 조인 키 ORD_NO로 나뉘어 있어서 칸 하나에 들어 있는 주문이 정해져 있기 때문이다. 그래서 옵티마이저는 주문은 칸대로 두고, 주문상품을 주문의 칸 기준에 맞춰 다시 나눠 보낸다. 한쪽만 칸에 맞는 부분 파티션 와이즈 조인이다. 주문상품이 칸에 맞지 않는 까닭은 해시 키에 있다. HASH (ORD_NO, ITEM_ID)는 두 컬럼을 합친 값으로 칸을 정하므로, 주문 한 건의 상품 세 개가 서로 다른 칸에 흩어진다. 주문상품의 칸 3번에 있는 행이 주문의 칸 3번과 짝이 맞는다는 보장이 없으므로, 칸끼리 조인할 수 없고 큰 쪽인 주문상품이 움직여야 했다. 처방은 주문상품을 조인 키 ORD_NO 하나로, 주문과 같은 8칸으로 다시 나누는 것이다(PARTITION BY HASH (ORD_NO) PARTITIONS 8). 그러면 같은 주문번호는 두 테이블에서 같은 번호의 칸에 들어가므로 칸 한 쌍을 PX 서버 하나가 맡아 조인한다(완전 파티션 와이즈 조인). 계획에서 PX PARTITION HASH ALL이 HASH JOIN 위로 올라가고, 조인 앞의 PX SEND와 PX RECEIVE가 사라진다. 서버 사이로 가는 것은 부분 집계 147,200행과 최종 18,400행뿐이고, 경과 시간은 11.8 → 약 5.1초로 준다. 읽는 블록은 77,894 그대로다. 두 테이블은 칸 수도 같아야 한다. 해시 칸은 8, 16처럼 2의 거듭제곱으로 정해야 칸 크기가 고르다. 칸 한 쌍이 일의 단위가 되므로 DOP를 칸 수(8)보다 크게 잡아도 남는 서버는 놀게 된다.

해설

"PK로 나눠야 고르다"는 오해: ORD_NO 하나도 값이 100만 가지라 8칸에 고르게 퍼진다. 해시 키는 고르게 퍼지는지보다 '무엇과 짝을 지을지'(조인 키)로 정한다. 값의 종류가 아주 적은 컬럼일 때만 쏠림을 걱정한다(PART-030, PART-031).
테이블을 다시 짓지 못한다면 지금처럼 부분 파티션 와이즈 조인이 차선이다. 해시 해시 재분배(두 테이블 모두 보내기)나 주문을 여덟 서버에 복제(BROADCAST)하는 것보다는 보내는 양이 적다.
만들 때 준 PARALLEL 8이 테이블에 남으면, 힌트가 없는 다른 조회까지 병렬로 돈다(DML-041). 만든 뒤 NOPARALLEL로 되돌린다.
교재 14화처럼 Free에서는 병렬이 돌지 않는다. 이런 계획은 운영의 EE에서 V$PQ_TQSTAT으로 보낸 행 수를 함께 확인한다(교재 14화 §9).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황