전송 작업을 넷으로 늘렸는데 점심 큐는 하나일 때처럼 쌓인다

중급PLSQL버거522 / 700

상황

버거 본사는 매장 POS 주문을 가맹 정산 시스템으로 보내는 전송 큐를 둔다. POS가 주문을 넣을 때마다 큐에 한 행이 쌓이고, 본사의 전송 작업이 100건씩 집어 보낸 뒤 DONE으로 바꾼다. 점심 피크에 큐가 1만 건 넘게 쌓여 정산 시스템의 매출이 40분씩 늦어지자, 지난주 전송 작업을 하나에서 넷으로 늘렸다. 쌓이는 모양은 그대로다. 개발팀은 이렇게 말한다. "SKIP LOCKED를 넣었으니 넷이 서로 다른 건을 집을 거예요. 정산 시스템이 느린 것 같아요."

테이블과 인덱스

ORD_SEND_Q 전송 큐(이 문제의 파생 테이블): Q_SEQ (PK, 시퀀스) · ORD_NO · SHOP_NO · SEND_ST (READY · DONE) · REG_DTT · 인덱스 ORD_SEND_Q_X01 (SEND_ST, Q_SEQ) · DONE 행은 밤에 지운다
점심 피크(11:30~13:00)에는 1분에 300건 남짓 쌓인다.
정산 시스템은 100건 묶음 하나를 받는 데 약 30초 걸린다(건마다 검증). 작업 하나는 1분에 약 200건을 보낸다.
작업 넷(W1~W4)은 DBMS_SCHEDULER 잡으로 같은 프로시저를 돈다.

원본 SQL

sql

PROCEDURE SEND_WORKER IS
  TYPE t_q IS TABLE OF ORD_SEND_Q%ROWTYPE;
  v_q t_q;
BEGIN
  LOOP
    SELECT * BULK COLLECT INTO v_q
      FROM ORD_SEND_Q
     WHERE SEND_ST = 'READY'
       AND ROWNUM <= 100
       FOR UPDATE SKIP LOCKED;                  -- 남이 잡은 건은 건너뛴다

    IF v_q.COUNT = 0 THEN
      DBMS_SESSION.SLEEP(30);                   -- 큐가 비었으면 30초 쉰다
    ELSE
      PKG_STLM_IF.SEND(v_q);                    -- 정산 시스템으로 보낸다 (100건에 약 30초)
      FORALL i IN 1 .. v_q.COUNT
        UPDATE ORD_SEND_Q SET SEND_ST = 'DONE' WHERE Q_SEQ = v_q(i).Q_SEQ;
      COMMIT;
    END IF;
  END LOOP;
END;

실행계획 학습용 예시

xplan

[「다음 100건」 한 번 — W2 · W1 이 100건을 쥐고 보내는 중]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |      0 |00:00:00.01 |     104 |
|   1 |  FOR UPDATE                    |                |      1 |        |      0 |00:00:00.01 |     104 |
|*  2 |   COUNT STOPKEY                |                |      1 |        |    100 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID | ORD_SEND_Q     |      1 |    100 |    100 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN           | ORD_SEND_Q_X01 |      1 |    100 |    100 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("SEND_ST"='READY')

[작업 넷 — 12:00 ~ 12:10]
작업   집은 묶음   보낸 건    0건을 받고 쉰 횟수
W1        19       1,900          0
W2         1         100         18
W3         0           0         20
W4         1         100         19
큐 (READY)  12:00 9,850건 → 12:10 10,920건   (그동안 3,170건이 들어왔다)

[V$SESSION — 12:05:00]
SID   EVENT                SECS   하고 있는 일
311   TCP Socket (KGAS)      12   PKG_STLM_IF.SEND (W1 · 100건을 쥔 채 보내는 중)
318   PL/SQL lock timer      21   DBMS_SESSION.SLEEP (W2)
325   PL/SQL lock timer       7   DBMS_SESSION.SLEEP (W3)
332   PL/SQL lock timer      28   DBMS_SESSION.SLEEP (W4)
(enq: TX - row lock contention 은 하나도 없다)

문제

실행계획과 작업 넷의 기록을 근거로 SKIP LOCKED를 넣었는데도 작업 셋이 놀고 있는 이유를 설명하시오.
넷이 동시에 서로 다른 100건을 집도록 고치시오. 고친 판에서 주의할 점도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE SEND_WORKER IS
  CURSOR c IS
    SELECT *
      FROM ORD_SEND_Q
     WHERE SEND_ST = 'READY'
     ORDER BY Q_SEQ
       FOR UPDATE SKIP LOCKED;                -- ROWNUM 없이: 잠긴 건을 건너뛰며 가져오는 행만 잠근다
  TYPE t_q IS TABLE OF c%ROWTYPE;
  v_q t_q;
