1분마다 도는 대기 주문 정리가 해마다 무거워진다

중급UPDATE한빛유통88 / 700

상황

현금 결제 주문은 입금을 기다리는 동안 상품 재고를 잡아 둔다. 30분 안에 입금되지 않으면 1분마다 도는 정리 작업이 그 주문을 취소해 재고를 푼다. 한 번에 바뀌는 주문은 보통 0 ~ 2건이다. 오픈할 때는 몇 블록이면 끝났는데, 2년이 지난 지금은 한 번에 400블록 넘게 읽는다. 오과장은 이렇게 묻는다. "바뀌는 건 한두 건인데 왜 해마다 무거워지나?"

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · ORD_DT DATE(시분초 있음, 2023 ~ 2024, 하루 약 137건) · PAY_TP CARD · CASH · POINT가 1/3씩 · ORD_ST COMPLETE · PENDING · CANCEL
운영 상태: 기한이 지난 현금 대기 주문은 이 작업이 그때그때 취소해 왔다. 그래서 기한이 지났는데 PENDING으로 남은 현금 주문은 실행할 때마다 0 ~ 2건뿐이다
인덱스 PK_T_ORD (ORD_SEQ) · IX_T_ORD_DT_ST_TP (ORD_DT, ORD_ST, PAY_TP): 「일자별 상태·결제수단 현황」용 · BLEVEL 1 · 리프 약 417블록(리프 한 블록에 약 240항목) · 통계 최신 · ORD_ST에 히스토그램

원본 SQL

sql

UPDATE T_ORD
   SET ORD_ST = 'CANCEL'
 WHERE ORD_ST = 'PENDING'
   AND PAY_TP = 'CASH'
   AND ORD_DT < SYSDATE - 30/1440

실행계획 학습용 예시 · 행 수는 적재 스크립트 그대로 · 운영 상태는 문제에 적은 대로

xplan

[한 번 실행 — 기한이 지난 현금 대기 주문 1건]
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                   |      1 |        |      0 |00:00:00.01 |     425 |
|   1 |  UPDATE            | T_ORD             |      1 |        |      0 |00:00:00.01 |     425 |
|*  2 |   INDEX RANGE SCAN | IX_T_ORD_DT_ST_TP |      1 |      2 |      1 |00:00:00.01 |     418 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ORD_DT"<SYSDATE@!-.0208333333333333333333333333333333333333 AND "ORD_ST"='PENDING' AND
              "PAY_TP"='CASH')
       filter(("ORD_ST"='PENDING' AND "PAY_TP"='CASH'))

문제

바뀌는 주문은 1건인데 2번 줄이 418블록을 읽는 이유를 access와 filter로 설명하시오. 이 작업이 왜 해마다 무거워지는지도 설명하시오.
인덱스를 설계해 DDL을 작성하고, 이 작업이 읽는 양이 한 번에, 하루에 어떻게 바뀌는지 숫자로 쓰시오.
그 인덱스가 UPDATE와 INSERT에 주는 부담을 설명하시오. 조건을 ORD_DT >= SYSDATE - 1처럼 좁혀 쓰는 방법의 문제도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 늘 = 로 들어오는 ORD_ST · PAY_TP 를 앞에, 범위인 ORD_DT 를 뒤에 — '현금 대기 중 기한이 지난 것' 이 한 구간
CREATE INDEX IX_T_ORD_ST_TP_DT ON T_ORD (ORD_ST, PAY_TP, ORD_DT) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 재고 → T2 → VISIBLE

UPDATE T_ORD                   -- 문장은 그대로 (업무 조건을 좁히지 않는다)
   SET ORD_ST = 'CANCEL'
 WHERE ORD_ST = 'PENDING'
   AND PAY_TP = 'CASH'
   AND ORD_DT < SYSDATE - 30/1440;

개선 후 실행계획 학습용 예시 · 행 수는 적재 스크립트 그대로 · 운영 상태는 문제에 적은 대로

xplan

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                   |      1 |        |      0 |00:00:00.01 |       9 |
|   1 |  UPDATE            | T_ORD             |      1 |        |      0 |00:00:00.01 |       9 |
|*  2 |   INDEX RANGE SCAN | IX_T_ORD_ST_TP_DT |      1 |      2 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------
   2 - access("ORD_ST"='PENDING' AND "PAY_TP"='CASH' AND "ORD_DT"<SYSDATE@!-.0208333333333333333333333333333333333333)

