batch_size를 500으로 넣었는데 보정 요청 12,000건 업로드가 여전히 10초 가까이 걸린다

중급INSERT한빛유통586 / 700

상황

한빛유통 정산팀은 매주 보정 요청 엑셀(약 12,000줄)을 관리 화면(Spring Boot, JPA)에 올린다. 화면은 줄마다 보정 요청 엔티티를 만들어 saveAll로 한꺼번에 저장한다. 업로드가 10초 가까이 걸려서 가끔 화면의 제한 시간(10초)을 넘긴다. 개발자는 블로그 글을 보고 hibernate.jdbc.batch_size=500을 넣었지만 9.8초가 9.6초가 됐을 뿐이다. "배치 크기를 넣었으니 500줄씩 묶여 나가야 하는데요?"

테이블과 인덱스

T_STLM_ADJ_REQ 보정 요청(이 문제에서 쓰는 테이블): REQ_ID NUMBER GENERATED BY DEFAULT AS IDENTITY(PK) · STLM_SEQ · ADJ_AMT · REQ_DT · REQ_USER · 지금 31만 행, 주에 약 12,000행씩 는다 · 다른 프로그램은 이 테이블에 넣지 않는다
REQ_ID는 업무 의미가 없는 키다(화면과 보고서는 STLM_SEQ로 찾는다).
관리 화면 서버와 DB의 왕복은 0.5ms다. 업로드는 한 트랜잭션으로 저장하고 끝에 한 번 커밋한다.

원본 SQL

sql

// AdjReq.java : 키는 DB의 IDENTITY 칸이 매긴다
@Entity
@Table(name = "T_STLM_ADJ_REQ")
public class AdjReq {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "REQ_ID")
    private Long reqId;
    private Long stlmSeq;
    private BigDecimal adjAmt;
    private LocalDateTime reqDt;
    private String reqUser;
}

# application.properties (지난주 추가)
spring.jpa.properties.hibernate.jdbc.batch_size=500

// AdjUploadService.java
@Transactional
public void upload(List<AdjRow> rows) {
    repo.saveAll(rows.stream().map(AdjReq::from).collect(toList()));   // 12,040건
}

실행계획 학습용 예시

xplan

[업로드 한 번 (12,040줄) · 세션 통계 증가분]
  경과 시간                               9.6 초
  execute count                        12,046
  SQL*Net roundtrips to/from client    12,051
  session logical reads                49,812
  SQL*Net message from client          12,050번 · 6.9 초

[V$SQL]
  SQL_TEXT                                                                         EXECUTIONS   ROWS_PROCESSED
  insert into T_STLM_ADJ_REQ (adj_amt, req_dt, req_user, stlm_seq) values
    (:1 , :2 , :3 , :4 ) RETURNING REQ_ID INTO :5                                      12,040           12,040

[한 번 실행]  INSERT STATEMENT → LOAD TABLE CONVENTIONAL · Buffers 4 (테이블 블록, PK 인덱스 리프)

문제

batch_size를 넣었는데도 INSERT가 묶이지 않은 것을 위 숫자로 확인하고, 왜 묶이지 않았는지 설명하시오.
같은 데이터를 넣으면서 INSERT가 묶여 나가도록 고치시오. 고친 뒤의 실행 수와 왕복 수를 쓰고, 이 방법의 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- DB : 시퀀스를 만들고(지금 키보다 위에서 시작) IDENTITY는 뗀다 · INCREMENT BY = allocationSize
CREATE SEQUENCE SEQ_STLM_ADJ_REQ START WITH 1000000 INCREMENT BY 500 CACHE 20;
ALTER TABLE T_STLM_ADJ_REQ MODIFY (REQ_ID DROP IDENTITY);

// AdjReq.java : 키를 INSERT 전에 받아 둔다 (시퀀스 한 번에 500개)
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "adjReqSeq")
@SequenceGenerator(name = "adjReqSeq", sequenceName = "SEQ_STLM_ADJ_REQ", allocationSize = 500)
@Column(name = "REQ_ID")
private Long reqId;

# application.properties
spring.jpa.properties.hibernate.jdbc.batch_size=500
spring.jpa.properties.hibernate.order_inserts=true

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

xplan

