미결제 주문 2,191건을 취소하는 데 1분 32초가 걸린다

기초UPDATE한빛유통432 / 700

상황

매달 1일 03:00에는 지난달 미결제 주문(PENDING)을 취소(CANCEL)로 바꾸는 정리 배치가 돈다. 7월 1일에 6월분 2,191건을 정리하는 데 1분 32초가 걸렸다. 배치 담당 오과장: "겨우 2천 건인데요. UPDATE 한 건에 0.04초면 빠른 편 아닌가요?"

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록. PK가 없고, ORD_SEQ는 값은 유일하지만 인덱스가 없다.
인덱스는 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) 하나다(정산 기간 조회 Q1용).
2024년 6월 주문 27,390건 중 PENDING은 2,191건(8%)이다. 통계는 최신이다.

원본 SQL

sql

-- 매월 1일 03:00 · 지난달 미결제 주문 정리 (오과장)
BEGIN
  FOR r IN (SELECT ORD_SEQ
              FROM T_ORD_BIG
             WHERE ORD_DT >= DATE '2024-06-01'
               AND ORD_DT <  DATE '2024-07-01'
               AND ORD_ST = 'PENDING') LOOP
    UPDATE T_ORD_BIG
       SET ORD_ST = 'CANCEL'
     WHERE ORD_SEQ = r.ORD_SEQ;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[세션 통계 — 7월 1일 03:00 실행]
  경과 시간                 1분 32초 (거의 전부 CPU)
  session logical reads     12,777,640
  recursive calls           2,236
  갱신한 행                  2,191

[V$SQL — 루프 안의 UPDATE]
  SQL_TEXT                                                     EXECUTIONS  ROWS_PROCESSED  BUFFER_GETS   ELAPSED
  UPDATE T_ORD_BIG SET ORD_ST = 'CANCEL' WHERE ORD_SEQ = :B1        2,191           2,191   12,771,339   91.4 초

[그 UPDATE 한 번의 실행계획 — DISPLAY_CURSOR (ALLSTATS LAST)]
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |           |      1 |        |      0 |00:00:00.04 |    5829 |
|   1 |  UPDATE            | T_ORD_BIG |      1 |        |      0 |00:00:00.04 |    5829 |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |      1 |      1 |00:00:00.04 |    5826 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORD_SEQ"=:B1)

문제

UPDATE 한 건은 0.04초인데 배치가 1분 32초 걸리는 이유를 실행계획과 V$SQL로 설명하시오.
같은 결과를 내도록 고치고, ORD_SEQ에 인덱스를 만들어 루프를 그대로 두는 안과 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 커서가 고르던 조건을 UPDATE 의 WHERE 로 — 테이블을 한 번만 읽고 2,191 행을 고친다
UPDATE T_ORD_BIG
   SET ORD_ST = 'CANCEL'
 WHERE ORD_DT >= DATE '2024-06-01'
   AND ORD_DT <  DATE '2024-07-01'
   AND ORD_ST = 'PENDING';
COMMIT;

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

xplan

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |           |      1 |        |      0 |00:00:00.09 |    8031 |
|   1 |  UPDATE            | T_ORD_BIG |      1 |        |      0 |00:00:00.09 |    8031 |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |   2191 |   2191 |00:00:00.04 |    5826 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ORD_ST"='PENDING' AND "ORD_DT">=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[세션 통계]  경과 0.09초 · session logical reads 약 8,100 · recursive calls 10 안팎 · 갱신 2,191행

모범 답안 서술

루프 안의 UPDATE가 미결제 주문 수만큼 2,191번 실행됐다(V$SQL EXECUTIONS 2,191). T_ORD_BIG에는 PK가 없고 ORD_SEQ에도 인덱스가 없어서, WHERE ORD_SEQ = :B1 한 건을 찾으려고 매번 100만 행을 FULL로 처음부터 끝까지 읽는다(실행계획 2번 줄 filter, 5,826블록). 한 번에 0.04초면 빠르지만 2,191번이면 2,191 × 5,829 = 12,771,339블록이고, 1분 32초가 거의 전부 여기서 나온다. 커서가 대상을 고르는 조건(6월, PENDING)을 그대로 UPDATE의 WHERE에 쓰면 한 문장이 된다. 테이블을 한 번만 FULL로 읽으면서 조건에 맞는 2,191행을 고친다. Buffers는 약 1,278만 → 8,031, 경과 시간은 1분 32초 → 0.09초로 준다. 커밋은 그대로 한 번이다. ORD_SEQ 인덱스를 만들어 루프를 살리는 안은 한 건을 찾는 비용만 줄인다(5,826 → 약 4블록). 2,191번 실행은 그대로이고, 100만 행 테이블에 인덱스를 하나 더 두면 재적재하거나 갱신할 때마다 유지 비용을 치러야 한다(교재 14화). 한 문장으로 바꾸면 새 인덱스가 필요 없다.

해설

함정: UPDATE 한 건의 시간(0.04초)만 보면 빠르다. 배치의 시간은 한 번의 비용에 실행 횟수를 곱한 값이므로 V$SQL의 EXECUTIONS를 먼저 본다.
커서가 이미 같은 테이블을 한 번 읽었다(5,826블록). 그 조건을 UPDATE로 옮기면 "읽고, 다시 찾아가서 고치는" 두 단계가 "읽으면서 고치는" 한 단계가 된다.
대상이 6월 한 달(전체의 2.7%)이라 IX_ORD_BIG_DT_AMT로 범위를 읽고 테이블에 가는 계획도 나올 수 있다. 어느 쪽이든 테이블을 한 번 지나가는 비용이다.
이 정리 배치가 다른 시간대에 돈다면 2,191행 잠금이 커밋까지 유지된다. 한 문장은 0.1초 안쪽이라 잠금 시간도 1분 32초 → 0.1초로 준다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황