이관 리허설에서 주문상세 단가 채우기만 3초, 다른 여섯 단계는 1초 안쪽이다

기초UPDATE한빛유통656 / 700

상황

구 시스템에서 신 시스템으로 넘어가는 이관 리허설이 토요일에 있었다. 일곱 단계 중 「주문상세 단가 채우기」가 2.9초로 가장 길었다. 본 이관 날에는 단계마다 허용 시간이 정해져 있어서, 오과장이 이 단계를 먼저 봐 달라고 했다. 이관팀은 단가 규칙을 이관 공통 함수 FN_ITM_PRC에 넣어 두고 UPDATE 한 문장으로 채운다. 이관팀의 설명은 "한 문장이니 집합 처리다"라는 것이다.

테이블과 인덱스

T_ORD_DET 주문상세: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 이관 직후 UNT_PRC는 전부 NULL · 상품 100종
M_ITM 상품: 100행 · 5블록 · PK_M_ITM (ITM_ID) · UNT_PRC
주문상세의 상품은 모두 M_ITM에 있다(이관 전 대조 완료) · 리허설 DB는 NOARCHIVELOG · 통계 최신

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_ITM_PRC(p_itm_id M_ITM.ITM_ID%TYPE) RETURN NUMBER IS
  v_prc M_ITM.UNT_PRC%TYPE;
BEGIN
  SELECT UNT_PRC INTO v_prc FROM M_ITM WHERE ITM_ID = p_itm_id;
  RETURN v_prc;
END;
/

UPDATE T_ORD_DET d
   SET d.UNT_PRC = FN_ITM_PRC(d.ITM_ID);

COMMIT;

실행계획 학습용 예시

xplan

[DISPLAY_CURSOR (ALLSTATS LAST)]
-----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |           |      1 |        |      0 |00:00:02.91 |    206K |
|   1 |  UPDATE            | T_ORD_DET |      1 |        |      0 |00:00:02.91 |    206K |
|   2 |   TABLE ACCESS FULL| T_ORD_DET |      1 |   200K |   200K |00:00:00.04 |     896 |
-----------------------------------------------------------------------------------------

[세션 통계: UPDATE 한 번]
  경과 시간                 2.91 초 (CPU 2.62 초)
  recursive calls           200,014
  execute count             200,005
  consistent gets           401,216
  db block gets             204,977
  redo size                 47.6 MB

[V$SQL: 함수 안의 문장 (dep=1)]
  SELECT UNT_PRC FROM M_ITM WHERE ITM_ID = :B1    EXECUTIONS 200,000 · BUFFER_GETS 400,000

문제

UPDATE 한 문장인데 2.9초가 걸리는 이유를 세션 통계로 설명하시오.
결과가 바뀌지 않게 다시 작성하고, 고친 뒤에도 줄지 않는 일량은 무엇인지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 함수 대신 상품을 한 번 읽어 주문상세에 대어 본다 (해시 조인 · 함수 호출 0)
MERGE INTO T_ORD_DET d
USING M_ITM i
   ON (d.ITM_ID = i.ITM_ID)
 WHEN MATCHED THEN UPDATE
      SET d.UNT_PRC = i.UNT_PRC;

COMMIT;

-- (다른 정답) 수정 가능 조인 뷰: M_ITM 이 PK 로 조인되므로 T_ORD_DET 는 키 보존 테이블이다
-- UPDATE (SELECT d.UNT_PRC AS old_prc, i.UNT_PRC AS new_prc
--           FROM T_ORD_DET d, M_ITM i
--          WHERE i.ITM_ID = d.ITM_ID)
--    SET old_prc = new_prc;

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

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |           |      1 |        |      0 |00:00:00.74 |    206K |          |
|   1 |  MERGE               | T_ORD_DET |      1 |        |      0 |00:00:00.74 |    206K |          |
|   2 |   VIEW               |           |      1 |        |   200K |00:00:00.08 |     901 |          |
|*  3 |    HASH JOIN         |           |      1 |   200K |   200K |00:00:00.06 |     901 | 1180K (0)|
|   4 |     TABLE ACCESS FULL| M_ITM     |      1 |    100 |    100 |00:00:00.01 |       5 |          |
|   5 |     TABLE ACCESS FULL| T_ORD_DET |      1 |   200K |   200K |00:00:00.02 |     896 |          |
-------------------------------------------------------------------------------------------------------
   3 - access("D"."ITM_ID"="I"."ITM_ID")

