한 달 치 주문상품 내려받기가 사무실 PC에서는 1분 12초, IDC 안 PC에서는 6초다

기초SELECT버거577 / 700

상황

버거 본사 영업관리팀은 매달 초 사내 리포트 도구(2015년, Java)로 지난달 주문상품을 엑셀로 내려받는다. 2013년 12월분 103,030행을 받는 데 1분 12초가 걸린다. 영업관리팀 사무실은 본사 IDC와 VPN으로 이어져 있다. 같은 도구를 IDC 안의 운영 PC에서 돌리면 6초 남짓이다. DBA는 이렇게 말했다. "DB 안에서 이 SQL이 쓴 시간은 0.4초뿐입니다. DB 문제는 아니에요."

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 날짜순으로 적재되어 있다 · 2013년 12월 103,030행(테이블 약 2,300블록, 인덱스 리프 약 380블록)
ITEM 상품: 92행 · 5블록 · PK (ITEM_ID)
리포트 도구는 오라클 JDBC 드라이버를 쓰고 fetch size는 손대지 않았다(드라이버 기본값 10).
사무실 PC와 DB 서버 사이의 왕복 시간(RTT)은 약 6.7ms, IDC 안 PC는 0.3ms다. 엑셀 시트를 쓰는 데는 어느 PC든 2.6초쯤 걸린다. 통계는 최신이다.

원본 SQL

sql

// MonthItemExport.java (2015) : 한 달 치 주문상품을 받아 엑셀 시트에 한 줄씩 쓴다
String sql = "SELECT i.ORD_DT, i.ORD_HMS, i.ORD_NO, i.ITEM_ID, m.ITEM_NM, i.ORD_ITEM_QTY "
           + "FROM ORD_ITEM i, ITEM m "
           + "WHERE i.ORD_DT BETWEEN ? AND ? "
           + "AND m.ITEM_ID = i.ITEM_ID";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, fromDt);                    // '20131201'
ps.setString(2, toDt);                      // '20131231'
ResultSet rs = ps.executeQuery();           // 한 번에 받는 행 수는 정하지 않았다
while (rs.next()) {
    sheet.addRow(rs.getString(1), rs.getString(2), rs.getLong(3), rs.getInt(4), rs.getString(5), rs.getInt(6));
}

실행계획 학습용 예시 · 페치 수의 셈은 교재 09화 실측과 같다

xplan

[사무실 PC에서 한 번 · 세션 통계 증가분 (V$SESSTAT)]
  경과 시간 (도구 화면 기준)               72.4 초
  SQL*Net roundtrips to/from client       10,306
  user calls                              10,308
  session logical reads                   23,318
  bytes sent via SQL*Net to client        6.4 MB

[같은 세션 · V$SESSION_EVENT]
  SQL*Net message from client             10,305번 · 71.6 초 (평균 6.9 ms)
  SQL*Net message to client               10,306번 ·  0.01 초

[V$SQL]
  EXECUTIONS 1 · FETCHES 10,304 · ROWS_PROCESSED 103,030 · ELAPSED_TIME 0.41 초 · BUFFER_GETS 23,301

[DBMS_XPLAN.DISPLAY_CURSOR — 위 실행 (모든 페치를 합친 값)]
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |    103K|00:00:00.41 |   23301 |
|*  1 |  HASH JOIN                            |              |      1 |    102K|    103K|00:00:00.41 |   23301 |
|   2 |   TABLE ACCESS FULL                   | ITEM         |      1 |     92 |     92 |00:00:00.01 |       5 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED | ORD_ITEM     |      1 |    102K|    103K|00:00:00.33 |   23296 |
|*  4 |    INDEX RANGE SCAN                   | ORD_ITEM_X01 |      1 |    102K|    103K|00:00:00.08 |   10688 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("M"."ITEM_ID"="I"."ITEM_ID")
   4 - access("I"."ORD_DT">=:1 AND "I"."ORD_DT"<=:2)

문제

DB 안에서는 0.4초인 내려받기가 사무실에서는 1분 넘게 걸리는 이유를 위 통계로 설명하시오. IDC 안 PC에서는 빠른 이유도 쓰시오.
내려받기 프로그램을 고치고, 고친 뒤 왕복 수와 논리 읽기가 어떻게 바뀌는지 숫자로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

// MonthItemExport.java : 이 문장만 한 번에 1,000행씩 받는다 (드라이버 기본값 10은 그대로 둔다)
String sql = "SELECT i.ORD_DT, i.ORD_HMS, i.ORD_NO, i.ITEM_ID, m.ITEM_NM, i.ORD_ITEM_QTY "
           + "FROM ORD_ITEM i, ITEM m "
           + "WHERE i.ORD_DT BETWEEN ? AND ? "
           + "AND m.ITEM_ID = i.ITEM_ID";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setFetchSize(1000);                      // 103,030행 → 페치 104번
