병렬 8로 돌리는 연간 상품 판매 집계가 새벽 배치들의 병렬 서버를 다 가져간다

중급INSERT버거113 / 700

상황

버거원 본사는 매주 일요일 03시에 「연간 상품 판매 순위」 마트를 2013년 치로 다시 적재한다. 적재 SQL에는 2019년에 붙인 FULL(oi) PARALLEL(oi 8) 힌트가 있다. 지난 일요일, 같은 시간에 도는 매출 집계 배치 두 개가 병렬 서버를 얻지 못해 직렬로 강등되었고(Parallel operations downgraded to serial), 03시 배치 묶음 전체가 40분 늦게 끝났다. 배치 담당 오과장은 이렇게 물었다. "병렬도를 4로 낮추면 되나?"

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록(문자열 채움 컬럼 때문에 행이 넓어 블록당 약 45행) · ORD_DT VARCHAR2(8) 2012 ~ 2013 · 2013년 약 1,236,000행 · ITEM_ID 92종 · ORD_NO와 ITEM_ID는 PK 컬럼이라 NOT NULL
인덱스 ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · ORD_ITEM_X02 (ITEM_ID, ORD_DT, ORD_ITEM_QTY): X02는 「상품 월간 추이」용으로 2019년 뒤에 만들었다 · BLEVEL 2 · 리프 약 8,500블록(리프 한 블록에 약 290항목) · 통계 최신
ITEM_SALE_YEAR 연간 상품 판매 마트(이 문제에서 쓰는 집계 테이블): SALE_YY · ITEM_ID · QTY · LINE_CNT · 해마다 92행 · 적재 전에 그해 행을 지운다
운영 DB는 EE이고 parallel_max_servers = 32 · db_file_multiblock_read_count = 128

원본 SQL

sql

DELETE FROM ITEM_SALE_YEAR WHERE SALE_YY = '2013';

INSERT INTO ITEM_SALE_YEAR (SALE_YY, ITEM_ID, QTY, LINE_CNT)
SELECT /*+ FULL(oi) PARALLEL(oi 8) */
       '2013', oi.ITEM_ID, SUM(oi.ORD_ITEM_QTY), COUNT(*)
  FROM ORD_ITEM oi
 WHERE oi.ORD_DT BETWEEN '20130101' AND '20131231'
 GROUP BY oi.ITEM_ID;
COMMIT;

실행계획 학습용 예시 · 병렬 실행은 운영(EE)의 모양(교재 14화 §9) · SQL Monitor 요약으로 적었다

xplan

[SQL Monitor — INSERT · 일요일 03:00 · DOP 8]
Global Stats
  Duration 14s · DB Time 121s · Buffer Gets 55.9K · Read Reqs 470 · Read Bytes 432MB
  PX Servers Requested 16 · Allocated 16 (생산자 8 + 소비자 8)

=========================================================================================
| Id |          Operation           |      Name      | Execs |  Rows    | Read | Read  |
|    |                              |                |       | (Actual) | Reqs | Bytes |
=========================================================================================
|  0 | INSERT STATEMENT             |                |     1 |        1 |      |       |
|  1 |  LOAD TABLE CONVENTIONAL     | ITEM_SALE_YEAR |     1 |          |      |       |
|  2 |   PX COORDINATOR             |                |    17 |       92 |      |       |
|  3 |    PX SEND QC (RANDOM)       | :TQ10001       |     8 |       92 |      |       |
|  4 |     HASH GROUP BY            |                |     8 |       92 |      |       |
|  5 |      PX RECEIVE              |                |     8 |      736 |      |       |
|  6 |       PX SEND HASH           | :TQ10000       |     8 |      736 |      |       |
|  7 |        HASH GROUP BY         |                |     8 |      736 |      |       |
|  8 |         PX BLOCK ITERATOR    |                |     8 |    1236K |      |       |
|  9 |          TABLE ACCESS FULL   | ORD_ITEM       |   104 |    1236K |  470 | 432MB |
=========================================================================================
   9 - filter(("OI"."ORD_DT">='20130101' AND "OI"."ORD_DT"<='20131231'))

문제

이 집계가 실제로 읽어야 하는 컬럼과 지금 읽고 있는 대상을 비교해, 병렬 8이 무엇을 나눠 읽고 있는지 설명하시오.
병렬 서버를 쓰지 않고도 빨라지도록 SQL을 고치시오. 고친 SQL이 결과를 바꾸지 않는 근거도 쓰시오.
오과장의 질문(병렬도를 4로 낮추면 되나)에 답하시오. 그래도 더 줄여야 할 때 쓸 수 있는 병렬 방법도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

DELETE FROM ITEM_SALE_YEAR WHERE SALE_YY = '2013';

-- 테이블 55,265 블록 대신, 필요한 세 컬럼을 다 담은 X02 (리프 약 8,500) 를 순서 없이 멀티블록으로
INSERT INTO ITEM_SALE_YEAR (SALE_YY, ITEM_ID, QTY, LINE_CNT)
SELECT /*+ INDEX_FFS(oi ORD_ITEM_X02) */
       '2013', oi.ITEM_ID, SUM(oi.ORD_ITEM_QTY), COUNT(*)
  FROM ORD_ITEM oi
 WHERE oi.ORD_DT BETWEEN '20130101' AND '20131231'
 GROUP BY oi.ITEM_ID;
