정정 반영 버튼을 누를 때마다 주문 100만 건을 읽고, 반영되지 않은 정정이 가끔 '반영'으로 찍힌다

실전UPDATE한빛유통455 / 700

상황

정산팀은 고객 응대 중 주문 금액 정정이 확정되면 정정 요청(T_ORD_FIX)에 넣고 「정정 반영」 버튼을 누른다. 버튼은 아래 두 문장을 실행하고 커밋한다. 하루 스무 번쯤 누르고 한 번에 30 ~ 60건이다. DBA 주간 보고에서 이 UPDATE가 논리 읽기 Top SQL 3위였고(하루 약 12만 블록), 개발팀은 T_ORD_BIG에 ORD_SEQ 유일 인덱스를 만들자고 한다. 그런데 지난주 정산팀 조민우가 다른 문제를 가져왔다. "정정 3건이 '반영'으로 찍혀 있는데 주문 금액은 그대로예요. 그날 반영 버튼을 누르는 동안 다른 직원이 정정을 넣었대요." 오과장은 인덱스를 더 만들면 일요일 재적재가 다시 느려지지 않느냐고 묻는다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · PK와 유일 제약은 없지만 ORD_SEQ 값은 원천에서 유일하다 · ORD_DT는 DATE(전부 자정)
인덱스는 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT)와 IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ) 둘이다(높이 3). 일요일 밤 재적재는 TRUNCATE, 인덱스 UNUSABLE, APPEND 적재, REBUILD 순서다(교재 14화).
T_ORD_FIX(정정 요청, 이 문제를 위해 둔 테이블): PK (ORD_SEQ) · ORD_DT(정산 화면이 주문에서 그대로 옮겨 적는다) · NEW_AMT · APPLY_YN('N' 대기, 'Y' 반영) · RUN_ID(지금은 쓰지 않는 빈 칸) · 반영된 요청은 매일 밤 이력으로 옮기고 지운다(낮 동안 최대 800행, 5블록)
정정 요청은 화면에서 한 건씩 INSERT하고 바로 커밋한다. 시퀀스는 새로 만들어도 된다.

원본 SQL

sql

-- 「정정 반영」 버튼 (정산 화면)
UPDATE (SELECT o.ORD_AMT, f.NEW_AMT
          FROM T_ORD_BIG o, T_ORD_FIX f
         WHERE o.ORD_SEQ = f.ORD_SEQ
           AND f.APPLY_YN = 'N')
   SET ORD_AMT = NEW_AMT;

UPDATE T_ORD_FIX SET APPLY_YN = 'Y' WHERE APPLY_YN = 'N';
COMMIT;

실행계획 학습용 예시

xplan

[반영 한 번 — 대기 정정 45건]
-----------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |           |      1 |        |      0 |00:00:00.14 |    6101 |
|   1 |  UPDATE             | T_ORD_BIG |      1 |        |      0 |00:00:00.14 |    6101 |
|*  2 |   HASH JOIN         |           |      1 |     45 |     45 |00:00:00.13 |    5831 |
|*  3 |    TABLE ACCESS FULL| T_ORD_FIX |      1 |     45 |     45 |00:00:00.01 |       5 |
|   4 |    TABLE ACCESS FULL| T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.09 |    5826 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."ORD_SEQ"="F"."ORD_SEQ")
   3 - filter("F"."APPLY_YN"='N')

[하루] 반영 약 20번 · 이 UPDATE의 논리 읽기 약 122,000 (Top SQL 3위) · 고치는 행마다 IX_ORD_BIG_DT_AMT도 고친다

[지난주 화요일 14:20 — 반영 한 번 동안]
  14:20:03.1  ① 조인 뷰 UPDATE 시작 · 그 순간 커밋된 대기 정정 41건을 반영
  14:20:03.2  다른 직원이 정정 3건 INSERT · COMMIT
  14:20:03.3  ② APPLY_YN = 'Y' 44건 · COMMIT   → 3건은 금액이 그대로인데 '반영'

문제

이 UPDATE가 T_ORD_BIG을 매번 다 읽는 이유를 실행계획으로 설명하고, 조인 뷰 UPDATE가 허용되는 근거를 쓰시오. 정정 3건이 반영되지 않고 '반영'으로 찍힌 이유도 설명하시오.
(가) ORD_SEQ에 유일 인덱스를 새로 만든다, (나) 조인 조건에 ORD_DT를 더해 지금 있는 인덱스로 찾아가게 한다, (다) 정정을 모아 밤에 한 번 반영한다를 반영 한 번의 읽는 양, 재적재와 저장 공간에 주는 부담, 업무 요구로 비교하시오.
고른 방법으로 반영 처리를 다시 작성하시오. 다른 직원이 동시에 정정을 넣어도 실제로 반영한 정정만 '반영'으로 찍히게 하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 번만: 반영 회차 번호
CREATE SEQUENCE SQ_FIX_RUN;