ps.setString(1, fromDt);
ps.setString(2, toDt);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    sheet.addRow(rs.getString(1), rs.getString(2), rs.getLong(3), rs.getInt(4), rs.getString(5), rs.getInt(6));
}

개선 후 실행계획 학습용 예시 · 페치 수의 셈은 교재 09화 실측과 같다

xplan

[사무실 PC에서 한 번 · fetch size 1,000 (학습용 예시)]
  경과 시간 (도구 화면 기준)               3.9 초    (엑셀 쓰기 약 2.6초 포함)
  SQL*Net roundtrips to/from client          107
  session logical reads                    2,926
  SQL*Net message from client              106번 · 3.3 초 (왕복 0.7초 + 클라이언트가 시트를 쓰는 시간)
  SQL*Net more data to client              412번    (한 번에 보내는 양이 SDU보다 커서 나눠 보낸다)
  V$SQL  FETCHES 104 · BUFFER_GETS 2,909

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |    103K|00:00:00.21 |    2909 |
|*  1 |  HASH JOIN                            |              |      1 |    102K|    103K|00:00:00.21 |    2909 |
|   2 |   TABLE ACCESS FULL                   | ITEM         |      1 |     92 |     92 |00:00:00.01 |       5 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED | ORD_ITEM     |      1 |    102K|    103K|00:00:00.15 |    2904 |
|*  4 |    INDEX RANGE SCAN                   | ORD_ITEM_X01 |      1 |    102K|    103K|00:00:00.03 |     488 |
--------------------------------------------------------------------------------------------------------------

모범 답안 서술

V$SQL의 ROWS_PROCESSED 103,030을 FETCHES 10,304로 나누면 페치 한 번에 10행이다. 리포트 도구가 fetch size를 정하지 않아 JDBC 드라이버 기본값대로 10행씩 받았다. 페치 한 번이 왕복 한 번이므로 왕복이 10,306번 생겼다. DB 안에서 이 SQL이 쓴 시간은 0.41초뿐이고, 나머지는 DB가 다음 페치 요청을 기다린 SQL*Net message from client(71.6초)다. 사무실 PC는 VPN 너머라 왕복 한 번에 6.7ms가 들어, 10,305번이면 그것만 약 69초가 된다. IDC 안 PC는 왕복이 0.3ms라 같은 10,305번이 3초 남짓이어서 문제가 덜 드러났다. SQL과 실행계획이 아니라 몇 번 오가느냐가 시간을 정했다. 고친 판은 이 문장에만 setFetchSize(1000)을 준다. 페치는 10,304 → 104번, 왕복은 10,306 → 107번이 되고, 사무실에서 72.4초 → 3.9초(엑셀 쓰기 2.6초 포함)가 된다. 이 계획에는 정렬이 없어서 오라클은 읽으면서 바로 내준다(부분범위 처리). 10행을 내주고 멈췄다가 다음 페치에서 읽던 인덱스 리프 블록과 테이블 블록을 다시 잡으므로, 페치 한 번마다 블록을 두 개쯤 더 읽었다. 그래서 4번 줄 10,688, 0번 줄 23,301이던 Buffers가 488과 2,909로 준다. 정렬이 있는 계획이었다면 왕복만 줄고 Buffers는 그대로였을 것이다. fetch size를 무작정 키우지는 않는다. 클라이언트는 한 번에 받는 행만큼 메모리를 잡고, 1,000을 넘기면 왕복은 104번에서 거의 줄지 않는다. 드라이버 기본값을 바꾸면 다른 화면의 문장까지 영향을 받으므로 내려받기 문장에만 준다.

해설

클라이언트가 한 번에 받는 행 수는 rows ÷ Fetch로 읽는다. V$SQL의 ROWS_PROCESSED를 FETCHES로 나누거나, TKPROF Fetch 줄의 rows를 count로 나누면 된다(교재 15화 §4.2).
교재 09화의 엑셀 버튼은 SQL*Plus의 ARRAYSIZE 15로 27,390건을 받아 왕복이 1,827번이었다. JDBC에서 같은 역할을 하는 것이 fetch size이고 기본값은 10이다. ODP.NET은 FetchSize(바이트 단위), python-oracledb는 arraysize로 정한다.
함정: VPN 대역폭을 늘려도 거의 빨라지지 않는다. 보내는 양(6.4MB)이 아니라 왕복 횟수와 왕복 한 번의 지연이 문제이기 때문이다.
SQL*Net message from client는 Idle 대기라 ASH와 DB Time에 잡히지 않는다(교재 17화). DB 쪽 보고서만 보면 "DB는 0.4초"로 끝나므로, 세션 통계의 roundtrips와 클라이언트 쪽 시간을 함께 봐야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 페치 수의 셈은 교재 09화 실측과 같다 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 페치 수의 셈은 교재 09화 실측과 같다 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황