혼자 하면 0.1초인 전표 확정이 월말 오후에는 2초씩 걸린다

중급PLSQL한빛유통503 / 700

상황

한빛유통 정산팀은 매달 말일 오후에 고객별 정산을 「전표 확정」 화면으로 확정한다. 담당자 열두 명이 두 시간 동안 약 1,800건을 확정한다. 회계 규정 제14조는 이렇다. "전표번호는 1번부터 결번 없이 이어져야 한다. 결번은 허용하지 않는다." 그래서 전표번호는 시퀀스가 아니라 채번 테이블로 매긴다. 혼자 테스트하면 확정 한 건은 0.12초인데, 월말 오후에는 한 건에 평균 1.9초가 걸린다. 오과장은 "서버가 바빠서 그렇다"라고 했다.

테이블과 인덱스

T_STLM_NUM 채번: PK (NUM_TP) · 이 문제에서는 전표번호 행 NUM_TP = 'SLIP'을 쓴다
T_ORD_STLM 정산: 100,000행 · 640블록 · 이 문제에서는 전표번호 컬럼 SLIP_NO를 더했다 · 인덱스 IX_T_ORD_STLM_CUS (CUS_ID, STLM_DT)
T_STLM_SLIP 전표(이 문제를 위해 더한 테이블): 한 달 약 1,800행 · PK (SLIP_NO)
T_ORD_BIG: 1,000,000행 · 5,826블록 · 확정할 때 주문 원장과 금액을 대어 본다 · CUS_ID로 찾는 인덱스는 없다 · 고객 한 명의 한 달 주문은 약 14건

원본 SQL

sql

PROCEDURE CONFIRM_SLIP(p_cus_id VARCHAR2, p_ym VARCHAR2) IS
  v_no   NUMBER;
  v_amt  NUMBER;
  v_from DATE := TO_DATE(p_ym, 'YYYYMM');
BEGIN
  -- ① 전표번호부터 받는다
  SELECT LAST_NO + 1 INTO v_no FROM T_STLM_NUM WHERE NUM_TP = 'SLIP' FOR UPDATE;
  UPDATE T_STLM_NUM SET LAST_NO = v_no WHERE NUM_TP = 'SLIP';

  -- ② 주문 원장과 금액을 대어 본다
  SELECT SUM(ORD_AMT) INTO v_amt
    FROM T_ORD_BIG
   WHERE CUS_ID = p_cus_id
     AND ORD_DT >= v_from AND ORD_DT < ADD_MONTHS(v_from, 1);

  -- ③ 정산 행에 전표번호를 적고 전표를 만든다
  UPDATE T_ORD_STLM SET SLIP_NO = v_no
   WHERE CUS_ID = p_cus_id AND STLM_DT >= v_from AND STLM_DT < ADD_MONTHS(v_from, 1);
  INSERT INTO T_STLM_SLIP (SLIP_NO, CUS_ID, SLIP_YM, SLIP_AMT, REG_DT)
  VALUES (v_no, p_cus_id, p_ym, v_amt, SYSDATE);

  COMMIT;
END;

실행계획 학습용 예시

xplan

[혼자 한 건 — 단계별 경과]
  ① 번호 받기     0.001초 · Buffers 5 (INDEX UNIQUE SCAN)
  ② 원장 대조     0.11초  · Buffers 5,826 (TABLE ACCESS FULL T_ORD_BIG)
  ③ 전표 쓰기     0.004초 · Buffers 12
  COMMIT          0.003초
  ① 에서 COMMIT 까지 채번 행을 쥐는 시간 약 0.12초

[② 원장 대조 — ALLSTATS LAST]
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.11 |    5826 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.11 |    5826 |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |     14 |     14 |00:00:00.11 |    5826 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("CUS_ID"=:B3 AND "ORD_DT">=:B2 AND "ORD_DT"<ADD_MONTHS(:B2,1)))

[월말 오후 — 열두 명 · 14:00 ~ 16:00]
  확정 1,804건 · 한 건 평균 1.9초 (그중 기다림 1.6초)
  enq: TX - row lock contention 1,690번 · 평균 1.7초 · 대상 T_STLM_NUM ('SLIP' 행)

[V$SESSION — 14:37:12]
SID   EVENT                            SECS  BLOCKING_SESSION  하는 문장
121   db file scattered read              0                    SELECT SUM(ORD_AMT) … T_ORD_BIG …
140   enq: TX - row lock contention       1  121               SELECT LAST_NO + 1 … FOR UPDATE
152   enq: TX - row lock contention       1  121               SELECT LAST_NO + 1 … FOR UPDATE
 …    (아홉 명 더 · 모두 BLOCKING_SESSION 121)

문제

혼자 0.12초인 확정이 열두 명일 때 1.9초가 되는 이유를 단계별 경과와 대기 이벤트로 설명하시오.
전표번호에 결번이 생기지 않는다는 규정을 지키면서 기다림을 줄이도록 프로시저를 고치시오. 시퀀스나 자율 트랜잭션 채번으로 바꾸면 안 되는 이유도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE CONFIRM_SLIP(p_cus_id VARCHAR2, p_ym VARCHAR2) IS
  v_no   NUMBER;
  v_amt  NUMBER;
  v_from DATE := TO_DATE(p_ym, 'YYYYMM');
