매장 결제 저장이 건마다 0.3초씩 걸리고 토요일 오후엔 가끔 저장이 실패한다

모범 답안 SQL ​

-- PKG_POS.SAVE_SALE — 그날 마지막 판매번호를 PK 인덱스 맨 끝에서 한 건만 읽는다
v_try := 0;
LOOP
  BEGIN
    SELECT MAX(SLE_NO) INTO v_last
      FROM CF10
     WHERE SLE_NO LIKE :dt || '-%';                 -- INDEX RANGE SCAN (MIN/MAX) · 3블록

    v_sle_no := :dt || '-' || LPAD(NVL(TO_NUMBER(SUBSTR(v_last, 10, 4)), 0) + 1, 4, '0');

    INSERT INTO CF10 (SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO)
    VALUES (v_sle_no, :dt, :hms, :dow, :shp_no);
    EXIT;                                            -- 넣었으면 끝
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN                       -- 다른 매장이 먼저 썼다 → MAX 부터 다시
      v_try := v_try + 1;
      IF v_try >= 3 THEN RAISE; END IF;              -- 세 번 겹치면 그때는 알린다
  END;
END LOOP;
-- 상품마다 CF11 INSERT → COMMIT

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

[번호 받기 — 고친 뒤 · 같은 시각]
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE              |         |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   FIRST ROW                  |         |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| CF10_PK |      1 |      1 |      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SLE_NO" LIKE :DT||'-%')
       filter("SLE_NO" LIKE :DT||'-%')

  번호 받기 0.29초 → 0.001초 · 번호를 읽기 시작해 COMMIT 하기까지 약 0.45초 → 약 0.02초
  겹칠 틈이 그만큼 줄고, 그래도 겹치면 프로시저가 다시 받는다 (손님은 모른다)

모범 답안 서술 ​

번호를 받는 SQL은 SLE_DT = :dt로 그날의 판매를 찾는데, SLE_DT에는 인덱스가 없어서 판매 한 건마다 CF10 4,583블록을 통째로 읽는다(0.29초). 그날의 512행 가운데 가장 큰 일련번호는 MAX 안에서 SUBSTR과 TO_NUMBER로 가공해서 찾는다. 이 0.29초가 겹침의 창이다. 매장 1003이 번호를 읽기 시작해 COMMIT하기까지 0.45초 동안, 1003이 넣은 행은 다른 매장에 보이지 않는다. 그 사이에 매장 1005가 번호를 읽으면 같은 0513을 받는다. 1005의 INSERT는 CF10_PK에서 1003의 키와 부딪혀 TX를 REQUEST 4로 기다리고, 1003이 COMMIT하자 ORA-00001을 받는다. PK가 겹침 자체는 막지만 결제가 실패한다. 판매번호는 날짜로 시작하므로 그날 판매는 이미 CF10_PK 안에 일련번호 순으로 모여 있다. 그래서 SLE_NO LIKE :dt || '-%'로 그날 범위를 지목하고, MAX를 가공하지 않고 SLE_NO 그대로 잡으면 인덱스 맨 끝 한 건만 읽고 멈춘다(INDEX RANGE SCAN (MIN/MAX), FIRST ROW). 일련번호를 네 자리로 채워 두었으므로 문자로 비교해도 숫자 순서와 같다. 번호 받기가 4,583 → 3블록, 0.29초 → 0.001초가 되어 겹칠 틈이 거의 사라진다. 그래도 같은 순간에 겹치면 DUP_VAL_ON_INDEX를 받아 프로시저가 MAX부터 다시 한다. 분당 6 ~ 8건인 이 규모에서는 드물고, 손님은 결제를 다시 하지 않는다. 시퀀스는 날마다 0001부터 다시 시작할 수 없어 이 번호 모양에 맞지 않고, 날짜별 채번 행을 두는 방법은 테이블 하나와 행 락이 더 든다. 이 규모라면 PK가 지켜 주는 MAX+1에 다시 받기를 더하는 것으로 충분하다.

해설 ​

• 가공한 MAX는 인덱스 맨 끝에서 멈추지 못한다. MAX(TO_NUMBER(SUBSTR(SLE_NO, 10, 4)))는 범위를 인덱스로 찾더라도 그날 700건을 다 읽고 가공해야 최댓값을 안다. 일련번호를 LPAD로 네 자리로 채워 두었기 때문에 SLE_NO 그대로의 MAX가 같은 답을 준다. 자리를 채우지 않은 번호라면('-512'와 '-1000') 문자 비교가 틀린 답을 준다.
• 다른 정답: CF10 (SLE_DT, SLE_NO) 인덱스를 만들고 WHERE SLE_DT = :dt에서 MAX(SLE_NO)를 구해도 같은 3블록이 된다. 다만 인덱스 하나만큼 판매 INSERT 비용이 늘고, PK로 이미 할 수 있는 일이다.
• 날짜별 채번 행(CF10_NUM (SLE_DT, LAST_NO))을 두면 다시 받기가 사라지지만, 번호를 받은 판매가 COMMIT할 때까지 다른 매장이 그 행에서 기다리고, 그날 첫 행을 누가 만들지도 정해야 한다. 매장과 동시 세션이 늘어 다시 받기가 잦아지면 그때 바꾼다.
• 이 방법은 빈 번호를 만들지 않는다. 판매가 ROLLBACK되면 그 번호는 다음 판매가 다시 쓴다. 다만 맨 끝 판매를 지우면 그 번호가 다시 나가므로, 판매를 지우는 일이 있는지 업무 쪽과 확인해 둔다.

관련 교재 ​

• 12화 · 채번: 겹침, 빈 번호, 기다림