-- 「정정 반영」 버튼 · (나) 조인 조건에 ORD_DT를 더해 IX_ORD_BIG_DT_SEQ로 찾아간다 (새 인덱스 없음)
DECLARE
  v_run   NUMBER := SQ_FIX_RUN.NEXTVAL;
  v_claim PLS_INTEGER;
  v_upd   PLS_INTEGER;
BEGIN
  -- ① 이 순간의 대기 정정을 이번 회차로 찍는다 (이 행들은 커밋까지 잠긴다)
  UPDATE T_ORD_FIX
     SET APPLY_YN = 'Y', RUN_ID = v_run
   WHERE APPLY_YN = 'N';
  v_claim := SQL%ROWCOUNT;

  -- ② 이번 회차로 찍은 정정만 반영한다 (T_ORD_FIX의 PK 덕분에 T_ORD_BIG이 키 보존 테이블)
  UPDATE (SELECT o.ORD_AMT, f.NEW_AMT
            FROM T_ORD_BIG o, T_ORD_FIX f
           WHERE o.ORD_DT  = f.ORD_DT          -- 지금 있는 (ORD_DT, ORD_SEQ) 인덱스의 앞 컬럼
             AND o.ORD_SEQ = f.ORD_SEQ
             AND f.RUN_ID  = v_run)
     SET ORD_AMT = NEW_AMT;
  v_upd := SQL%ROWCOUNT;

  -- ③ 찍은 건수와 반영한 건수가 다르면(주문일이 틀린 요청 등) 반쯤 반영하지 않는다
  IF v_upd <> v_claim THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20010, '정정 반영 건수 불일치: 요청 ' || v_claim || ' / 반영 ' || v_upd);
  END IF;
  COMMIT;
END;

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

xplan

[② 조인 뷰 UPDATE — 대기 정정 45건]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                   |      1 |        |      0 |00:00:00.01 |     455 |
|   1 |  UPDATE                      | T_ORD_BIG         |      1 |        |      0 |00:00:00.01 |     455 |
|   2 |   NESTED LOOPS               |                   |      1 |     45 |     45 |00:00:00.01 |     185 |
|*  3 |    TABLE ACCESS FULL         | T_ORD_FIX         |      1 |     45 |     45 |00:00:00.01 |       5 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T_ORD_BIG        |     45 |      1 |     45 |00:00:00.01 |     180 |
|*  5 |     INDEX RANGE SCAN         | IX_ORD_BIG_DT_SEQ |     45 |      1 |     45 |00:00:00.01 |     135 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("F"."RUN_ID"=:B1)
   5 - access("O"."ORD_DT"="F"."ORD_DT" AND "O"."ORD_SEQ"="F"."ORD_SEQ")

[세 방법 비교 (학습용 예시)]
  방법                                반영 한 번      하루         새 객체 · 재적재 부담                                  업무 요구
  지금 (ORD_SEQ만으로 조인)            6,101          약 122,000   —                                                     즉시 반영
  (가) ORD_SEQ 유일 인덱스 신설         약 450         약 9,000     인덱스 하나(리프 약 2,200블록) · 재적재 때 끌 수 없다   즉시 반영
  (나) 조인에 ORD_DT 추가(기존 인덱스)  455            약 9,100     없음 · 요청의 주문일이 맞아야 한다                       즉시 반영
  (다) 밤에 모아 한 번 반영             약 10,600      하루 한 번   없음                                                  정정이 다음 날 보인다

모범 답안 서술