모범 답안 서술

2번 줄의 access에는 ORD_ST와 PAY_TP도 적혀 있지만 filter에 또 나온다. 인덱스의 선두 ORD_DT가 < SYSDATE - 30분이라는 열린 범위로 들어왔기 때문이다. 이 범위는 '30분 전보다 오래된 주문 전부'라서 시작점이 인덱스 맨 앞이고, 뒤의 ORD_ST와 PAY_TP는 구간을 줄이지 못하고 filter로 거른다. 그래서 2년 치 약 10만 항목, 리프 약 417블록을 매번 다 읽고 1건을 찾는다(Buffers 418). 해마다 무거워지는 이유도 같다. 주문이 쌓일수록 '30분 전보다 오래된 주문'이 늘어나므로 access 범위가 테이블 크기만큼 자란다. 오픈 무렵에는 주문이 적어서 몇 블록이면 끝났다. 늘 =로 들어오는 ORD_ST와 PAY_TP를 앞에, 범위 조건 ORD_DT를 뒤에 둔 (ORD_ST, PAY_TP, ORD_DT)를 만든다. 'PENDING이면서 CASH이고 기한이 지난 주문'이 인덱스 안에서 한 구간이 되고, 그 구간에는 지금 바꿀 주문만 있다. 이미 취소된 주문은 CANCEL 구간으로 옮겨 갔기 때문이다. 2번 줄은 418 → 2블록, 문장 전체는 425 → 9블록이다. 1분마다 도므로 하루 1,440번에 약 61만 → 1만 3천 블록으로 준다. 주문이 더 쌓여도 이 숫자는 늘지 않는다. 대가도 있다. ORD_ST를 바꾸면 ORD_ST를 담은 인덱스마다 항목을 지우고 새 자리(CANCEL 구간)에 다시 넣는다. 기존 인덱스와 새 인덱스가 모두 ORD_ST를 담고 있으므로 UPDATE 한 건에 인덱스 둘을 고친다(UPDATE 몫 7 → 9블록). 주문을 INSERT할 때도 인덱스 하나를 더 고친다. 하루 137건 남짓이라 작은 비용이다. 조건을 ORD_DT >= SYSDATE - 1처럼 좁혀 쓰면 기존 인덱스로도 하루치만 읽는다. 하지만 뜻이 바뀐다. 작업이 하루 넘게 멈췄다가 다시 돌면 그사이 기한이 지난 대기 주문을 놓쳐 재고가 풀리지 않는다. 성능을 위해 업무 조건을 바꾸지 않는다.

해설

<나 >만 있는 열린 범위가 선두에 오면 범위가 데이터와 함께 자란다. 오늘 빠른 SQL이 1년 뒤에 느려지는 흔한 모양이다. 오픈 직후의 데이터로만 성능을 재면 드러나지 않는다.
새 인덱스가 작게 유지되는 데는 이유가 있다. 'PENDING이면서 CASH'인 구간에는 아직 처리되지 않은 주문만 남고, 처리된 주문은 CANCEL이나 COMPLETE 구간으로 옮겨 간다. 상태 컬럼을 선두에 둔 인덱스가 '처리 대기 큐'처럼 쓰이는 경우다.
기존 인덱스를 (ORD_ST, PAY_TP, ORD_DT)로 바꿔 하나만 두는 방법도 있다. 다만 「일자별 상태·결제수단 현황」(ORD_DT 범위만 주는 SQL)이 선두 조건을 잃는다. 그 SQL이 하루 몇 번뿐이라면 T_ORD 전체 스캔(896블록)으로 충분한지 재 보고 정한다.
INDEX RANGE SCAN의 Buffers는 결과 건수가 아니라 access 범위가 정한다. A-Rows 1과 Buffers 418이 함께 보이면 범위 조건 뒤에 = 조건이 있는지부터 본다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수는 적재 스크립트 그대로 · 운영 상태는 문제에 적은 대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수는 적재 스크립트 그대로 · 운영 상태는 문제에 적은 대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황