[업로드 한 번 (12,040줄) · 고친 뒤 (학습용 예시)]
  경과 시간                               0.52 초
  execute count                              51   (INSERT 배치 25 + 시퀀스 25 + 기타)
  SQL*Net roundtrips to/from client          55
  session logical reads                  36,900
  SQL*Net message from client            54번 · 0.04 초

[V$SQL]
  insert into T_STLM_ADJ_REQ (adj_amt, req_dt, req_user, stlm_seq, req_id) values (:1 , :2 , :3 , :4 , :5 )
                                                                     EXECUTIONS 25 · ROWS_PROCESSED 12,040 (실행 한 번에 약 482행)
  select SEQ_STLM_ADJ_REQ.nextval from dual                           EXECUTIONS 25

모범 답안 서술

V$SQL에서 INSERT의 EXECUTIONS와 ROWS_PROCESSED가 둘 다 12,040이다. 실행 한 번에 한 행이므로 batch_size를 넣었어도 하나도 묶이지 않았다. 묶였다면 실행 한 번에 수백 행이어야 한다. 줄마다 실행 한 번, 왕복 한 번이라 왕복이 12,051번이고, 그 대기(SQL*Net message from client)가 6.9초다. 묶이지 않은 까닭은 키 생성 방식이다. GenerationType.IDENTITY는 키를 DB의 IDENTITY 칸이 INSERT하는 순간 매긴다. JPA는 저장한 엔티티의 키를 바로 알아야 하므로, 문장 끝에 RETURNING REQ_ID INTO :5를 붙여 행마다 키를 돌려받는다. 키가 INSERT를 실행해야만 나오니 여러 행을 모아 둘 수 없고, 그래서 Hibernate는 IDENTITY 엔티티의 JDBC 배치를 소리 없이 끈다. 설정 한 줄은 들어갔지만 일은 그대로였다. 고친 판은 키를 INSERT 전에 시퀀스에서 받아 둔다. @SequenceGenerator의 allocationSize = 500이면 Hibernate가 NEXTVAL을 한 번 부를 때마다 500개 번호를 쓴다. 그러려면 시퀀스의 INCREMENT BY도 500이어야 한다. 키를 미리 알고 있으니 500행을 모아 한 번에 보낼 수 있다. INSERT 실행은 12,040 → 25번(실행 한 번에 약 482행), 시퀀스 호출은 25번, 왕복은 12,051 → 55번, 업로드는 9.6초 → 0.52초가 된다. 묶어 넣으면 블록을 한 번 잡고 여러 행을 넣으므로 논리 읽기도 49,812 → 36,900으로 준다. 대가도 있다. 서버가 받아 둔 500개를 다 쓰지 못하고 재기동하면 남은 번호는 버려지므로 키 번호에 빈 곳이 생긴다. REQ_ID는 업무 의미가 없는 키라 받아들일 수 있다. 시퀀스의 INCREMENT BY와 allocationSize가 어긋나면 서버 두 대가 같은 번호를 쓸 수 있으므로 둘을 같은 값으로 묶어 관리한다. 시퀀스는 지금 키(31만)보다 위에서 시작하고, 다른 길로 번호가 나오지 않게 IDENTITY는 뗀다.

해설

설정을 넣었다는 사실보다 숫자가 중요하다. 묶였는지는 V$SQL의 ROWS_PROCESSED ÷ EXECUTIONS로 확인하고, 그 값을 리포트에 적는다(교재 15화의 「PL/SQL 호출 통계」 칸과 같은 생각이다).
JPA를 거치지 않고 JdbcTemplate.batchUpdate로 넣으면 IDENTITY를 그대로 두고도 묶을 수 있다. 저장한 행의 키를 화면에 돌려줄 필요가 없는 업로드라면 가장 간단한 답이다.
엑셀 내용을 임시 테이블에 올리고 INSERT … SELECT 한 문장으로 넣는 방법이 가장 빠르지만(DML 단원), 줄마다 검증 규칙을 JPA 엔티티에 둔 화면이라면 손이 많이 간다.
번호가 비면 안 되는 칸(정산 번호처럼 연속성이 업무 규칙인 번호)에는 이 방법을 쓰지 않는다. 그런 번호를 매기는 법은 LOCK 단원의 채번에서 다룬다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황