이 UPDATE는 정정 45건을 찾으려고 T_ORD_BIG 100만 행을 다 읽는다(4번 줄 TABLE ACCESS FULL 5,826블록). 조인 조건이 ORD_SEQ 하나뿐인데 ORD_SEQ로 시작하는 인덱스가 없어서, 옵티마이저가 고를 수 있는 길은 정정을 해시 테이블에 올리고 주문 전체를 한 번 훑는 해시 조인뿐이다. 반영 한 번에 6,101블록, 하루 스무 번이면 약 12만 블록이다. 조인 뷰 UPDATE가 허용되는 것은 T_ORD_FIX의 PK가 ORD_SEQ라서다. 주문 한 행에 맞는 정정이 많아야 하나이므로 T_ORD_BIG이 키 보존 테이블이 된다. T_ORD_BIG에 PK가 없는 것은 상관없다. 고치는 쪽이 아니라 조인 상대 쪽의 유일성이 필요하다. 정정 3건이 빠진 것은 두 문장이 서로 다른 시점의 행 집합을 보기 때문이다. 첫 UPDATE는 시작 순간에 커밋돼 있던 대기 정정 41건을 반영했고, 그 사이 다른 직원이 3건을 넣고 커밋했다. 두 번째 UPDATE는 자기가 시작하는 순간의 'N' 44건을 모두 'Y'로 바꿨다. 읽기 일관성은 문장 단위라서, 같은 트랜잭션 안이라도 두 문장이 같은 행을 본다는 보장이 없다. 세 방법을 견주면 이렇다. (가) 유일 인덱스를 새로 만들면 NL 조인으로 정정마다 인덱스 3블록과 테이블 1블록만 읽어 반영 한 번이 약 450블록이 된다. 하지만 리프 약 2,200블록짜리 인덱스가 하나 늘고, 유일 인덱스는 재적재 때 UNUSABLE로 끌 수 없어서(끄면 적재가 ORA-01502로 막힌다) APPEND 적재 내내 행마다 유지된다. 14화에서 줄여 놓은 일요일 재적재가 다시 길어진다. (나) 정정 요청에는 주문일이 이미 적혀 있으므로, 조인 조건에 o.ORD_DT = f.ORD_DT를 더하면 지금 있는 IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ)의 두 칸이 모두 access 조건이 되어 정정마다 바로 찾아간다. 반영 한 번이 6,101 → 455블록, 하루 약 12만 → 9천 블록으로 줄고, 새 객체도 재적재 부담도 없다. 전제는 요청의 주문일이 맞는 것이다. 틀리면 그 정정은 조용히 반영되지 않으므로 건수를 대조해야 한다. (다) 밤에 모아 한 번 반영하면 읽는 양은 하루 한 번이지만, 정정이 다음 날에야 보여 정산팀의 요구(바로 반영)를 어긴다. 그래서 (나)를 고르고, 두 문장의 순서를 바꾼다. 먼저 대기 정정을 '이번 회차'(RUN_ID)로 찍으면서 'Y'로 바꾸고, 그 회차의 정정만 조인 뷰로 반영한다. 찍은 건수와 반영한 건수가 다르면(주문일이 틀린 요청 등) ROLLBACK하고 멈춘다. 둘 다 한 트랜잭션이므로, 그 사이 들어온 정정은 회차에 들지 않고 다음 반영 때 처리된다.

해설

(가)를 고른다면: 유일 인덱스 대신 일반 인덱스 (ORD_SEQ)로 만들면 재적재 때 끄고 다시 지을 수 있다. 그래도 REBUILD가 하나 늘고, 쓰는 곳이 반영 버튼 하나뿐인 인덱스가 리프 2,200블록을 차지한다. 이미 있는 인덱스로 같은 효과를 낼 수 있으면 새로 만들지 않는다(교재 04화, 14화의 인덱스 정리).
NL이 흔들리지 않게: T_ORD_FIX는 낮 동안 0 ~ 800행으로 크기가 계속 바뀐다. 어느 쪽 통계든 NL이 유리하지만, 계획이 흔들리면 LEADING(f) USE_NL(o) INDEX(o IX_ORD_BIG_DT_SEQ) 힌트나 통계 잠금으로 고정하고 그 이유를 적어 둔다.
두 사람이 동시에 누르면: 먼저 누른 쪽의 ①이 대기 정정 행을 잠그므로, 뒤에 누른 쪽의 ①은 그 행에서 기다렸다가 앞 사람이 커밋한 뒤 'N'인지 다시 확인하고 건너뛴다. 같은 정정이 두 번 반영되지 않는다.
주문일이 틀린 요청은 (나)의 전제를 깨므로 건수 대조로 막았다. 화면이 주문일을 손으로 입력받지 않고 주문에서 옮겨 적게 한 것이 곧 인덱스 설계의 일부다. 정정 요청에 무엇을 적어 두느냐가 반영 SQL의 접근 경로를 정한다.
같은 트랜잭션의 두 문장도 서로 다른 시점의 데이터를 본다. 두 문장이 같은 행 집합을 다뤄야 하면 먼저 행을 찍어 두고(회차, 잠금) 그 표시로 이어 간다(교재 13화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황