오후 3시에 돌린 2022년 주문 정리 3분 동안 반품 접수는 멈추고 대사 리포트는 서른 배 느려졌다

실전UPDATE한빛유통545 / 700

상황

한빛유통 고객센터의 요청으로 2022년 완료 주문을 「보관」 상태로 바꿔 화면의 기본 목록에서 빼기로 했다. 개발팀은 목요일 15:00에 UPDATE 한 문장으로 돌렸고 2분 52초 만에 끝났다. 그 3분 동안 고객센터의 「반품 접수」 세 건이 길게는 2분 50초 멈췄고, 정산팀의 「연간 대사 리포트」가 평소 0.4초에서 12초로 늘었다. 신규 주문은 멀쩡했다. 다음 달에는 2023년 상반기 주문을 정리할 예정이라 오과장이 방법을 비교해 달라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 이 문제에서는 운영 주문 테이블 역할을 한다 · ORD_DT 2022-01-01 ~ 2024-12-31(날짜순 적재) · 2022년 주문은 333,000행 남짓이고 그중 COMPLETE가 90%
인덱스: UX_ORD_BIG_SEQ (ORD_SEQ) UNIQUE · IX_ORD_BIG_CUS (CUS_ID, ORD_DT) · ORD_DT와 ORD_ST에는 인덱스가 없다
고객센터 「반품 접수」는 ORD_SEQ로 주문 한 건의 ORD_ST를 RETURN으로 바꾼다(하루 수백 건, 오래된 주문도 온다)
「연간 대사 리포트」는 T_ORD_BIG을 통째로 읽어 연도와 상태별로 센다(시간당 수십 번)
T_JOB_LOG 작업 기록(문제 설정): 작업 이름, 조각, 바꾼 행 수, 끝난 시각 · 언두 테이블스페이스는 넉넉하다

원본 SQL

sql

-- 목요일 15:00 · 한 문장 · 끝에 COMMIT
UPDATE T_ORD_BIG
   SET ORD_ST = 'ARCHIVE'
 WHERE ORD_DT < DATE '2023-01-01'
   AND ORD_ST = 'COMPLETE';
COMMIT;

실행계획 학습용 예시 · CR 블록이 만들어지는 과정은 교재 13화 §4.5

xplan

