12월 카드 승인 재대사를 작업 8개로 나눠 돌렸는데 한 작업만 12분 넘게 돌고 일곱은 1초 만에 끝났다

중급PLSQL버거489 / 700

상황

버거원 본사는 카드사의 요청으로 2013년 12월 주문 전부의 카드 승인 내역을 다시 대사한다. 주문 한 건마다 카드사 중계 서버에 승인 내역을 묻고 결과를 대사 테이블에 적는데, 한 건에 평균 18ms가 걸린다(대부분 중계 서버의 응답 대기). 중계 서버는 동시 연결을 8개까지 받는다. 개발자는 DBMS_PARALLEL_EXECUTE로 주문번호를 8조각으로 나눠 작업 8개로 돌렸다. 그런데 작업 7개는 1초 안에 끝났고 한 작업만 12분 48초를 돌았다. 오과장: "한 줄로 돌린 것과 똑같잖아요. 작업을 16개로 늘리면 될까요?"

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록(블록당 약 45행) · ORD_NO NUMBER 1 ~ 1,000,000(빈 번호 없음, 주문 일시 순으로 매겼다) · 테이블도 주문번호 순서로 쌓였다
인덱스 ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · 2013년 12월 주문 42,408건은 ORD_NO 957,593 ~ 1,000,000이다(ORD_X01 리프 약 140블록, 테이블 끝의 951블록).
PKG_CARD_IF.RECON(p_ord_no): 주문 한 건의 승인 내역을 중계 서버에 묻고 대사 테이블 CARD_RECON(이 문제용, PK ORD_NO)에 한 행을 넣는다. 주문끼리 같은 행을 고치지 않는다.
배치 계정에는 CREATE JOB 권한이 있다.

원본 SQL

sql

-- 청크 하나가 하는 일 (RUN_TASK 가 청크마다 부르고, 끝나면 커밋한다)
PROCEDURE P_CARD_RECON(p_start NUMBER, p_end NUMBER) IS
BEGIN
  FOR r IN (SELECT ORD_NO
              FROM ORD
             WHERE ORD_NO BETWEEN p_start AND p_end
               AND ORD_DT BETWEEN '20131201' AND '20131231') LOOP
    PKG_CARD_IF.RECON(r.ORD_NO);                             -- 중계 서버 왕복 · 평균 18ms
  END LOOP;
END;

-- 작업 등록 (주문번호를 125,000씩 8조각 · 동시 작업 8개)
BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_TASK('RECON_201312');
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_NUMBER_COL('RECON_201312', 'DEV', 'ORD', 'ORD_NO', chunk_size => 125000);
  DBMS_PARALLEL_EXECUTE.RUN_TASK('RECON_201312', 'BEGIN P_CARD_RECON(:start_id, :end_id); END;',
                                 DBMS_SQL.NATIVE, parallel_level => 8);
END;

실행계획 학습용 예시

xplan

[USER_PARALLEL_EXECUTE_CHUNKS — 작업 RECON_201312]
  CHUNK_ID    START_ID      END_ID    대상 주문    경과
      1             1      125,000          0      0.3초
      2       125,001      250,000          0      0.3초
      …
      7       750,001      875,000          0      0.3초
      8       875,001    1,000,000     42,408      12분 48초
  작업 전체 경과 12분 49초 · 일한 작업 수: 처음 1초 동안 8개, 그 뒤로 1개

[청크 1의 커서 — DISPLAY_CURSOR (ALLSTATS LAST)]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |      1 |        |      0 |00:00:00.02 |    1091 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |   5302 |      0 |00:00:00.02 |    1091 |
|*  2 |   INDEX RANGE SCAN                  | ORD_X01 |      1 |  42419 |  42408 |00:00:00.01 |     140 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ORD_NO">=:B2 AND "ORD_NO"<=:B1))
   2 - access("ORD_DT">='20131201' AND "ORD_DT"<='20131231')

문제

작업 8개로 나눴는데도 한 작업만 12분 넘게 돈 이유를 청크 표로 설명하시오. 작업을 16개로 늘리면 어떻게 될지도 쓰시오.
작업마다 비슷한 양을 맡도록 청크를 다시 만들어 작성하시오. 청크 수를 작업 수와 같게 할지 더 많게 할지도 쓰시오.
CREATE_CHUNKS_BY_ROWID로 바꾸면 해결되는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_TASK('RECON_201312');

  -- 대상(12월 주문)만 주문번호 순으로 줄 세워 64등분 → 청크마다 약 663건 · 주문번호가 유일해 범위가 겹치지 않는다
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL('RECON_201312',
    q'[SELECT MIN(ORD_NO), MAX(ORD_NO)
         FROM (SELECT ORD_NO, NTILE(64) OVER (ORDER BY ORD_NO) AS grp
                 FROM ORD
                WHERE ORD_DT BETWEEN '20131201' AND '20131231')
        GROUP BY grp]',
    by_rowid => FALSE);

  -- 작업은 8개 그대로 (중계 서버 동시 연결 한도) · 청크 64개를 끝난 작업부터 하나씩 가져간다
  DBMS_PARALLEL_EXECUTE.RUN_TASK('RECON_201312', 'BEGIN P_CARD_RECON(:start_id, :end_id); END;',
                                 DBMS_SQL.NATIVE, parallel_level => 8);
END;
-- P_CARD_RECON 은 그대로 둔다 (범위 조건과 12월 조건을 함께 건다)
-- 확인: SELECT status, COUNT(*) FROM USER_PARALLEL_EXECUTE_CHUNKS WHERE task_name = 'RECON_201312' GROUP BY status;

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

