정산 10만 건에 고객 등급을 채우는 단계가 적재 단계보다 오래 걸린다

기초MERGE한빛유통436 / 700

상황

정산 화면이 등급별로 묶어 보려고 T_ORD_STLM에 고객 등급 칸(CUS_GD)을 더했고, 야간 체인에 그 칸을 채우는 단계가 하나 생겼다. 체인 시각을 찍어 보니 이 단계가 1.3초로, 정산 10만 건을 넣는 적재 단계(0.1초)보다 오래 걸린다. 배치 담당 오과장: "한 건씩 ROWID로 바로 찾아가니까 제일 빠른 방법 아닌가요?"

테이블과 인덱스

T_ORD_STLM: 100,000행 · 640블록. 이 문제에서는 CUS_GD VARCHAR2(1) 칸이 더 있다. 체인 첫 단계(P1)가 매일 밤 비우고 다시 채우므로, 이 단계가 돌 때 CUS_GD는 전부 NULL이다. 인덱스는 없다.
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · 정산의 CUS_ID는 모두 M_CUS에 있다.
P1은 INSERT /*+ APPEND */ … SELECT ROWNUM, … FROM T_ORD 한 문장이다(교재 14화 rev02).

원본 SQL

sql

-- 야간 체인 ② 정산 등급 채우기
BEGIN
  FOR r IN (SELECT s.ROWID AS rid, c.CUS_GD
              FROM T_ORD_STLM s, M_CUS c
             WHERE c.CUS_ID = s.CUS_ID) LOOP
    UPDATE T_ORD_STLM
       SET CUS_GD = r.CUS_GD
     WHERE ROWID = r.rid;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[세션 통계 — ② 한 번]
  경과 시간                 1.28 ~ 1.36 초
  CPU used by this session  1.15 초
  recursive calls           101,046
  execute count             100,021
  session logical reads     약 203,600
  redo size                 약 24.8 MB
  undo change vector size   약 7.9 MB

[TKPROF 모양 — ② 의 두 문장]
SELECT S.ROWID AS RID, C.CUS_GD FROM T_ORD_STLM S, M_CUS C WHERE C.CUS_ID = S.CUS_ID
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Fetch     1001      0.06       0.07          0       1670          0      100000     (HASH JOIN · M_CUS 30 + T_ORD_STLM 640)

UPDATE T_ORD_STLM SET CUS_GD = :B2 WHERE ROWID = :B1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute 100000      0.93       1.02          0     100000     102861      100000     (TABLE ACCESS BY USER ROWID)

문제

ROWID로 바로 찾아가는데도 이 단계가 1.3초 걸리는 이유를 위 통계로 설명하시오.
같은 결과를 내도록 고치시오. 이 단계 자체를 없앨 수 있는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ② 한 문장 — 고객 2,000명을 해시 조인의 빌드로 올리고 정산 10만 건을 한 번 훑어 채운다
MERGE INTO T_ORD_STLM s
USING M_CUS c
   ON (s.CUS_ID = c.CUS_ID)
 WHEN MATCHED THEN UPDATE SET s.CUS_GD = c.CUS_GD;
COMMIT;

-- 더 낫다: ② 를 없애고 P1 이 적재할 때 등급을 같이 넣는다 (정산 번호를 매기는 순서가 그대로인지 확인한 뒤)
-- INSERT /*+ APPEND */ INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT, CUS_GD)
-- SELECT ROWNUM, o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.ORD_AMT, c.CUS_GD
--   FROM T_ORD o LEFT OUTER JOIN M_CUS c ON c.CUS_ID = o.CUS_ID;

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

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT       |            |      1 |        |      0 |00:00:00.33 |    103K |          |
|   1 |  MERGE                | T_ORD_STLM |      1 |        |      0 |00:00:00.33 |    103K |          |
|   2 |   VIEW                |            |      1 |        |    100K|00:00:00.05 |     670 |          |
|*  3 |    HASH JOIN          |            |      1 |    100K|    100K|00:00:00.04 |     670 | 1582K (0)|
|   4 |     TABLE ACCESS FULL | M_CUS      |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|   5 |     TABLE ACCESS FULL | T_ORD_STLM |      1 |    100K|    100K|00:00:00.02 |     640 |          |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S"."CUS_ID"="C"."CUS_ID")

[세션 통계]  경과 0.33초 · CPU 0.29초 · recursive calls 12 · session logical reads 약 103,500 · redo 약 21.6 MB
[② 를 P1 에 합치면]  ② 단계 0초 · P1 은 0.1초 그대로(조인 몫 +30블록) · 고치는 행 0

모범 답안 서술

루프 안의 UPDATE가 정산 행 수만큼 100,000번 실행됐다(TKPROF Execute 100,000, recursive calls 101,046). ROWID로 찾아가니 한 번은 1블록 읽고 1블록 고치는 가장 싼 UPDATE지만, 실행할 때마다 PL/SQL에서 SQL로 건너가 문장을 실행하고 행 하나의 redo와 undo를 따로 남긴다. 한 번의 비용이 작아도 10만 번이면 CPU 1.15초가 된다. 커서는 이미 정산과 고객을 해시 조인해 등급까지 가져왔으므로, 그 조인을 UPDATE 쪽으로 옮기면 된다. MERGE 한 문장으로 바꾸면 고객 2,000명(30블록)을 해시 조인의 빌드로 올리고 정산 10만 건을 한 번 훑으면서 등급을 채운다. 경과 시간은 1.3초 → 0.33초, recursive calls는 101,046 → 12로 준다. 다만 10만 행을 고치는 일 자체(current 읽기 약 10만, redo 약 21.6MB)는 남는다. 더 나은 답은 이 단계를 없애는 것이다. P1이 정산을 적재할 때 M_CUS를 조인해 등급을 처음부터 같이 넣으면, 10만 행을 넣고 또 고치는 일이 사라진다. 직접 경로 적재라 redo도 거의 생기지 않는다. 이때는 조인 때문에 ROWNUM으로 매기는 정산 번호의 순서가 바뀌지 않는지 확인한다.

해설

함정: "ROWID 접근이 가장 빠르다"는 한 건을 찾는 방법으로는 맞다. 하지만 배치의 비용은 한 건을 찾는 비용이 아니라 문장을 실행하는 횟수가 정한다. 같은 10만 행을 고쳐도 문장 하나와 문장 10만 개는 CPU가 네 배쯤 차이 난다.
UPDATE (SELECT s.CUS_GD AS old_gd, c.CUS_GD AS new_gd FROM T_ORD_STLM s, M_CUS c WHERE c.CUS_ID = s.CUS_ID) SET old_gd = new_gd도 답이다. PK_M_CUS가 유일하므로 T_ORD_STLM이 키 보존 테이블이 된다.
SET CUS_GD = (SELECT CUS_GD FROM M_CUS WHERE CUS_ID = s.CUS_ID) 모양도 한 문장이다. 고객이 2,000명이라 서브쿼리 캐시가 듣지만, 정산에 짝 없는 고객이 생기면 그 행은 NULL로 덮인다는 점을 알고 쓴다.
적재한 뒤 같은 행을 다시 고치는 단계가 체인에 있으면, 그 단계를 적재 문장에 합칠 수 있는지부터 본다. 넣고 또 고치면 redo와 블록 변경을 두 번 치른다(교재 14화, DML의 부수비용).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황