COMMIT;

-- 더 줄여야 할 때만: /*+ INDEX_FFS(oi ORD_ITEM_X02) PARALLEL_INDEX(oi ORD_ITEM_X02 4) */

개선 후 실행계획 학습용 예시 · 병렬 실행은 운영(EE)의 모양(교재 14화 §9) · SQL Monitor 요약으로 적었다

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                |      1 |        |      0 |00:00:03.62 |    8561 |  8538 |
|   1 |  LOAD TABLE CONVENTIONAL | ITEM_SALE_YEAR |      1 |        |      0 |00:00:03.62 |    8561 |  8538 |
|   2 |   HASH GROUP BY          |                |      1 |     92 |     92 |00:00:03.61 |    8546 |  8538 |
|*  3 |    INDEX FAST FULL SCAN  | ORD_ITEM_X02   |      1 |   1236K|   1236K|00:00:02.90 |    8546 |  8538 |
-------------------------------------------------------------------------------------------------------------
   3 - filter(("OI"."ORD_DT">='20130101' AND "OI"."ORD_DT"<='20131231'))

[세션 통계] physical read total IO requests 71 (multi block 67) · PX 서버 0 · 14초 → 3.6초
[비교 · PARALLEL_INDEX(oi ORD_ITEM_X02 4)] 약 1.3초 · PX 서버 8 (생산자 4 + 소비자 4)

모범 답안 서술

병렬 FULL은 테이블 55,265블록(432MB)을 PX 서버 16개(읽는 생산자 8, 묶는 소비자 8)가 나눠 읽는다. 그런데 이 집계에 나오는 컬럼은 ITEM_ID, ORD_DT, ORD_ITEM_QTY 셋뿐이고, 셋 다 ORD_ITEM_X02에 들어 있다. 테이블이 큰 것은 문자열 채움 컬럼 때문에 행이 넓어서인데, 이 집계는 그 컬럼을 하나도 쓰지 않는다. 병렬 8은 필요 없는 블록을 여덟 명이 나눠 읽고 있는 셈이다. X02는 리프 약 8,500블록으로 테이블의 6분의 1 크기다. INDEX FAST FULL SCAN으로 읽으면 테이블에 가지 않고, 순서가 필요 없는 집계이므로 세그먼트를 한 번에 128블록씩 멀티블록으로 읽는다(I/O 요청 약 70번). 결과도 같다. ITEM_ID가 PK 컬럼이라 NOT NULL이므로 모든 행이 X02에 들어 있고, ORD_DT 조건은 인덱스 안에서 filter로 거른다. Buffers는 55.9K → 약 8,561블록, PX 서버는 16 → 0이 되고, 직렬로도 14초 → 약 3.6초다. 오과장의 질문에 대한 답은 '병렬도보다 읽는 대상을 먼저 줄인다'이다. 병렬은 읽는 양을 줄이지 않고 나눌 뿐이다. 병렬도를 4로 낮추면 서버는 8개로 줄지만 55,265블록은 그대로이고 시간만 늘어난다. 서버 16개를 쓰는 동안 같은 시간대의 배치가 서버를 얻지 못해 강등된 것이 이번 지연의 원인이었다. 대상을 8,500블록으로 줄이면 병렬 없이 충분하고, 그래도 더 줄여야 하면 PARALLEL_INDEX(oi ORD_ITEM_X02 4)로 FFS를 블록 범위로 나눠 읽는다(약 1.3초, 서버 8개). 2019년의 FULL 힌트는 X02가 생기기 전의 판단이다. 그때는 테이블을 읽는 것 말고 길이 없었고 병렬이 유일한 단축 수단이었다. 인덱스가 생긴 뒤에는 그 힌트가 더 싼 길을 막고 있었다.

해설

행이 넓은 테이블일수록 필요한 컬럼만 담은 인덱스를 FFS로 읽는 효과가 크다. 반대로 SELECT 목록에 X02에 없는 컬럼(예: ORD_HMS)이 하나라도 들어오면 FFS를 쓸 수 없다. 마트 적재 SQL을 고칠 때 이 전제를 주석으로 남긴다.
INDEX SKIP SCAN으로 상품 92개마다 2013년 구간만 읽는 길도 있다. Buffers는 리프의 절반(약 4,300)으로 더 적지만 한 블록씩 읽고 병렬로 나눌 수도 없다. 1년 치가 인덱스의 절반이나 되는 배치에서는 FFS가 낫다.
병렬 힌트를 지울 때는 그 힌트를 넣은 이유가 아직 유효한지 본다. 이 문제처럼 인덱스가 새로 생기거나 데이터 규모가 바뀌면 전제가 바뀐다(교재 06화 힌트 유효기간).
이 교재의 실습 DB(Free)에서는 병렬 실행이 되지 않는다(교재 14화 §9). 병렬 계획과 서버 수는 운영(EE)의 모양을 옮긴 학습용 예시다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 병렬 실행은 운영(EE)의 모양(교재 14화 §9) · SQL Monitor 요약으로 적었다 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 병렬 실행은 운영(EE)의 모양(교재 14화 §9) · SQL Monitor 요약으로 적었다 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황