10분마다 도는 택배 상태 반영이 AWR의 실행 횟수 1위와 2위를 차지한다

중급PLSQL한빛유통676 / 700

상황

한빛유통은 택배사 다섯 곳이 보내는 배송 상태 파일을 10분마다 받아 주문별 배송 추적 테이블에 반영한다. 고객센터 화면과 주문 조회 화면이 이 테이블을 읽는다. DBA가 주간 AWR을 보다가 「SQL ordered by Executions」의 1위와 2위가 이 프로그램의 UPDATE와 INSERT인 것을 찾았다. 그 시간대에는 주문 화면과 CPU를 나눠 쓴다. 개발자는 이렇게 말한다. "FORALL로 묶으면 어느 주문이 UPDATE 0건이었는지 알 수 없어서 루프로 뒀어요."

테이블과 인덱스

T_DLV_RCV(택배사 수신, 이 문제용): RCV_NO(회차) · ORD_SEQ · DLV_CO · RAW_ST · EVT_DT · 회차마다 평균 4,400행 · 인덱스 IX_DLV_RCV_NO (RCV_NO)
수신 프로그램이 주문마다 최신 상태 한 행만 남기므로, 한 회차에 같은 주문은 한 번만 온다.
T_DLV_TRACK(배송 추적, 이 문제용): 주문당 한 행 · PK_T_DLV_TRACK (ORD_SEQ) · DLV_CO · DLV_ST · LAST_EVT_DT
한 회차 4,400행 가운데 약 4,050행은 이미 추적 중인 주문(UPDATE)이고, 약 350행은 처음 오는 주문(INSERT)이다.
PKG_DLV_MAP.TO_STD(dlv_co, raw_st): 택배사별 상태 코드를 표준 코드로 바꾸는 PL/SQL 함수(SQL 없음) · 고객센터 화면도 같은 함수를 쓴다
회차는 06:00 ~ 24:00에 10분마다, 하루 108번 돈다.

원본 SQL

sql

PROCEDURE APPLY_DLV_STATUS(p_rcv_no NUMBER) IS
  v_st VARCHAR2(10);
BEGIN
  FOR r IN (SELECT ORD_SEQ, DLV_CO, RAW_ST, EVT_DT
              FROM T_DLV_RCV
             WHERE RCV_NO = p_rcv_no) LOOP
    v_st := PKG_DLV_MAP.TO_STD(r.DLV_CO, r.RAW_ST);
    UPDATE T_DLV_TRACK
       SET DLV_ST = v_st, LAST_EVT_DT = r.EVT_DT
     WHERE ORD_SEQ = r.ORD_SEQ;
    IF SQL%ROWCOUNT = 0 THEN                     -- 처음 오는 주문
      INSERT INTO T_DLV_TRACK (ORD_SEQ, DLV_CO, DLV_ST, LAST_EVT_DT)
      VALUES (r.ORD_SEQ, r.DLV_CO, v_st, r.EVT_DT);
    END IF;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[AWR — 하루치 (SQL ordered by Executions)]
   Executions   Rows Processed   CPU (s)   SQL
      475,236          437,412      31.2   UPDATE T_DLV_TRACK SET DLV_ST = :B3 , LAST_EVT_DT = :B2 WHERE ORD_SEQ = :B1
       37,824           37,824       4.9   INSERT INTO T_DLV_TRACK (ORD_SEQ, DLV_CO, DLV_ST, LAST_EVT_DT) VALUES (:B4 , …)
          108          475,236       0.4   SELECT ORD_SEQ, DLV_CO, RAW_ST, EVT_DT FROM T_DLV_RCV WHERE RCV_NO = :B1

[한 회차 — 4,400행 (V$MYSTAT 증가분)]
  경과 0.41 초 · CPU 0.38 초 · recursive calls 4,797 · execute count 4,752
  session logical reads 20,614 · redo size 2.0 MB

문제

한 회차에 SQL이 몇 번 실행되는지 AWR과 세션 통계로 설명하시오.
결과가 바뀌지 않게 반영 프로그램을 고치시오. FORALL에서 원소마다 UPDATE된 행 수를 어떻게 아는지도 쓰시오.
고친 프로그램이 원래 프로그램과 같은 결과를 내기 위한 전제 조건을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE APPLY_DLV_STATUS(p_rcv_no NUMBER) IS
  CURSOR c IS SELECT ORD_SEQ, DLV_CO, RAW_ST, EVT_DT FROM T_DLV_RCV WHERE RCV_NO = p_rcv_no;
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  TYPE t_vc   IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  v       t_rows;
  v_st    t_vc;
  ins     t_rows := t_rows();          -- UPDATE 0건이던 원소 → INSERT
  ins_st  t_vc;
  k       PLS_INTEGER;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v LIMIT 1000;
    EXIT WHEN v.COUNT = 0;
    FOR i IN 1 .. v.COUNT LOOP
      v_st(i) := PKG_DLV_MAP.TO_STD(v(i).DLV_CO, v(i).RAW_ST);
    END LOOP;

    FORALL i IN 1 .. v.COUNT                       -- 천 건을 한 번에 UPDATE
      UPDATE T_DLV_TRACK
         SET DLV_ST = v_st(i), LAST_EVT_DT = v(i).EVT_DT
       WHERE ORD_SEQ = v(i).ORD_SEQ;

    ins.DELETE;  k := 0;
    FOR i IN 1 .. v.COUNT LOOP
      IF SQL%BULK_ROWCOUNT(i) = 0 THEN             -- 원소마다 고친 행 수가 남아 있다 (다른 SQL을 실행하기 전에 읽는다)
        k := k + 1;
        ins.EXTEND;
        ins(k)    := v(i);
        ins_st(k) := v_st(i);
      END IF;
    END LOOP;

    FORALL i IN 1 .. k                             -- 처음 오는 주문만 한 번에 INSERT
      INSERT INTO T_DLV_TRACK (ORD_SEQ, DLV_CO, DLV_ST, LAST_EVT_DT)
      VALUES (ins(i).ORD_SEQ, ins(i).DLV_CO, ins_st(i), ins(i).EVT_DT);
  END LOOP;
  CLOSE c;
  COMMIT;