BEGIN
  -- ① 번호가 필요 없는 일부터 — 락 없이 열두 명이 동시에 한다
  SELECT SUM(ORD_AMT) INTO v_amt
    FROM T_ORD_BIG
   WHERE CUS_ID = p_cus_id
     AND ORD_DT >= v_from AND ORD_DT < ADD_MONTHS(v_from, 1);

  -- ② 번호는 맨 끝에서 한 문장으로 — 여기서부터 COMMIT 까지만 채번 행을 쥔다
  UPDATE T_STLM_NUM SET LAST_NO = LAST_NO + 1
   WHERE NUM_TP = 'SLIP'
  RETURNING LAST_NO INTO v_no;

  UPDATE T_ORD_STLM SET SLIP_NO = v_no
   WHERE CUS_ID = p_cus_id AND STLM_DT >= v_from AND STLM_DT < ADD_MONTHS(v_from, 1);
  INSERT INTO T_STLM_SLIP (SLIP_NO, CUS_ID, SLIP_YM, SLIP_AMT, REG_DT)
  VALUES (v_no, p_cus_id, p_ym, v_amt, SYSDATE);

  COMMIT;   -- 실패하면 ROLLBACK 과 함께 LAST_NO 도 되돌아간다 → 결번 없음
END;

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

xplan

[고친 뒤 — 월말 오후 같은 구간 (학습용 예시)]
  채번 행을 쥐는 시간   0.12초 → 0.006초 (② ~ COMMIT)
  확정 한 건 평균       1.9초 → 0.4초 (원장 대조 열두 개가 CPU 2개를 나눠 쓰는 시간)
  enq: TX - row lock contention   1,690번 · 평균 1.7초 → 210번 · 평균 0.004초

모범 답안 서술

채번 행의 락은 FOR UPDATE를 한 순간부터 COMMIT할 때까지 간다. 원본은 ①에서 번호를 받은 다음 ② 원장 대조(T_ORD_BIG FULL, 5,826블록, 0.11초)를 하므로, 채번 행을 쥔 채 테이블 전체를 읽는다. 혼자면 0.12초지만 열두 명이 동시에 누르면 모두 그 0.12초짜리 구간 앞에 한 줄로 선다. V$SESSION에서 열한 명이 enq: TX - row lock contention으로 SID 121을 기다리고, 121은 그동안 db file scattered read로 원장을 읽고 있다. 기다림은 1,690번, 평균 1.7초이고, 한 건 1.9초 가운데 1.6초가 줄을 선 시간이다. 번호가 꼭 필요한 곳은 ③뿐이다. 그래서 번호가 필요 없는 원장 대조를 먼저 하고, 번호는 맨 끝에서 UPDATE … RETURNING 한 문장으로 받은 뒤 바로 전표를 쓰고 COMMIT한다. 채번 행을 쥐는 시간이 0.12초 → 0.006초로 줄고, 원장 대조는 락 없이 열두 명이 동시에 한다(한 건 1.9초 → 0.4초). 시퀀스나 자율 트랜잭션 채번으로 바꾸면 기다림은 0이 되지만 규정을 어긴다. 둘 다 번호가 업무 트랜잭션 밖에서 확정되므로 확정이 실패해 ROLLBACK해도 번호는 돌아오지 않고 결번이 된다. 같은 트랜잭션에서 받으면 실패할 때 LAST_NO도 함께 되돌아가므로 결번이 생기지 않는다. 대가도 있다. 결번 없는 번호는 결국 한 사람씩 받아야 하므로 줄은 남는다. 줄을 짧게 할 수 있을 뿐이고, 번호를 받은 뒤에 하는 일(③)이 길어지면 줄도 다시 길어진다.

해설

"번호부터 받는다"는 습관이 줄을 길게 만든다. 번호를 먼저 받아 두면 코드는 쓰기 편하지만, 그 순간부터 COMMIT까지가 모두 줄의 길이가 된다(교재 12화 §6).
원장 대조를 빠르게 하는 T_ORD_BIG (CUS_ID, ORD_DT, ORD_AMT) 인덱스도 도움이 된다. 하지만 순서를 바꾸지 않으면 줄 앞에서 하는 일이 짧아질 뿐 줄은 그대로다. 순서를 먼저 바꾸고, 인덱스는 적재 비용과 함께 따로 따진다.
같은 채번이라도 요구 원문이 답을 가른다. 12화의 정산번호는 감사 원문이 '결번 사유 기록'이어서 시퀀스가 답이었다. 이 문제의 규정은 결번 자체를 금지하므로 같은 트랜잭션 안의 채번이 답이다.
번호를 받은 뒤 실패할 수 있는 검사(제약 위반, 금액 불일치)는 모두 번호 앞으로 옮긴다. 번호를 받은 뒤에는 실패할 일이 없게 만드는 것이 줄을 짧게 유지하는 방법이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황