실사 보정 8,412줄을 반영하는 데 7.9초, 하드 파싱은 두 번뿐이다

기초UPDATE유통581 / 700

상황

유통 본사 물류팀은 매달 말 물류센터의 실사 결과 파일(입출고번호, 상품번호, 실사 수량)로 입출고상품 수량을 보정한다. 2011년에 만든 Java 프로그램이 파일 한 줄마다 UPDATE를 한 번씩 보낸다. 이번 달 8,412줄에 7.9초가 걸렸다. DBA가 세션 통계를 보내며 말했다. "바인드 변수는 잘 쓰고 있어요. 그런데 parse count가 실행 수만큼 올라갑니다."

테이블과 인덱스

CF15 입출고상품: 51,497행 · 349블록 · PK (INOUT_NO, ITM_NO) · INOUT_NO는 VARCHAR2(15), ITM_NO는 NUMBER다
한 줄을 고치는 데 PK로 3블록 남짓을 읽는다.
배치 서버와 DB 서버의 왕복은 0.6ms다. JDBC 문장 캐시는 쓰지 않는다. session_cached_cursors = 50

원본 SQL

sql

// StockAdjust.java (2011) : 실사 파일 한 줄마다 문장을 준비하고, 실행하고, 닫는다
conn.setAutoCommit(false);
for (String[] f : lines) {                        // 8,412줄
    PreparedStatement ps = conn.prepareStatement(
        "UPDATE CF15 SET INOUT_QTY = ? WHERE INOUT_NO = ? AND ITM_NO = ?");
    ps.setInt(1, Integer.parseInt(f[2]));
    ps.setString(2, f[0]);
    ps.setInt(3, Integer.parseInt(f[1]));
    ps.executeUpdate();
    ps.close();
}
conn.commit();

실행계획 학습용 예시

xplan

[세션 통계 증가분 · 8,412줄 한 번]
  경과 시간                              7.9 초
  execute count                          8,431
  parse count (total)                    8,433
  parse count (hard)                         2
  session cursor cache hits              8,409
  opened cursors cumulative              8,440
  SQL*Net roundtrips to/from client      8,418
  session logical reads                 33,890
  CPU used by this session                 214 cs

[V$SQL · UPDATE CF15 SET INOUT_QTY = :1 WHERE INOUT_NO = :2 AND ITM_NO = :3]
  PARSE_CALLS 8,412 · EXECUTIONS 8,412 · ROWS_PROCESSED 8,412 · BUFFER_GETS 33,648

[V$SESSION_EVENT · 같은 세션]
  SQL*Net message from client            8,417번 · 5.3 초
  log file sync                              1번 · 0.004 초

[한 번 실행의 계획]
---------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |         |      1 |        |      0 |00:00:00.01 |       4 |
|   1 |  UPDATE            | CF15    |      1 |        |      0 |00:00:00.01 |       4 |
|*  2 |   INDEX UNIQUE SCAN| CF15_PK |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("INOUT_NO"=:2 AND "ITM_NO"=:3)

문제

하드 파싱은 두 번뿐인데 parse count (total)가 실행 수만큼 오른 이유를 코드와 V$SQL로 설명하시오. 7.9초가 어디에 쓰였는지도 쓰시오.
결과가 바뀌지 않게 프로그램을 고치고, 고친 뒤 파스 호출과 왕복이 몇 번이 되는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

// StockAdjust.java : 문장은 한 번 준비하고, 1,000줄씩 묶어 보낸다
conn.setAutoCommit(false);
try (PreparedStatement ps = conn.prepareStatement(
         "UPDATE CF15 SET INOUT_QTY = ? WHERE INOUT_NO = ? AND ITM_NO = ?")) {
    int n = 0;
    for (String[] f : lines) {                    // 8,412줄
        ps.setInt(1, Integer.parseInt(f[2]));
        ps.setString(2, f[0]);
        ps.setInt(3, Integer.parseInt(f[1]));
        ps.addBatch();
        if (++n % 1000 == 0) ps.executeBatch();   // 1,000줄에 왕복 한 번
    }
    ps.executeBatch();                            // 나머지 412줄
}
conn.commit();                                    // 커밋은 전처럼 끝에 한 번

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

xplan

[세션 통계 증가분 · 같은 8,412줄 (학습용 예시)]
  경과 시간                              0.31 초
  execute count                             28
  parse count (total)                       21
  parse count (hard)                         2
  SQL*Net roundtrips to/from client         14
  session logical reads                 33,710
  CPU used by this session                  19 cs

[V$SQL · 같은 UPDATE]
  PARSE_CALLS 1 · EXECUTIONS 9 · ROWS_PROCESSED 8,412 (실행 한 번에 약 935행)

모범 답안 서술

V$SQL의 PARSE_CALLS와 EXECUTIONS가 둘 다 8,412다. 프로그램이 줄마다 prepareStatement로 문장을 준비하고 실행한 뒤 close로 닫으므로, 실행할 때마다 DB에 파스 호출이 온다. 문장 텍스트는 하나이고 바인드 변수를 쓰므로 하드 파싱은 처음 두 번뿐이고 나머지는 소프트 파싱이다. session cursor cache hits 8,409를 보면 세션 커서 캐시가 그 소프트 파싱을 받아 주고 있다. 하지만 캐시는 커서를 찾는 길을 줄여 파싱을 가볍게 할 뿐 파스 호출 자체를 없애지는 못한다. 커서를 닫고 여는 일(opened cursors cumulative 8,440)도 그대로다. 7.9초의 대부분은 파싱이 아니라 왕복이다. 줄마다 executeUpdate가 DB까지 한 번 갔다 오므로 왕복이 8,418번이고, DB가 다음 줄을 기다린 SQL*Net message from client가 5.3초다. 한 줄을 고치는 일 자체는 4블록이다. 고친 판은 문장을 루프 밖에서 한 번만 준비하고 값만 바꿔 쓴다. 파스 호출은 8,412 → 1번이 된다. 그리고 addBatch로 1,000줄씩 묶어 executeBatch로 보내면 1,000줄이 실행 한 번, 왕복 한 번이다. 실행은 8,412 → 9번, 왕복은 8,418 → 14번, 경과 시간은 7.9 → 0.31초다. 커밋은 전처럼 끝에 한 번이라 결과도 같다. 고친 행 수(8,412)와 논리 읽기(약 33,700)는 거의 그대로다. 줄인 것은 일의 양이 아니라 DB를 부르는 횟수다.

해설

파싱 네 가지 가운데 이 프로그램은 ②, ③(소프트 파싱, 세션 커서 캐시 적중)에 머물러 있었다. 문장을 열어 둔 채 다시 실행하는 ④로 옮기는 것은 애플리케이션만 할 수 있다(교재 11화 §2).
JDBC 문장 캐시(implicitStatementCacheSize)를 켜도 파스 호출은 사라진다. 다만 그것은 화면처럼 문장을 여기저기서 준비하는 프로그램에 맞는 장치이고, 루프 하나라면 문장을 밖으로 빼는 것이 가장 간단하다.
파일을 외부 테이블이나 SQL*Loader로 임시 테이블에 올린 뒤 MERGE 한 문장으로 고치면 실행도 1번이 된다. 파일 형식을 DB가 읽을 수 있고 물류팀이 그 절차를 받아들일 때의 답이다.
묶음이 너무 크면 클라이언트 메모리와 실패했을 때 다시 볼 범위가 커진다. 1,000줄 안팎이면 왕복은 충분히 준다(14번).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황