판매 50만 건을 5,000건씩 넘기는 연동 배치, 뒤쪽 묶음일수록 한 번이 길어진다

중급PLSQL유통325 / 700

상황

본사 DW 연동 배치 P_SEND_CF10은 매일 02시에 판매(CF10) 전체를 DW로 보낸다. DW 쪽 수신 API가 한 번에 5,000건까지만 받아서, 개발자는 판매번호 순으로 정렬해 5,000건씩 OFFSET으로 잘라 보냈다. 첫 묶음은 0.01초인데 마지막 묶음은 0.7초가 걸리고, DB 쪽에서만 35초를 쓴다. DW 담당자는 다른 문제도 알려 왔다. "판매 몇 건이 같은 날 두 번 들어오고, 어떤 판매는 그날 빠졌다가 다음 날 들어온다." 판매는 야간에도 들어오고(POS 지연 전송분은 원래 판매일의 판매번호를 받는다), 취소된 판매는 지워진다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_NO VARCHAR2(15) ('20231231-0612', 앞 8자리가 판매일자) · SLE_DT · SLE_HMS · SLE_DOW · SHP_NO
CF10_PK (SLE_NO): 높이 3 · 리프 약 1,700블록 · 판매번호 순서와 쌓인 순서가 거의 같다(클러스터링 팩터 9,340).
DW는 받은 행을 판매번호로 다시 정렬해 적재하므로 보내는 순서는 상관없다. 한 묶음은 5,000건 이하면 된다. API 호출 한 번은 약 0.2초다.
UNDO_RETENTION은 900초다. 통계는 최신이다.

원본 SQL

sql

PROCEDURE P_SEND_CF10 IS
  TYPE t_tab IS TABLE OF CF10%ROWTYPE;
  v_buf  t_tab;
  v_cnt  NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM CF10;
  FOR k IN 0 .. CEIL(v_cnt / 5000) - 1 LOOP
    SELECT *
      BULK COLLECT INTO v_buf
      FROM CF10
     ORDER BY SLE_NO
     OFFSET k * 5000 ROWS FETCH NEXT 5000 ROWS ONLY;
    DW_API.SEND_CHUNK(v_buf);
  END LOOP;
END;

실행계획 학습용 예시 · TKPROF 요약

xplan

[TKPROF 요약 · P_SEND_CF10 한 번]
SELECT COUNT(*) FROM CF10

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.05       0.05          0       1712          0           1

SELECT * FROM CF10 ORDER BY SLE_NO OFFSET :B1 * 5000 ROWS FETCH NEXT 5000 ROWS ONLY

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.01       0.01          0          0          0           0
Fetch      100     34.62      35.18          0     557389          0      500000

[마지막 묶음 · k = 99]   (첫 묶음 k = 0 은 같은 모양 · A-Rows 5,000 · Buffers 112)
-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |   5000 |00:00:00.69 |   11037 |
|*  1 |  VIEW                          |         |      1 |   5000 |   5000 |00:00:00.69 |   11037 |
|*  2 |   WINDOW NOSORT STOPKEY        |         |      1 |   5000 |    500K|00:00:00.62 |   11037 |
|   3 |    TABLE ACCESS BY INDEX ROWID | CF10    |      1 |    500K|    500K|00:00:00.41 |   11037 |
|   4 |     INDEX FULL SCAN            | CF10_PK |      1 |   5000 |    500K|00:00:00.08 |    1697 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (:B1*5000>=0) THEN
              FLOOR(TO_NUMBER(TO_CHAR(:B1*5000)))+5000 ELSE 5000 END  AND
              "from$_subquery$_002"."rowlimit_$$_rownumber">:B1*5000))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "CF10"."SLE_NO")<=CASE  WHEN (:B1*5000>=0) THEN
              FLOOR(TO_NUMBER(TO_CHAR(:B1*5000)))+5000 ELSE 5000 END )

문제

SELECT 한 번이 읽는 양이 묶음 번호에 따라 어떻게 바뀌는지 쓰고, 전체 557,389블록이 나오는 계산 근거를 보이시오.
DW 쪽이 말한 "두 번 들어온 판매"와 "빠진 판매"가 이 배치의 어느 부분 때문인지 설명하시오.
같은 일을 하면서 CF10을 한 번만 읽고, 묶음 사이에 판매가 들어오거나 지워져도 빠지거나 겹치지 않도록 배치를 고치시오. 고친 방식의 위험도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_SEND_CF10 IS
  CURSOR c IS SELECT * FROM CF10;          -- DW 가 다시 정렬하므로 정렬하지 않는다 · 한 번만 훑는다
  TYPE t_tab IS TABLE OF CF10%ROWTYPE;
  v_buf  t_tab;
