시퀀스로 바꾼 뒤 정산번호가 군데군데 빈다

중급DDL한빛유통506 / 700

상황

12화의 장애 뒤에 정산번호를 시퀀스 SQ_STLM_SEQ(CACHE 20)로 바꾼 지 한 달이 지났다. 정산팀 조민우 씨가 빈 번호 목록을 들고 왔다. "6월 첫 주에만 45개가 비었어요. 3일 새벽에는 13개가 한꺼번에요. 데이터가 지워진 건 아니죠?" 개발팀은 아래 DDL을 다음 배포에 넣겠다고 한다. 정산번호의 업무 규정은 12화에서 정한 그대로다. 겹치면 안 되고, 결번은 사유를 기록하면 된다.

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록(5월 말 기준) · PK_T_ORD_STLM (STLM_SEQ) · 6월 1일 ~ 4일에 6,214건이 더 들어왔다
시퀀스 SQ_STLM_SEQ: CACHE 20 · NOORDER · 데이터베이스는 단일 인스턴스
정산 등록은 화면(낮)과 보정 배치(밤)가 한다. 화면은 카드사 응답을 받은 뒤 COMMIT하고, 보정 배치는 30건을 등록한 뒤 COMMIT한다
앱 로그, 배치 로그, DBA 작업 대장이 남아 있다 · 결번을 기록하는 곳은 아직 없다

원본 SQL

sql

-- 개발팀 제안 (6/5) — "번호가 비지 않게"
ALTER SEQUENCE SQ_STLM_SEQ NOCACHE ORDER;

실행계획 학습용 예시

xplan

[정산팀이 찾은 빈 번호 — 6월 1일 ~ 4일]
빈 구간               개수   같은 시각의 기록
100412                  1    6/1 10:31 앱 로그 · 정산 등록 화면 · 카드사 응답 시간 초과 → ROLLBACK
100860 ~ 100889        30    6/2 23:40 배치 로그 · 보정 배치 · 30건째에서 ORA-01400 → 통째로 ROLLBACK
101377 ~ 101389        13    6/3 02:10 DBA 작업 대장 · ALTER SYSTEM FLUSH SHARED_POOL (파싱 장애 조치)
101902                  1    6/4 15:02 앱 로그 · 정산 등록 화면 · 사용자가 저장 중 취소 → ROLLBACK
합계                   45

[DBA_SEQUENCES — 6/5 09:00]
SEQUENCE_NAME   CACHE_SIZE   ORDER_FLAG   LAST_NUMBER
SQ_STLM_SEQ             20   N                102,341

[정산 등록 — 6/1 ~ 6/4]
  화면과 배치를 합쳐 초당 최대 4건 · 번호 하나 받기 Buffers 0 (20번에 한 번 SEQ$ 갱신)

문제

빈 구간 넷이 각각 왜 생겼는지 기록과 시퀀스 정의로 설명하시오. 데이터가 지워진 것인지도 판단하시오.
개발팀의 DDL을 넣으면 45개 가운데 몇 개가 사라지는지와 그 대가를 쓰시오. 그 DDL 대신 무엇을 할지 SQL로 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 제안 DDL 은 넣지 않는다 — SQ_STLM_SEQ 는 CACHE 20, NOORDER 그대로

-- 결번 대장 (새로 만든다)
CREATE TABLE T_STLM_GAP (
  GAP_FROM NUMBER, GAP_TO NUMBER, GAP_CNT NUMBER,
  FOUND_DT DATE,   REASON VARCHAR2(200)
);

-- 매일 08:00 — 지난번에 확인한 번호 뒤부터 빈 구간을 찾는다 (PK 를 범위로 한 번 읽는다)
INSERT INTO T_STLM_GAP (GAP_FROM, GAP_TO, GAP_CNT, FOUND_DT)
SELECT STLM_SEQ + 1, NXT - 1, NXT - STLM_SEQ - 1, SYSDATE
  FROM (SELECT STLM_SEQ, LEAD(STLM_SEQ) OVER (ORDER BY STLM_SEQ) AS NXT
          FROM T_ORD_STLM
         WHERE STLM_SEQ >= :last_checked_seq)
 WHERE NXT - STLM_SEQ > 1;
COMMIT;
-- REASON 은 앱 로그 · 배치 로그 · DBA 작업 대장과 맞춰 채운다 (ROLLBACK · 캐시 소실)

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

