보정 누락분을 한 번에 넣은 7분 동안 보정 화면 11개가 멈춰 있었다

기초INSERT한빛유통459 / 700

상황

월요일 10:24, 정산팀 조민우가 지난주 보정에서 빠진 정산 1,240건을 SQL Developer로 한 번에 넣었다. 교재에서 본 대로 /*+ APPEND */를 붙였고 문장은 0.06초 만에 끝났다. 넣은 건수와 금액을 맞춰 본 뒤 10:31에 커밋했다. 그 7분 동안 보정 화면을 쓰던 정산팀 11명의 저장 버튼이 돌기만 했다. 화면 담당자: "보정 한 건 저장은 늘 0.01초인데, 그 시간에만 전부 멈췄어요."

테이블과 인덱스

T_STLM_ADJ 보정 이력(교재 15화): 98,780행 · 약 420블록 · PK_T_STLM_ADJ (STLM_SEQ) · 낮에는 보정 화면이 한 건씩 INSERT하거나 UPDATE한다(시간당 약 300건).
T_ORD_STLM 정산: 100,020행 · 640블록 · 인덱스 없음 · 금액 미확정(STLM_AMT NULL) 20건
M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
보정률은 등급 A 0.1%, B 0.2%, 그 밖 0.3%다. SQL Developer는 자동 커밋을 끄고 쓴다.

원본 SQL

sql

-- 10:24 · SQL Developer · 지난주 보정 누락분
INSERT /*+ APPEND */ INTO T_STLM_ADJ (STLM_SEQ, ADJ_RATE, ADJ_AMT, ADJ_DT)
SELECT s.STLM_SEQ, r.ADJ_RATE, ROUND(s.STLM_AMT * r.ADJ_RATE), SYSDATE
  FROM T_ORD_STLM s
  JOIN (SELECT CUS_ID,
               CASE CUS_GD WHEN 'A' THEN 0.001 WHEN 'B' THEN 0.002 ELSE 0.003 END AS ADJ_RATE
          FROM M_CUS) r
    ON r.CUS_ID = s.CUS_ID
 WHERE s.STLM_AMT IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM T_STLM_ADJ a WHERE a.STLM_SEQ = s.STLM_SEQ);

-- (건수와 금액을 이것저것 맞춰 본 뒤) 10:31
COMMIT;

실행계획 학습용 예시

xplan

[10:24:48 · SID 147 (SQL Developer) — 1,240 rows inserted]
-----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |               |      1 |        |      0 |00:00:00.06 |     981 |
|   1 |  LOAD AS SELECT         | T_STLM_ADJ    |      1 |        |      0 |00:00:00.06 |     981 |
|*  2 |   HASH JOIN             |               |      1 |   1240 |   1240 |00:00:00.03 |     906 |
|   3 |    TABLE ACCESS FULL    | M_CUS         |      1 |   2000 |   2000 |00:00:00.01 |      30 |
|*  4 |    HASH JOIN ANTI       |               |      1 |   1240 |   1240 |00:00:00.03 |     876 |
|*  5 |     TABLE ACCESS FULL   | T_ORD_STLM    |      1 |    100K|    100K|00:00:00.01 |     640 |
|   6 |     INDEX FAST FULL SCAN| PK_T_STLM_ADJ |      1 |  98780 |  98780 |00:00:00.01 |     236 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUS_ID"="S"."CUS_ID")
   4 - access("A"."STLM_SEQ"="S"."STLM_SEQ")
   5 - filter("S"."STLM_AMT" IS NOT NULL)

[10:31:00 · V$LOCK — T_STLM_ADJ 관련]
   SID  프로그램         TYPE  LMODE  REQUEST  CTIME(초)  BLOCK
   147  SQL Developer    TM      6        0       372       1
   147  SQL Developer    TX      6        0       372       0
   203  보정 화면        TM      0        3       351       0
   219  보정 화면        TM      0        3       298       0
   …    (보정 화면 세션 11개가 같은 모양)

[ASH · 10:24 ~ 10:31 · 보정 화면 세션]
   enq: TM - contention   96% · blocking_session = 147

문제

보정 화면 11개가 7분 동안 멈춘 원인을 V$LOCK의 숫자로 설명하시오.
같은 누락분을 넣는 방법을 다시 작성하고, 낮에 이 테이블에 넣을 때 무엇을 지켜야 하는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 넣기 전에 맞춰 본다 (건수 1,240 · 보정액 합계) — 확인을 커밋 앞으로 당긴다
SELECT COUNT(*), SUM(ROUND(s.STLM_AMT * CASE c.CUS_GD WHEN 'A' THEN 0.001 WHEN 'B' THEN 0.002 ELSE 0.003 END))
  FROM T_ORD_STLM s
  JOIN M_CUS c ON c.CUS_ID = s.CUS_ID
 WHERE s.STLM_AMT IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM T_STLM_ADJ a WHERE a.STLM_SEQ = s.STLM_SEQ);