END;

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

xplan

[한 회차 — 고친 판 (같은 4,400행)]
  경과 0.09 초 · CPU 0.08 초 · recursive calls 18 · execute count 12
  session logical reads 19,962 · redo size 1.6 MB
  UPDATE Executions 5 (rows 4,050) · INSERT Executions 5 (rows 350)
  T_DLV_TRACK 결과는 루프 판과 같다 (MINUS 0 / 0)

[하루로 늘리면]
  UPDATE Executions 475,236 → 540 · INSERT Executions 37,824 → 540 · 두 문장의 CPU 36.1 → 6.2 초

모범 답안 서술

AWR에서 UPDATE는 하루 475,236번, INSERT는 37,824번 실행됐다. 108회차로 나누면 한 회차에 UPDATE 4,400번, INSERT 350번이다. 수신 행마다 UPDATE를 한 번 해 보고, 0건이면 INSERT를 한 번 더 한다. 세션 통계의 recursive calls 4,797도 같은 숫자다(UPDATE 4,400 + INSERT 350 + 구동 커서 Fetch 45 + 2). 한 번은 PK로 한 행을 찾아 고치는 가벼운 일이지만, 하루 51만 번이 넘는 실행이 실행 횟수 1위와 2위를 차지했다. FORALL로 묶어도 원소마다의 결과는 알 수 있다. FORALL이 끝나면 SQL%BULK_ROWCOUNT(i)에 i번째 원소가 고친 행 수가 남는다. 그래서 상태 변환은 PL/SQL 배열에 먼저 해 두고, FORALL로 천 건씩 UPDATE한 뒤, SQL%BULK_ROWCOUNT(i) = 0인 원소만 모아 FORALL로 INSERT한다. 이 값은 다른 SQL을 실행하기 전에 읽어야 한다. 한 회차의 recursive calls는 4,797 → 18, 경과는 0.41초 → 0.09초, 하루 실행 횟수는 UPDATE 475,236 → 540, INSERT 37,824 → 540으로 준다. 논리 읽기는 20,614 → 19,962로 거의 같다. 원소마다 PK로 주문을 찾아 고치는 일은 그대로 남기 때문이다. 줄어든 것은 실행 횟수와 엔진을 오가는 CPU다. 주문 화면과 CPU를 나눠 쓰는 시간대라 이 몫이 의미가 있다. 전제 조건은 한 회차에 같은 주문이 한 번만 온다는 것이다. 원래 루프는 원소를 하나씩 차례로 처리하므로, 같은 주문이 두 번 오면 첫 행은 INSERT, 둘째 행은 방금 넣은 행을 UPDATE한다. FORALL 판은 같은 덩어리의 두 원소가 모두 UPDATE 0건이 되어 두 번 INSERT하고 ORA-00001이 난다. 지금은 수신 프로그램이 주문마다 최신 한 행만 남기므로 결과가 같다. 이 전제가 깨질 수 있다면 구동 커서에서 ROW_NUMBER() OVER (PARTITION BY ORD_SEQ ORDER BY EVT_DT DESC) = 1로 한 행만 남긴다.

해설

다른 정답: 구동 커서에서 T_DLV_TRACK을 아우터 조인해 NVL2(t.ORD_SEQ, 'U', 'I')로 미리 가르고, UPDATE 배열과 INSERT 배열을 따로 FORALL하는 방법도 같은 답이다(교재 15화 §5.3). 덩어리마다 추적 테이블을 한 번 더 읽지만, '이미 있나'를 문장 하나로 묻는다.
함정: FORALL i … MERGE INTO T_DLV_TRACK USING (SELECT v(i)… FROM dual) …로 바꾸면 호출은 줄지만, 원소마다 대상 행을 찾아 있으면 UPDATE, 없으면 INSERT하는 행 단위 MERGE다. 교재 15화의 측정에서 이 모양은 논리 읽기와 redo가 루프 판과 거의 같았다.
SQL%BULK_ROWCOUNT는 FORALL의 첨자와 같은 첨자로 읽는다. INDICES OF로 구멍 난 배열을 보냈다면 있는 첨자만 따라가며 읽는다.
회차를 10분에서 30분으로 늘리자는 제안은 CPU를 줄이지 못한다. 같은 행 수를 덜 자주 처리할 뿐이고, 고객센터가 보는 배송 상태만 늦어진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황