협력사 납품 확인 7,102줄 반영을 addBatch로 묶었는데 13초가 그대로다

중급PLSQL유통588 / 700

상황

유통 본사 협력사 연동 서버(Java)는 협력사가 보낸 납품 확인 파일(발주번호, 상품번호, 납품 수량)을 받아 줄마다 구매팀 패키지의 프로시저 PKG_PO.CONFIRM_LINE을 부른다. 프로시저는 발주 상태를 확인하고, 초과 납품이나 마감된 발주는 반송함에 적고, 나머지는 발주상품의 입고 수량을 고친다. 구매팀은 "같은 규칙을 우리 화면도 쓰니 프로시저는 그대로 써야 한다"라고 한다. 이번 달 7,102줄에 13.1초가 걸렸다. 개발자가 지난주 addBatch로 500줄씩 묶었는데 12.8초다.

테이블과 인덱스

CF13 발주상품: 7,102행 · 57블록 · PK (ORD_NO, ITM_NO) · ORD_QTY · RCV_QTY(입고 수량) · ORD_NO는 VARCHAR2(12)
CF12 발주: 734행 · 10블록 · PK (ORD_NO) · ORD_PRGS_CD(진행 상태)
CONFIRM_LINE 한 번은 SQL을 두 번(발주 상태 SELECT, 발주상품 UPDATE 또는 반송함 INSERT) 실행하고 DB 안에서 약 0.15ms가 걸린다. 규칙 위반은 예외 없이 반송함에 적고 돌아온다.
연동 서버는 DMZ에 있고 DB 서버와의 왕복은 1.6ms다. 반영은 한 트랜잭션이고 끝에 한 번 커밋한다.

원본 SQL

sql

// PoConfirmJob.java : 줄마다 프로시저를 부른다 (지난주 addBatch로 500줄씩 묶었다)
CallableStatement cs = conn.prepareCall("{call PKG_PO.CONFIRM_LINE(?, ?, ?)}");
int n = 0;
for (PoLine l : lines) {                       // 7,102줄
    cs.setString(1, l.ordNo);
    cs.setInt(2, l.itmNo);
    cs.setInt(3, l.qty);
    cs.addBatch();
    if (++n % 500 == 0) cs.executeBatch();
}
cs.executeBatch();
conn.commit();

-- PKG_PO.CONFIRM_LINE (구매팀 · 규칙은 고치지 않는다)
PROCEDURE CONFIRM_LINE(p_ord_no IN VARCHAR2, p_itm_no IN NUMBER, p_qty IN NUMBER);

실행계획 학습용 예시

xplan

[반영 한 번 (7,102줄) · 연동 서버 세션 통계 증가분 · addBatch 판]
  경과 시간                               12.8 초
  user calls                               7,121
  SQL*Net roundtrips to/from client        7,116
  execute count                           21,330   (호출 블록 7,102 + 블록 안 SQL 14,204 + 그 밖)
  recursive calls                         14,268
  session logical reads                   29,640
  DB time                                   1.2 초
  SQL*Net message from client             7,115번 · 11.4 초 (평균 1.6 ms)

[V$SQL]
  BEGIN PKG_PO.CONFIRM_LINE(:1 , :2 , :3 ); END;                    EXECUTIONS 7,102
  SELECT ORD_PRGS_CD FROM CF12 WHERE ORD_NO = :B1                   EXECUTIONS 7,102
  UPDATE CF13 SET RCV_QTY = NVL(RCV_QTY, 0) + :B1 WHERE ...         EXECUTIONS 7,044
  INSERT INTO T_PO_REJECT ...                                       EXECUTIONS    58

[addBatch 전 · 같은 7,102줄]  13.1 초 · roundtrips 7,118 · user calls 7,124

문제

addBatch로 묶었는데 왕복이 거의 그대로인 이유를 위 통계로 설명하시오. 12.8초가 어디에 쓰였는지도 쓰시오.
프로시저의 규칙은 그대로 쓰면서 연동 서버와 DB 사이의 호출 수를 줄이도록 고치시오. 고친 뒤 줄지 않는 숫자는 무엇이고 왜 그런지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- DB : 줄 묶음을 받는 타입과 프로시저 (새 패키지 · 규칙은 CONFIRM_LINE을 그대로 부른다)
CREATE OR REPLACE TYPE PO_LINE_T AS OBJECT (ORD_NO VARCHAR2(12), ITM_NO NUMBER, QTY NUMBER);
/
CREATE OR REPLACE TYPE PO_LINE_TAB AS TABLE OF PO_LINE_T;
/
CREATE OR REPLACE PACKAGE BODY PKG_PO_IF AS
  PROCEDURE CONFIRM_LINES(p_lines IN PO_LINE_TAB) IS
  BEGIN
    FOR i IN 1 .. p_lines.COUNT LOOP
      PKG_PO.CONFIRM_LINE(p_lines(i).ORD_NO, p_lines(i).ITM_NO, p_lines(i).QTY);
    END LOOP;
  END;
END PKG_PO_IF;
/