BEGIN
  LOOP
    OPEN c;
    FETCH c BULK COLLECT INTO v_q LIMIT 100;  -- 잠기지 않은 100건을 채울 때까지 읽는다
    CLOSE c;
    IF v_q.COUNT = 0 THEN
      DBMS_SESSION.SLEEP(5);                  -- 정말 비었을 때만 쉰다 (30초 → 5초)
    ELSE
      BEGIN
        PKG_STLM_IF.SEND(v_q);
        FORALL i IN 1 .. v_q.COUNT
          UPDATE ORD_SEND_Q SET SEND_ST = 'DONE' WHERE Q_SEQ = v_q(i).Q_SEQ;
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;                           -- 보내지 못한 100건은 READY 로 남아 다른 작업이 다시 집는다
          PKG_LOG.WRITE('SEND FAIL ' || SQLERRM);
      END;
    END IF;
  END LOOP;
END;

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

xplan

[「다음 100건」 한 번 — 고친 판 · W2 · W1 이 앞의 100건을 쥐고 있을 때 (학습용 예시)]
  FETCH 1번   잠긴 100건을 건너뛰고 그다음 100건을 가져와 잠근다 · Buffers 약 210 · 0.01초

[작업 넷 — 같은 12:00 ~ 12:10]
  작업마다 약 19묶음 · 보낸 건 약 7,600 · 0건을 받고 쉰 횟수 0
  큐 (READY)  12:00 9,850건 → 12:10 5,420건 → 12:22 0건 (1분에 약 800건 · 유입 300건 남짓)

모범 답안 서술

실행계획을 보면 FOR UPDATE(1번)가 COUNT STOPKEY(2번) 위에 있다. ROWNUM <= 100이 먼저 적용되어 READY 중 앞의 100행이 후보로 정해지고, SKIP LOCKED는 그 100행 안에서만 잠긴 행을 건너뛴다. W1이 그 100행을 쥐고 정산 시스템으로 보내는 30초 동안 W2가 같은 SELECT를 실행하면, 같은 100행이 후보가 되고 전부 잠겨 있으므로 0건이 된다(2번 A-Rows 100 → 1번 A-Rows 0이고, Buffers 4 → 104는 100행을 잠그러 갔다가 건너뛴 몫이다). 0건을 받은 작업은 큐가 빈 줄 알고 30초를 쉰다. 그래서 10분 동안 W1이 19묶음을 보낼 때 나머지 셋은 합쳐 2묶음을 보냈고, 쉰 횟수가 18~20번이다. V$SESSION에 enq: TX가 하나도 없는 것도 같은 이유다. 기다리지 않고 건너뛰었기 때문이다. 정산 시스템이 느린 것이 아니다. 고친 판은 ROWNUM으로 자르지 않는다. 커서를 FOR UPDATE SKIP LOCKED로 열고 FETCH … BULK COLLECT … LIMIT 100으로 100행을 가져온 뒤 커서를 닫는다. SKIP LOCKED 커서는 가져오는 행만 잠그고, 잠긴 행을 건너뛰며 잠기지 않은 100행을 채울 때까지 읽는다. 그러면 W2는 W1이 쥔 100행 다음의 100행을, W3은 그다음 100행을 받는다. 넷이 동시에 보내므로 1분에 약 800건을 보낼 수 있어 점심 유입(1분 300건 남짓)을 따라잡는다. 주의할 점은 셋이다. 첫째, SKIP LOCKED는 순서를 보장하지 않는다. 묶음끼리 Q_SEQ 순서로 나가지 않으므로 정산 시스템이 순서에 기대지 않는지 확인한다. 둘째, 보내다 실패하면 ROLLBACK해서 그 100건을 READY로 남긴다. 그래야 다른 작업이 다시 집는다. 셋째, 100건을 보내는 30초 동안 그 행들을 쥐지만 다른 작업은 건너뛰므로 기다리지 않는다. 큐가 정말 비었을 때만 쉬므로 쉬는 시간은 30초에서 5초로 줄였다.

해설

ROWNUM과 SKIP LOCKED를 함께 쓰는 실수는 흔하다. ROWNUM은 행을 잠그기 전에 자르므로 '잠기지 않은 100건'이 아니라 '앞의 100건 중 잠기지 않은 것'을 준다. 작업이 많을수록 0건을 받는 작업이 늘어 오히려 손해다. 이 판의 증상은 기다림(enq: TX)이 아니라 노는 작업이라 대기 이벤트만 보면 놓친다.
옵션 없는 FOR UPDATE 커서는 OPEN할 때 결과 전체를 잠그지만, SKIP LOCKED 커서는 FETCH할 때 가져오는 행만 잠근다. 그래서 SKIP LOCKED 대기열은 커서를 열고 필요한 만큼만 FETCH하는 모양으로 쓴다(LOCK-004의 출고 대기열과 같은 원리).
100건 묶음 하나를 30초 동안 한 트랜잭션으로 쥐는 대신, 집은 뒤 바로 SENDING으로 바꿔 COMMIT하고 보내는 방법도 있다. 락을 쥐는 시간은 줄지만, 보내다 죽은 SENDING 건을 되돌리는 정리 작업이 따로 필요하다.
큐 테이블의 인덱스 (SEND_ST, Q_SEQ)는 READY를 Q_SEQ 순서로 찾게 해 준다. DONE 행을 밤에 지우지 않으면 인덱스 앞쪽에 지운 항목이 쌓여 읽는 블록이 늘어난다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황