xplan

[USER_PARALLEL_EXECUTE_CHUNKS — 고친 판]
  청크 64개 · 청크마다 대상 주문 662 ~ 663건 · 청크 하나 11.6 ~ 12.4초
  작업 8개가 청크를 하나씩 가져가고, 끝나면 다음 청크를 가져간다(작업마다 7 ~ 9개)
  작업 전체 경과 12분 49초 → 1분 41초 · 일한 작업 수: 마지막 몇 초를 빼고 8개

[청크 하나의 커서 — DISPLAY_CURSOR (ALLSTATS LAST)]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |      1 |        |    663 |00:00:00.01 |      19 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |     28 |    663 |00:00:00.01 |      19 |
|*  2 |   INDEX RANGE SCAN                  | ORD_PK  |      1 |    663 |    663 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ORD_DT">='20131201' AND "ORD_DT"<='20131231'))
   2 - access("ORD_NO">=:B2 AND "ORD_NO"<=:B1)

[청크를 만드는 SQL]  12월 주문 42,408건 · ORD_X01 + 테이블 1,091블록 · WINDOW SORT 0.05초

모범 답안 서술

CREATE_CHUNKS_BY_NUMBER_COL은 지정한 칸의 최솟값과 최댓값(테이블 전체에서 1과 1,000,000)을 chunk_size 125,000씩 같은 폭으로 자른다. 행 수가 아니라 값의 폭으로 자르므로, 처리할 행이 값의 범위에 고르게 퍼져 있어야 고르게 나뉜다. 그런데 주문번호는 주문 일시 순이라 12월 주문 42,408건은 모두 957,593 ~ 1,000,000에 있다. 그래서 대상은 전부 8번 청크에 들어갔고, 1 ~ 7번 청크는 12월 주문을 읽고 범위로 걸러 0건으로 1초 안에 끝났다. 결국 작업 하나가 42,408건 × 18ms를 혼자 처리해 12분 48초가 걸렸다. 작업을 16개로 늘려도 같은 폭으로 자르면 대상은 여전히 끝의 한두 청크에 몰리므로 경과는 거의 그대로이고, 노는 작업만 늘어난다. 청크는 값의 폭이 아니라 처리할 행 수로 나눈다. 대상인 12월 주문만 주문번호 순으로 줄 세워 NTILE(64)로 같은 수씩 묶고, 묶음마다 처음과 끝 번호를 CREATE_CHUNKS_BY_SQL에 넘긴다. 주문번호는 유일하므로 묶음의 범위가 겹치지 않고, 청크마다 약 663건이 된다. 범위가 좁아져 청크의 커서도 ORD_PK로 자기 범위만 읽는다(19블록). 청크 수는 작업 수(8)보다 여러 배 많게 잡는다. 청크가 8개뿐이면 응답이 느린 청크 하나가 전체의 끝을 늦추지만, 64개면 먼저 끝난 작업이 남은 청크를 가져가 골고루 나눠 맡는다. 경과는 12분 49초 → 1분 41초로 준다. 중계 서버가 동시 연결을 8개까지 받으므로 작업 수는 8에서 늘리지 않는다. CREATE_CHUNKS_BY_ROWID로 테이블 전체를 나누면 해결되지 않는다. ORD는 주문번호 순서로 쌓여 12월 주문이 테이블 끝의 951블록에 몰려 있으므로, 블록 범위로 나눠도 끝의 한두 청크만 일한다. ROWID로 나누려면 대상 행의 ROWID를 NTILE로 같은 수씩 묶어 by_rowid => TRUE로 넘기거나, 청크를 아주 잘게 만들어 끝의 블록이 여러 청크에 퍼지게 해야 한다.

해설

함정: chunk_size는 '청크마다 행 수'가 아니라 '청크마다 값의 폭'이다. 빈틈없는 번호라도 처리할 행이 한쪽에 몰려 있으면(최근 한 달, 특정 상태) 같은 폭의 청크는 일의 양이 크게 다르다. 값 자체가 치우친 칸으로 나눠도 같다. 판매의 43%가 아메리카노 두 종인 유통 CF11을 ITM_NO로 나누면 그 두 상품이 든 청크 하나가 절반을 맡는다.
NTILE의 범위가 겹치는 경우: 유일하지 않은 키로 줄 세우면 이웃한 두 묶음의 끝 값과 처음 값이 같을 수 있고, 그러면 두 청크가 같은 값의 행을 함께 처리한다. 유일한 키(주문번호)로 묶거나, 값마다 건수를 세어 누적 건수로 나눈다.
대상 목록을 작업 테이블에 담아 1부터 번호를 매기고 그 번호로 CREATE_CHUNKS_BY_NUMBER_COL을 써도 고르게 나뉜다. 대상을 고르는 일을 한 번만 하는 대신 테이블 하나를 더 관리한다.
청크마다 커밋되고 상태가 남으므로 중계 서버 오류로 실패한 청크만 RESUME_TASK로 다시 돌린다. CARD_RECON의 PK가 같은 주문을 두 번 넣지 못하게 막아야 다시 돌려도 안전하다(DML-062, DML-067).
주문마다 독립인 일이라 이렇게 나눌 수 있었다. 여러 청크가 같은 행(예: 매장 합계)을 고치는 일이면 청크를 고르게 나눠도 잠금 대기와 교착이 생긴다(LOCK-021).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황