BEGIN
  OPEN c;                                  -- 여는 순간의 스냅숏 하나로 50만 건을 끝까지 읽는다
  LOOP
    FETCH c BULK COLLECT INTO v_buf LIMIT 5000;
    EXIT WHEN v_buf.COUNT = 0;
    DW_API.SEND_CHUNK(v_buf);
  END LOOP;
  CLOSE c;
END;

개선 후 실행계획 학습용 예시 · TKPROF 요약

xplan

[TKPROF 요약 · 고친 뒤]
SELECT * FROM CF10

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.24       0.26          0       4682          0      500000

Rows (1st)  Row Source Operation
----------  ---------------------------------------------------
    500000  TABLE ACCESS FULL CF10 (cr=4682 pr=0 pw=0 ...)

모범 답안 서술

k번째 묶음(0부터)은 OFFSET k * 5000이라서, 그 묶음이 어디서 시작하는지 알려고 CF10_PK를 맨 앞부터 (k + 1) × 5,000개 세며 행마다 테이블까지 읽는다. 마지막 묶음의 3번 줄 A-Rows가 500K이고 Buffers는 11,037(인덱스 1,697, 테이블 9,340)이다. 첫 묶음은 약 112블록이다. 묶음 하나가 평균 50.5묶음 분량을 읽으므로 전체는 11,037 × 50.5 ≈ 557,389블록이고, 행으로는 5,000 × 5,050 = 2,525만 행을 지난다. 묶음 수가 두 배가 되면 일은 네 배가 된다(묶음 수의 제곱에 비례). DW가 말한 중복과 누락도 같은 데서 나온다. SELECT가 묶음마다 따로 실행되므로 스냅숏이 100개다. 묶음 사이에 이미 지나간 자리의 판매번호로 지연 전송분이 하나 들어오면 뒤의 행이 한 칸씩 밀려 경계의 판매가 다음 묶음에 한 번 더 나온다(중복). 취소된 판매가 지워지면 한 칸씩 당겨져 경계의 판매 하나를 어느 묶음도 가져가지 않는다(누락). 고친 배치는 커서를 한 번 열고 FETCH … BULK COLLECT … LIMIT 5000으로 5,000건씩 받아 보낸다. DW는 순서를 쓰지 않으므로 ORDER BY를 빼서 CF10을 한 번 FULL로 훑고(멀티블록 읽기), 미리 세던 COUNT(*)도 없앤다. 557,389 → 4,682블록이고(건수 쿼리 1,712블록도 사라진다), 커서를 연 순간의 스냅숏 하나로 끝까지 읽으므로 빠지거나 겹치는 판매가 없다. 위험도 있다. 커서를 오래 열어 두면 그사이 바뀐 블록을 언두로 되돌려 읽어야 하므로 ORA-01555가 날 수 있다. 고친 뒤 전체 시간은 API 0.2초 × 100묶음과 DB 0.3초 정도라 UNDO_RETENTION 900초보다 훨씬 짧다. API가 느려져 전체가 15분에 가까워지면 다시 잰다.

해설

키 기반으로 자르는 다른 답: WHERE SLE_NO > :last ORDER BY SLE_NO FETCH FIRST 5000 ROWS ONLY로 마지막 판매번호 다음부터 읽으면 묶음마다 약 112블록, 전체 약 11,200블록이다. API가 중간에 실패해도 마지막으로 보낸 판매번호부터 다시 시작할 수 있다는 것이 장점이다. 다만 묶음마다 스냅숏이 다르므로 이미 지나간 자리에 들어온 지연 전송분은 그날 보내지 못한다(다음 날 보낸다). 중복은 생기지 않는다.
OFFSET 묶음은 화면 페이징과 같은 병이다. 뒤쪽일수록 앞을 세는 일이 늘어나는데, 배치는 끝까지 전부 넘기므로 합이 제곱이 된다(교재 09화 §8).
행마다 보내지 않고 5,000건씩 묶은 것은 이미 잘한 일이다. 남은 문제는 묶음을 만드는 방법이었다. BULK COLLECT LIMIT의 크기와 PGA 사용량은 교재 15화에서 다룬다.
DW가 순서를 필요로 한다면 ORDER BY SLE_NO를 남기고 CF10_PK를 따라 한 번 읽는다(약 11,037블록). 그래도 557,389블록의 50분의 1이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · TKPROF 요약 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · TKPROF 요약 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황