-- ② 낮에 화면이 함께 쓰는 테이블 → 일반 INSERT (TM 3 · 다른 행을 고치는 세션을 막지 않는다)
INSERT INTO T_STLM_ADJ (STLM_SEQ, ADJ_RATE, ADJ_AMT, ADJ_DT)
SELECT s.STLM_SEQ, r.ADJ_RATE, ROUND(s.STLM_AMT * r.ADJ_RATE), SYSDATE
  FROM T_ORD_STLM s
  JOIN (SELECT CUS_ID,
               CASE CUS_GD WHEN 'A' THEN 0.001 WHEN 'B' THEN 0.002 ELSE 0.003 END AS ADJ_RATE
          FROM M_CUS) r
    ON r.CUS_ID = s.CUS_ID
 WHERE s.STLM_AMT IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM T_STLM_ADJ a WHERE a.STLM_SEQ = s.STLM_SEQ);

-- ③ 넣은 건수가 ①과 같으면 바로 커밋
COMMIT;

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

xplan

-----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |               |      1 |        |      0 |00:00:00.08 |    5212 |
|   1 |  LOAD TABLE CONVENTIONAL| T_STLM_ADJ    |      1 |        |      0 |00:00:00.08 |    5212 |
|*  2 |   HASH JOIN             |               |      1 |   1240 |   1240 |00:00:00.03 |     906 |
|   3 |    TABLE ACCESS FULL    | M_CUS         |      1 |   2000 |   2000 |00:00:00.01 |      30 |
|*  4 |    HASH JOIN ANTI       |               |      1 |   1240 |   1240 |00:00:00.03 |     876 |
|*  5 |     TABLE ACCESS FULL   | T_ORD_STLM    |      1 |    100K|    100K|00:00:00.01 |     640 |
|   6 |     INDEX FAST FULL SCAN| PK_T_STLM_ADJ |      1 |  98780 |  98780 |00:00:00.01 |     236 |
-----------------------------------------------------------------------------------------------------

[V$LOCK — INSERT 직후]
   SID  프로그램         TYPE  LMODE  REQUEST
   147  SQL Developer    TM      3        0
   203  보정 화면        TM      3        0      (기다리지 않는다)
[커밋까지] 0.1 초 안쪽 · 보정 화면 대기 0

모범 답안 서술

원인은 직접 경로 적재가 거는 테이블 잠금이다. /*+ APPEND */로 넣은 147번 세션은 T_STLM_ADJ에 TM 6(EXCLUSIVE)을 잡았고(LMODE 6, BLOCK 1), 보정 화면이 INSERT와 UPDATE를 하려고 요청한 TM 3(ROW EXCLUSIVE)은 TM 6과 함께 잡을 수 없다. 그래서 화면 세션 11개가 REQUEST 3으로 줄을 섰고 ASH의 96%가 enq: TM - contention이었다. 문장은 0.06초에 끝났지만 잠금은 문장이 아니라 트랜잭션이 쥔다. 넣은 뒤 건수와 금액을 맞춰 보느라 커밋이 7분 늦어졌고(CTIME 372초), 그 7분이 그대로 화면이 막힌 시간이다. 고친 방법은 APPEND를 빼고 일반 INSERT로 넣는 것이다. 일반 INSERT는 테이블에 TM 3만 잡으므로 다른 행을 고치는 화면 세션과 함께 돈다. 확인은 넣기 전에 같은 조건의 SELECT로 해 두고, 넣은 건수가 같으면 바로 커밋해 트랜잭션을 짧게 둔다. 1,240건 정도에서는 직접 경로의 이득이 거의 없다(일반 INSERT도 0.08초, Buffers 981 → 5,212로 조금 늘 뿐이다). 낮에 여러 세션이 쓰는 테이블에는 직접 경로 적재와 병렬 DML을 쓰지 않는다. 대량 적재는 그 테이블을 쓰는 세션이 없는 시간에 하고, 그 시간이 마감 안에 끝나는지 확인한다.

해설

APPEND의 대가는 교재 14화 §4.1이다. 야간 배치에서는 마감 전에 끝나는 한 아무도 모르지만, 낮에는 바로 드러난다. 같은 이유로 APPEND_VALUES 힌트나 병렬 DML도 낮의 공유 테이블에는 쓰지 않는다.
일반 INSERT로 바꿔도 화면이 같은 STLM_SEQ를 동시에 넣으면 그 한 행은 PK에서 TX 잠금을 기다린다. 누락분만 넣으므로 겹칠 일은 거의 없다.
직접 경로는 고수위선 위의 새 블록에만 쓴다. 낮에 소량 적재를 자주 하면 비어 있는 블록을 쓰지 못해 테이블이 조금씩 커진다(교재 14화 §4.2).
막힘은 V$LOCK의 LMODE/REQUEST/BLOCK과 V$SESSION.BLOCKING_SESSION으로 찾는다(교재 12화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황