본사 실시간 매출판의 하루 합계가 마감 매출보다 늘 몇 건 적다

실전INSERT유통509 / 700

상황

본사 레거시 유통은 매장 판매를 1분마다 본사 사본 테이블 HQ_SLE로 옮겨 실시간 매출판에 띄운다. 지난달 매장 POS의 판매번호 받기를 고친 뒤부터, 매출판의 하루 합계가 마감 정산보다 늘 2 ~ 5건 적다. 빠진 판매는 다음 날에도 매출판에 나타나지 않는다. 지난달의 변경은 이렇다. 결제 저장 때 번호를 받느라 다른 매장의 저장을 기다린다는 민원이 있어서, 번호를 받는 시점을 결제 저장에서 첫 상품 스캔으로 앞당기고 채번 행은 자율 트랜잭션으로 바로 COMMIT하게 했다. 판매 행(CF10, CF11)은 결제가 끝나야 INSERT하고 COMMIT한다. 본사 전산 담당자는 이렇게 제안했다. "판매번호를 시퀀스로 바꾸고 ORDER를 붙이면 순서가 맞지 않을까요?"

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · SLE_NO 날짜 여덟 자리와 하이픈, 그날의 네 자리 일련번호 · 하루 약 700건
SLE_HMS는 결제 시각이다. 결제 저장의 INSERT가 DB의 SYSDATE로 적고, 바로 COMMIT한다
CF11 판매상품: 1,096,708행 · CF11_PK (SLE_NO, ITM_NO) · 판매당 약 2.2행
CF10_NUM 판매번호 채번 행(지난달에 만들었다): PK (SLE_DT) · 번호는 자율 트랜잭션으로 받고 바로 COMMIT한다
HQ_SLE 본사 사본(이 문제를 위해 더한 테이블): PK (SLE_NO) · SLE_DT · SHP_NO · SLE_AMT
첫 상품 스캔부터 결제 COMMIT까지는 보통 20 ~ 90초다. 손님이 지갑을 찾으면 몇 분이 걸린다

원본 SQL

sql

-- 1분마다 — 지난번에 옮긴 마지막 판매번호 뒤부터 옮긴다
INSERT INTO HQ_SLE (SLE_NO, SLE_DT, SHP_NO, SLE_AMT)
SELECT h.SLE_NO, h.SLE_DT, h.SHP_NO, SUM(d.SLE_QTY * d.SLE_UPRC)
  FROM CF10 h
  JOIN CF11 d ON d.SLE_NO = h.SLE_NO
 WHERE h.SLE_NO > :last_sle_no
 GROUP BY h.SLE_NO, h.SLE_DT, h.SHP_NO;

SELECT MAX(SLE_NO) INTO :last_sle_no FROM HQ_SLE;    -- 다음 번의 시작점
COMMIT;

실행계획 학습용 예시

xplan

[시간표 — 2023-12-16(토)]
시각        매장 1002 POS (SID 41)                  매장 1004 POS (SID 58)                  1분 추출 (SID 90)
10:00:05    첫 스캔 → 번호 20231216-0412 (자율 COMMIT)
10:00:20                                            첫 스캔 → 번호 0413 (자율 COMMIT)
10:00:41                                            결제 · CF10 · CF11 INSERT · COMMIT
10:01:00                                                                                    SLE_NO > '…-0411' → 0413 을 옮긴다
                                                                                            (0412 는 COMMIT 전이라 안 보인다)
                                                                                            다음 시작점 '…-0413'
10:01:12    결제 · CF10 · CF11 INSERT · COMMIT
10:02:00                                                                                    SLE_NO > '…-0413' → 0412 는 범위 밖

[12월 둘째 주 · 하루 대사]
날짜        마감 정산 판매   HQ_SLE   빠진 판매번호
12/11(월)        684          681    0233 · 0571 · 0602
12/16(토)        731          726    0412 · 0498 · 0517 · 0603 · 0688
  빠진 판매는 모두 번호를 받고 나서 다음 번호보다 늦게 COMMIT 한 판매다