// PoConfirmJob.java : 1,000줄을 배열 하나로 넘긴다 · 호출 한 번 = 왕복 한 번
OracleConnection oc = conn.unwrap(OracleConnection.class);
CallableStatement cs = conn.prepareCall("{call PKG_PO_IF.CONFIRM_LINES(?)}");
for (List<PoLine> chunk : partition(lines, 1000)) {
    Struct[] rows = new Struct[chunk.size()];
    for (int i = 0; i < chunk.size(); i++) {
        PoLine l = chunk.get(i);
        rows[i] = conn.createStruct("PO_LINE_T", new Object[] { l.ordNo, l.itmNo, l.qty });
    }
    cs.setArray(1, oc.createOracleArray("PO_LINE_TAB", rows));
    cs.execute();
}
conn.commit();                                 // 커밋은 전처럼 끝에 한 번

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

xplan

[반영 한 번 (7,102줄) · 고친 뒤 (학습용 예시)]
  경과 시간                               1.4 초
  user calls                                 16
  SQL*Net roundtrips to/from client          18
  execute count                          14,231   (호출 블록 8 + 블록 안 SQL 14,204 + 그 밖)
  recursive calls                        14,290
  session logical reads                  29,610
  DB time                                   1.3 초
  SQL*Net message from client            17번 · 0.06 초

[V$SQL]
  BEGIN PKG_PO_IF.CONFIRM_LINES(:1 ); END;                         EXECUTIONS     8
  SELECT ORD_PRGS_CD FROM CF12 WHERE ORD_NO = :B1                  EXECUTIONS 7,102

모범 답안 서술

V$SQL의 호출 블록 BEGIN PKG_PO.CONFIRM_LINE(…); END;가 7,102번 실행됐고, user calls 7,121과 roundtrips 7,116도 addBatch 전(7,124와 7,118)과 거의 같다. 오라클 JDBC가 진짜로 묶어 보내는 것은 바인드 값만 바뀌는 PreparedStatement의 DML이다. CallableStatement의 addBatch는 문법만 받아 줄 뿐 실제로는 호출을 하나씩 보낸다. 그래서 500줄씩 묶었다고 생각했지만 일은 그대로였다(13.1 → 12.8초). 12.8초의 대부분은 왕복이다. 연동 서버는 DMZ에 있어 왕복 한 번에 1.6ms가 들고, 7,115번이면 SQL*Net message from client가 11.4초다. DB 안에서 프로시저가 쓴 시간(DB time)은 1.2초뿐이다. 고친 판은 줄 1,000개를 배열 하나(PO_LINE_TAB)로 넘긴다. 새 패키지의 CONFIRM_LINES가 배열을 받아 안에서 줄마다 PKG_PO.CONFIRM_LINE을 그대로 부르므로 구매팀의 규칙은 한 글자도 바뀌지 않는다. 연동 서버와 DB 사이의 호출은 7,102 → 8번, 왕복은 7,116 → 18번, 경과 시간은 12.8 → 1.4초가 된다. 줄지 않는 숫자도 있다. 프로시저는 여전히 줄마다 SQL을 두 번 실행하므로 recursive calls(14,268 → 14,290)와 논리 읽기(약 29,600)는 그대로다. 줄인 것은 네트워크를 건너는 호출이고, DB 안에서 PL/SQL과 SQL 사이를 오가는 재귀 호출은 다른 층의 일이다. 그것까지 줄이려면 규칙을 집합 처리로 다시 짜야 하는데, 구매팀 화면과 규칙을 함께 쓰므로 이번에는 손대지 않는다. 대가도 적어 둔다. SQL 타입 둘과 새 패키지를 배포해야 한다(PKG_PO는 건드리지 않으므로 구매팀 화면은 재컴파일과 무관하다). 묶음 안의 한 줄에서 예상 못 한 오류가 나면 그 묶음 호출 전체가 실패하고, 커밋 전이므로 전처럼 반영 전체가 롤백된다. 몇 번째 줄인지는 오류를 잡아 첨자와 함께 기록해야 알 수 있다. 1,000줄씩이면 왕복은 충분히 줄고 한 번에 넘기는 배열도 수십 KB라 부담이 없다.

해설

오라클 JDBC 문서도 Statement와 CallableStatement의 표준 배치는 진짜 배치가 아니라고 적고 있다. 묶였는지는 설정이 아니라 user calls, roundtrips, 호출 블록의 EXECUTIONS로 확인한다.
넘기는 방법은 여럿이다. SQL 객체 타입의 컬렉션(createOracleArray), 칸마다 따로 만든 숫자와 문자 배열 여러 개, PL/SQL 연관 배열(setPlsqlIndexTable)이 있다. 칸이 몇 개 안 되면 칸별 배열 셋이 가장 간단하다.
규칙을 바꿀 수 있다면 CONFIRM_LINES 안을 FORALL이나 한 문장의 MERGE로 짜서 재귀 호출까지 줄일 수 있다(교재 15화의 BULK와 FORALL). 이번 문제는 규칙을 그대로 둬야 하는 경우다.
협력사 파일을 외부 테이블로 읽을 수 있다면 연동 서버를 거치지 않고 DB 안에서 처리하는 방법도 있다. 다만 DMZ에 있던 파일을 DB 서버로 옮기는 보안 절차가 따른다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황