[UPDATE — ALLSTATS LAST · 15:00:04 ~ 15:02:56 (SID 201)]
----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |           |      1 |        |      0 |00:02:52.10 |    312K |
|   1 |  UPDATE            | T_ORD_BIG |      1 |        |      0 |00:02:52.10 |    312K |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |    300K|    299K|00:00:01.30 |    5826 |
----------------------------------------------------------------------------------------
   2 - filter(("ORD_DT"<TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ORD_ST"='COMPLETE'))
  세션 통계: redo size 81 MB · undo change vector size 74 MB · 고친 행 299,412

[반품 접수 — 15:01:20 의 V$SESSION]
SID   EVENT                            SECS   BLOCKING_SESSION   하는 문장
118   enq: TX - row lock contention      74   201                UPDATE T_ORD_BIG SET ORD_ST = 'RETURN' WHERE ORD_SEQ = :B1   (2022-01 주문)
133   enq: TX - row lock contention      31   201                (2022-04 주문)
(15:02:10 에 온 2022-06 주문의 반품은 46초 기다림 · 같은 3분 동안 2024년 주문의 반품 6건은 0.01초)

[연간 대사 리포트 — 평소 (ALLSTATS LAST)]
----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      9 |00:00:00.41 |    5826 |
|   1 |  HASH GROUP BY     |           |      1 |      9 |      9 |00:00:00.41 |    5826 |
|   2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.18 |    5826 |
----------------------------------------------------------------------------------------
[같은 리포트 — 15:02:30 에 시작한 한 번]
|   0 | SELECT STATEMENT   |           |      1 |        |      9 |00:00:12.07 |    260K |
|   1 |  HASH GROUP BY     |           |      1 |      9 |      9 |00:00:12.07 |    260K |
|   2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |   1000K|   1000K|00:00:11.85 |    260K |
  세션 통계: consistent gets 260,140 · data blocks consistent reads - undo records applied 252,600 · 기다린 enqueue 0

문제

같은 3분 동안 반품 접수와 연간 대사 리포트, 신규 주문이 각각 무엇을 겪었는지 실행계획과 V$SESSION, 세션 통계를 근거로 구분해 설명하시오.
다음 달 2023년 상반기 정리에 쓸 방법을 셋 이상 비교하고 하나를 골라 SQL로 쓰시오. 고른 방법의 부작용과 그 대비도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 2023년 상반기 정리 — 한 달씩 나눠 짧게 COMMIT 한다 (한 조각 약 2만 5천 행 · 15초 안쪽)
DECLARE
  v_cnt PLS_INTEGER;
BEGIN
  FOR m IN 0 .. 5 LOOP
    UPDATE T_ORD_BIG
       SET ORD_ST = 'ARCHIVE'
     WHERE ORD_DT >= ADD_MONTHS(DATE '2023-01-01', m)
       AND ORD_DT <  ADD_MONTHS(DATE '2023-01-01', m + 1)
       AND ORD_ST = 'COMPLETE';                  -- 다시 돌려도 이미 바꾼 행은 건너뛴다
    v_cnt := SQL%ROWCOUNT;

    INSERT INTO T_JOB_LOG (JOB_NM, PIECE, ROWS_DONE, DONE_AT)
    VALUES ('ARCHIVE_2023H1', TO_CHAR(ADD_MONTHS(DATE '2023-01-01', m), 'YYYYMM'), v_cnt, SYSDATE);
    COMMIT;                                      -- 조각마다 행 락을 놓는다

    DBMS_SESSION.SLEEP(5);                       -- 다음 조각 전에 쉬어 기다리던 반품 접수가 지나가게
  END LOOP;
END;

개선 후 실행계획 학습용 예시 · CR 블록이 만들어지는 과정은 교재 13화 §4.5

xplan

[2023년 상반기 · 같은 목요일 15:00 에 돌린다고 할 때 (학습용 예시)]
조각       고친 행    걸린 시간   그동안 그 달 주문의 반품 접수
2023-01    24,610     14.1초     최대 14초 기다릴 수 있다 (그 달 주문만)
2023-02    22,240     12.8초
…
2023-06    24,690     14.2초
  합계 146,900행 · 여섯 조각 · 조각 사이 5초 · 전체 약 1분 50초 · redo 약 40MB (한 문장일 때와 비슷)
  연간 대사 리포트 (조각 하나가 도는 중에)   Buffers 5,826 → 약 31K · 1.4초 안팎

모범 답안 서술

셋이 겪은 일은 서로 다르다. 반품 접수(118, 133)는 행 락을 기다렸다. UPDATE 한 문장은 고친 2022년 완료 주문 299,412행의 행 락을 COMMIT하는 15:02:56까지 쥔다. 118과 133은 그 문장이 이미 고친 2022년 주문을 RETURN으로 바꾸려다 enq: TX - row lock contention으로 기다렸다(blocking_session 201, TX 6). 118은 15:00:06부터 2분 50초를 기다렸다. 같은 시각 2024년 주문의 반품 6건은 잠긴 행이 아니어서 0.01초에 끝났다. 신규 주문도 새 행을 INSERT할 뿐이라 잠긴 행을 건드리지 않았고, TM은 서로 3이라 막히지 않았다. 연간 대사 리포트는 락을 기다리지 않았다(기다린 enqueue 0). 대신 읽는 양이 늘었다. 리포트는 시작한 시점의 데이터를 봐야 하므로, UPDATE가 고쳤지만 아직 COMMIT하지 않은 블록을 만날 때마다 언두를 적용해 그 블록을 과거 모습으로 되돌린 사본(CR 블록)을 만들어 읽는다. 15:02:30에는 이미 약 25만 행이 고쳐져 있어서 undo records applied가 252,600이었고, Buffers가 5,826 → 260K, 시간이 0.41 → 12.07초가 되었다. 기다린 것이 아니라 일을 더 한 것이다. 방법은 셋을 비교한다. ⓐ 한 문장을 새벽에 돌린다. 반품 접수와 리포트가 거의 없는 시간이라 피해가 없고 SQL도 그대로다. 대신 2023년 상반기는 14만 7천 행이라 그 시간에도 1분 남짓 행 락을 쥐고, 새벽 작업 창을 따로 얻어야 한다. ⓑ 한 달씩 나눠 조각마다 COMMIT한다. 조각 하나는 2만 5천 행 안팎, 15초 안쪽이라 행 락을 쥐는 시간이 짧고, 리포트가 되돌릴 블록도 그 조각만큼으로 준다(260K → 약 31K). 조각 사이에 5초씩 쉬면 기다리던 반품 접수가 그 틈에 지나간다. ⓒ 상태를 바꾸지 않는다. 화면의 기본 목록을 날짜 조건(2023년 이후 주문)으로 거르면 UPDATE 자체가 없다. 행 락도 redo도 0이지만 화면 SQL을 고쳐 배포해야 하고, 보관을 상태로 쓰는 다른 프로그램이 있으면 맞지 않는다. 다음 달은 업무 시간에 해야 하므로 ⓑ를 고른다. 대가는 반쯤 반영이다. 중간에 멈추면 1 ~ 3월만 보관이고 4월부터는 완료인 상태가 남는다. 그래서 조건에 ORD_ST = 'COMPLETE'를 두어 다시 돌리면 이미 바꾼 행은 건너뛰게 하고, 조각마다 작업 기록(T_JOB_LOG)에 몇 행을 바꿨는지 남겨 어디까지 했는지 알게 한다. 리포트가 정리 중간에 돌면 일부 달만 보관인 숫자를 볼 수 있다는 점도 정산팀에 미리 알린다. 전체 redo와 undo는 한 문장일 때와 비슷하다(약 40MB). 나눠서 줄어드는 것은 한 번에 쥐는 행과 시간이지 일의 총량이 아니다.

해설

한 문장이 좋다는 말은 일량의 이야기다. 한 문장은 왕복과 커밋이 가장 적고 원자적이다(교재 13화 §7). 하지만 업무 시간에는 한 번에 쥐는 행과 시간이 다른 세션의 비용이 된다. 무엇을 줄일지는 언제 돌리느냐가 정한다.
월 조각마다 ORD_DT에 인덱스가 없어 T_ORD_BIG을 통째로 읽는다(조각마다 5,826블록, 1.3초). 여섯 번이면 견딜 만하지만 조각이 수백 개라면 DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID로 ROWID 범위를 나눠 블록을 한 번씩만 읽게 한다.
루프 안에서 COMMIT하지만 fetch across commit은 아니다. 열어 둔 커서를 읽으며 COMMIT하는 것이 아니라, 조각마다 새 문장이 새 시점으로 읽기 때문이다(교재 13화 §5).
리포트의 260K는 V$SESSION에도 V$LOCK에도 나타나지 않는다. 느린데 아무것도 기다리지 않는다면 세션 통계의 undo records applied와 consistent gets를 본다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · CR 블록이 만들어지는 과정은 교재 13화 §4.5 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · CR 블록이 만들어지는 과정은 교재 13화 §4.5 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황