[추출 한 번 — ALLSTATS LAST · 10:01:00]
------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |         |      1 |        |      0 |00:00:00.01 |      21 |
|   1 |  LOAD TABLE CONVENTIONAL       | HQ_SLE  |      1 |        |      0 |00:00:00.01 |      21 |
|   2 |   HASH GROUP BY                |         |      1 |      1 |      1 |00:00:00.01 |      12 |
|   3 |    NESTED LOOPS                |         |      1 |      2 |      2 |00:00:00.01 |      12 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CF10    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  5 |      INDEX RANGE SCAN          | CF10_PK |      1 |      1 |      1 |00:00:00.01 |       3 |
|   6 |     TABLE ACCESS BY INDEX ROWID| CF11    |      1 |      2 |      2 |00:00:00.01 |       8 |
|*  7 |      INDEX RANGE SCAN          | CF11_PK |      1 |      2 |      2 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("H"."SLE_NO">:LAST_SLE_NO)
   7 - access("D"."SLE_NO"="H"."SLE_NO")

문제

0412가 왜 영영 옮겨지지 않는지 번호를 받는 시점과 COMMIT 시점, 읽기 일관성으로 설명하시오.
빠지는 판매가 없도록 고치시오. 추출 쪽을 고치는 방법과 번호 받기 쪽을 고치는 방법을 비교하고, 하나를 골라 SQL로 작성하시오.
본사 전산 담당자의 "시퀀스와 ORDER" 제안에 답하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 1분마다 — 오늘 판매 가운데 최근 10분 안에 결제된 것을 다시 읽고, 아직 없는 판매만 옮긴다 (겹쳐 읽고 걸러 넣는다)
MERGE INTO HQ_SLE t
USING (SELECT h.SLE_NO, h.SLE_DT, h.SHP_NO, SUM(d.SLE_QTY * d.SLE_UPRC) AS SLE_AMT
         FROM CF10 h
         JOIN CF11 d ON d.SLE_NO = h.SLE_NO
        WHERE h.SLE_NO LIKE :today || '-%'          -- 오늘 판매 범위 (CF10_PK)
          AND h.SLE_HMS >= :from_hms                -- 지난번 실행 시각 - 10분
        GROUP BY h.SLE_NO, h.SLE_DT, h.SHP_NO) s
   ON (t.SLE_NO = s.SLE_NO)
 WHEN NOT MATCHED THEN
      INSERT (SLE_NO, SLE_DT, SHP_NO, SLE_AMT)
      VALUES (s.SLE_NO, s.SLE_DT, s.SHP_NO, s.SLE_AMT);
COMMIT;
-- 자정을 넘긴 10분은 어제 날짜로 한 번 더 돈다 · 하루 한 번 마감 뒤 CF10 하루치와 HQ_SLE 건수를 대사한다

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

xplan

[고친 뒤 — 같은 토요일 (학습용 예시)]
10:01:00  최근 10분 결제분 14건을 다시 읽는다 → HQ_SLE 에 없는 0413 만 넣는다
10:02:00  최근 10분 결제분 15건 → 10:01:12 에 결제된 0412 가 새로 보인다 → 넣는다
  하루 대사   12/16 마감 731 · HQ_SLE 731 · 빠진 판매 0
  추출 한 번  Buffers 21 → 약 60 (오늘 판매 범위 + 판매상품 + HQ_SLE PK 확인)

모범 답안 서술