[세션 통계]  경과 0.74 초 (CPU 0.66 초) · recursive calls 27 · consistent gets 1,233 · db block gets 205,102 · redo size 47.9 MB

모범 답안 서술

UPDATE는 한 문장이지만 SET 절의 FN_ITM_PRC(d.ITM_ID)는 고치는 행마다 호출된다. 주문상세 200,000행이니 함수가 20만 번 호출됐고, 그때마다 함수 안의 SELECT UNT_PRC FROM M_ITM …가 재귀 SQL로 실행됐다. recursive calls 200,014와 V$SQL의 EXECUTIONS 200,000이 그 흔적이다. 호출당 2블록(PK 인덱스 1, 테이블 1)이라 함수 안 SQL만 400,000블록을 읽었고, consistent gets 401,216의 거의 전부가 여기서 나왔다. 문장 안에 루프가 숨어 있었던 것이다. 상품은 100종, 5블록뿐이다. MERGE로 상품을 한 번 읽어 해시 조인으로 주문상세에 대어 보면 함수는 한 번도 호출되지 않는다. consistent gets는 401,216 → 1,233, recursive calls는 200,014 → 27, 경과 시간은 2.91 → 0.74초가 된다. 남는 일은 행을 고치는 일 자체다. 20만 행의 블록을 current 모드로 읽어 고치고(db block gets 약 20만), redo를 47MB 남기는 것은 두 판이 같다. 이 단계를 더 줄이려면 갱신 대신 적재 단계에서 단가를 붙여 넣는 방법을 따로 검토한다. 결과도 확인한다. 함수는 상품을 못 찾으면 NO_DATA_FOUND가 SQL 안에서 NULL이 되어 그 행에 NULL을 넣고, MERGE는 짝이 없는 행을 건드리지 않는다. 이관 전 대조로 주문상세의 상품이 모두 M_ITM에 있음을 확인했고 단가가 원래 NULL이었으므로 두 판의 결과는 같다.

해설

"한 문장이면 집합 처리"라는 말은 반만 맞다. SQL 안에서 부른 함수가 SQL을 실행하면, 그 문장은 행마다 PL/SQL로 건너갔다가 다시 SQL로 건너간다(교재 15화의 한 문장의 함정). recursive calls가 행 수만큼 나오면 숨은 루프를 의심한다.
상관 서브쿼리 UPDATE(SET d.UNT_PRC = (SELECT i.UNT_PRC FROM M_ITM i WHERE i.ITM_ID = d.ITM_ID))도 답이다. 스칼라 서브쿼리 캐시 덕에 상품 100종 대부분은 한 번만 찾지만, 칸에 들지 못한 상품은 나올 때마다 찾는다. 짝이 없는 행에 NULL을 넣는다는 점은 함수와 같다.
수정 가능 조인 뷰는 M_ITM이 PK로 조인되어 T_ORD_DET가 키 보존 테이블일 때만 된다. PK가 없으면 ORA-01779로 거절된다.
이관 공통 함수는 다른 단계에서도 쓰일 수 있다. 함수는 남겨 두고 대량 처리 단계에서만 조인으로 바꾼다. 단가 규칙이 함수 안에서 더 복잡해지면(할인, 기간) 규칙이 두 곳이 되지 않게 조인 쪽도 함께 고친다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황