xplan

[빈 구간 찾기 — 6/5 08:00 · :last_checked_seq = 100001]
------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |               |      1 |        |      0 |00:00:00.01 |      26 |
|   1 |  LOAD TABLE CONVENTIONAL | T_STLM_GAP    |      1 |        |      0 |00:00:00.01 |      26 |
|*  2 |   VIEW                   |               |      1 |   6214 |      4 |00:00:00.01 |      18 |
|   3 |    WINDOW BUFFER         |               |      1 |   6214 |   6214 |00:00:00.01 |      18 |
|*  4 |     INDEX RANGE SCAN     | PK_T_ORD_STLM |      1 |   6214 |   6214 |00:00:00.01 |      18 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("NXT"-"STLM_SEQ">1)
   4 - access("STLM_SEQ">=:LAST_CHECKED_SEQ)

모범 답안 서술

네 구간은 모두 기록으로 설명된다. 100412와 101902는 정산 등록 화면이 번호를 받은 뒤 ROLLBACK한 자리다(카드사 응답 시간 초과, 사용자 취소). 시퀀스는 한 번 나간 번호를 되돌리지 않으므로 ROLLBACK된 번호는 비어서 남는다. 100860 ~ 100889의 30개는 보정 배치가 30건을 등록하다가 ORA-01400으로 통째로 ROLLBACK한 자리다. 101377 ~ 101389의 13개는 6/3 02:10에 공유 풀을 비웠을 때(ALTER SYSTEM FLUSH SHARED_POOL) CACHE 20으로 꺼내 두었던 번호 가운데 아직 쓰지 않은 13개가 사라진 것이다. 따라서 데이터가 지워진 것이 아니다. 지웠다면 그 번호로 등록을 마친 기록이 앱 로그에 있어야 하는데, 네 구간 모두 등록을 마치지 못한 기록뿐이다. 개발팀의 DDL을 넣으면 45개 가운데 캐시 소실로 생긴 13개만 사라진다. ROLLBACK으로 생긴 32개는 NOCACHE로도 막지 못한다. ORDER는 RAC에서 인스턴스끼리 번호 순서를 맞추는 옵션이라 단일 인스턴스에서는 아무것도 바꾸지 않는다. 반면 NOCACHE는 번호마다 딕셔너리 SEQ$를 고치므로(재귀 UPDATE와 redo), 등록이 몰리는 시간에는 enq: SQ - contention 줄이 생긴다. 얻는 것이 작고 대가가 있으므로 넣지 않는다. 대신 결번은 사유를 기록한다는 규정을 지키는 장치를 만든다. 매일 아침 지난번에 확인한 번호 뒤부터 LEAD로 다음 번호와의 차이를 보고, 차이가 1보다 큰 구간을 결번 대장에 넣는다. PK 인덱스를 범위로 한 번 읽으면 되므로 6,214건에 18블록이다. 사유 칸은 앱 로그, 배치 로그, DBA 작업 대장과 맞춰 채운다.

해설

빈 번호를 없애는 시퀀스 옵션은 없다. 시퀀스는 겹치지 않는 번호를 기다림 없이 주는 대신 빈 번호를 남긴다. 빈 번호가 정말 금지라면 같은 트랜잭션 안에서 받는 채번(채번 테이블과 짧은 트랜잭션)을 써야 하고, 그 대가는 줄이다(교재 12화 §6).
셋째 구간처럼 운영 작업이 번호를 날리는 경우가 있다. 공유 풀 비우기, 인스턴스 재시작, ALTER SEQUENCE로 캐시 크기 바꾸기가 그렇다. DBA 작업 대장에 '시퀀스 캐시 소실 가능'을 함께 적어 두면 사유를 찾기 쉽다.
캐시가 컸다면 6/3의 13개는 더 많았을 것이다. 캐시를 키우는 결정과 빈 번호는 함께 따라오므로, 캐시 크기를 정할 때 사유를 기록하는 부담도 함께 적는다.
화면과 출력물에 빈 번호가 보이는 것이 업무에 부담이라면, 시퀀스 번호는 내부 키로만 쓰고 보여 주는 번호(날짜별 순번 등)를 따로 매길 수 있다. 다만 그 번호를 저장하려면 다시 채번 문제가 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황