판매번호는 첫 상품 스캔 때 받고 채번 행은 자율 트랜잭션으로 바로 COMMIT되지만, 판매 행은 결제가 끝나야 COMMIT된다. 그래서 번호 순서와 COMMIT 순서가 다르다. 0412는 10:00:05에 번호를 받았지만 10:01:12에야 COMMIT됐고, 0413은 늦게 받았지만 먼저(10:00:41) COMMIT됐다. 10:01:00의 추출은 문장이 시작한 시점의 데이터를 읽으므로(읽기 일관성) COMMIT 전인 0412는 보이지 않고 0413만 옮긴 뒤, 다음 시작점을 0413으로 올린다. 10:02:00의 추출은 SLE_NO > '…-0413'만 읽으므로, 그 뒤에 COMMIT된 0412는 영영 범위 밖에 남는다. 하루 대사에서 빠진 판매가 모두 번호를 받고 나서 다음 번호보다 늦게 COMMIT한 판매라는 것이 그 증거다. 고치는 방법은 둘이다. ① 번호 받기 쪽: 자율 트랜잭션을 걷어 내고 번호를 판매 트랜잭션 안에서, 결제 저장 맨 끝에 받는다. 채번 행의 락이 COMMIT까지 가므로 다음 판매는 앞 판매가 COMMIT해야 번호를 받는다. 번호 순서와 COMMIT 순서가 같아져서 지금 추출이 맞게 된다. 대가는 지난달에 없앤 기다림이 짧게(저장 수 ms) 돌아온다는 것과, 추출의 정확성이 채번 구현에 묶인다는 것이다. 누군가 채번을 다시 고치면 같은 장애가 조용히 돌아온다. ② 추출 쪽: 마지막 번호 뒤부터 읽지 않는다. 오늘 판매 가운데 최근 10분 안에 결제된 판매를 매번 다시 읽고, HQ_SLE에 아직 없는 판매만 MERGE의 WHEN NOT MATCHED로 넣는다. 결제 시각 SLE_HMS는 INSERT할 때 DB가 적고 INSERT 직후 COMMIT하므로, 늦게 COMMIT된 판매도 다음 추출의 10분 창 안에 들어온다. 대가는 겹쳐 읽는 양이다. 한 번에 십여 건, 블록으로는 수십 개라서 1분 주기에 부담이 없다(21 → 약 60블록). ②를 고른다. 번호를 언제 어떻게 받든 맞게 동작하고, 채번의 기다림도 돌아오지 않는다. 전제는 INSERT와 COMMIT 사이가 짧다는 것이므로 그 전제를 추출 프로그램과 운영 문서에 적어 둔다. 이미 빠진 판매는 마감 정산과 HQ_SLE를 하루 단위로 대사해 채우고, 앞으로도 하루 한 번 대사를 돌려 빠진 판매가 0건인지 확인한다. 시퀀스와 ORDER 제안은 답이 아니다. ORDER는 RAC에서 인스턴스끼리 번호를 요청 순서대로 주게 하는 옵션일 뿐이고, 번호 순서대로 COMMIT된다는 보장은 시퀀스에도 없다. 0412와 0413의 문제는 그대로 남는다. 게다가 시퀀스는 날마다 0001부터 다시 시작하지 못하므로 이 판매번호 모양에도 맞지 않는다.

해설

'마지막 번호 뒤부터'는 번호 순서와 COMMIT 순서가 같을 때만 맞다. 시퀀스, 자율 트랜잭션 채번, 미리 받아 둔 번호는 모두 그 둘이 다르다. 증분 추출의 기준은 번호가 아니라 COMMIT된 뒤에 확실히 보이는 것으로 잡거나, 겹쳐 읽고 거른다.
창의 크기는 INSERT부터 COMMIT까지 가장 긴 시간보다 커야 한다. 이 문제는 결제 저장이 INSERT 직후 COMMIT하므로 10분이면 넉넉하다. INSERT를 먼저 하고 사람을 기다린 뒤 COMMIT하는 모양이라면 창을 그만큼 넓히거나 다른 기준을 찾아야 한다.
ORA_ROWSCN(행이나 블록이 마지막으로 COMMIT된 SCN)을 기준으로 삼는 방법도 있다. 다만 기본 설정에서는 블록 단위라 같은 블록의 다른 행이 바뀌어도 값이 바뀌고, 행 단위로 쓰려면 테이블을 ROWDEPENDENCIES로 다시 만들어야 한다.
반품이나 정정처럼 이미 옮긴 판매가 나중에 바뀌는 일은 이 추출이 다루지 않는다. 그런 변경까지 옮겨야 한다면 변경 로그 테이블이나 CDC 도구가 필요하다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황