엑셀 내려받기를 고친 다음 날부터 고객센터 주문 이력 화면이 굼뜨고 WAS 힙이 두 배가 됐다

실전SELECT한빛유통578 / 700

상황

지난주 정산팀이 엑셀 내려받기가 느리다고 하자, 개발팀은 커넥션 풀 설정에 defaultRowPrefetch = 5000을 넣었다. 드라이버 기본값(10)을 모든 문장에 대해 바꾼 것이다. 내려받기는 1.9초 → 0.3초가 됐다. 다음 날부터 고객센터 「고객 주문 이력」 화면이 0.01초에서 0.3초 안팎으로 느려졌고, 화면 서버(WAS)의 힙 사용량이 늘어 GC가 잦아졌다. 상담원은 주문 이력의 첫 화면(20건)만 보고 닫는 일이 대부분이다. 박팀장이 문장마다 fetch size를 어떻게 둘지 정하고, 무엇을 잃는지도 적어 달라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 고객 2,000명이라 고객 한 명의 주문은 약 500건 · 같은 고객의 주문은 테이블 곳곳에 흩어져 있다(클러스터링 팩터가 나쁘다)
인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT)(이 문제에만 있다) · IX_ORD_BIG_DT (ORD_DT) · 통계 최신
T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ)
WAS와 DB는 같은 IDC에 있다(왕복 0.5ms). 고객센터가 여는 고객은 그때그때 달라서 그 고객의 주문 블록은 버퍼 캐시에 없을 때가 많다.

원본 SQL

sql

// DataSourceConfig.java (지난주 반영) : 커넥션 풀의 모든 연결에 건다
props.setProperty("defaultRowPrefetch", "5000");

-- [S1] 정산팀 엑셀 내려받기 : 한 달 치 전부 (6월 27,390건)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= :d_from AND ORD_DT < :d_to
 ORDER BY ORD_DT, ORD_SEQ;

-- [S2] 고객센터 고객 주문 이력 : 화면은 앞 20건을 보여 주고, 상담원이 닫으면 ResultSet을 닫는다
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE CUS_ID = :cus
 ORDER BY ORD_DT DESC;

-- [S3] 주문 한 건 상세 : PK 단건
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, PAY_TP, ORD_AMT, PAY_AMT
  FROM T_ORD
 WHERE ORD_SEQ = :seq;

실행계획 학습용 예시 · [S1]의 왕복 수는 교재 09화 실측과 같다

xplan

[S2 · 같은 고객 (CUS_000731) · DISPLAY_CURSOR 'ALLSTATS LAST']

(지난주까지 · fetch size 10 · 화면이 20건을 읽고 닫음)
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |     20 |00:00:00.01 |      24 |     19 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T_ORD_BIG         |      1 |    500 |     20 |00:00:00.01 |      24 |     19 |
|*  2 |   INDEX RANGE SCAN DESCENDING | IX_ORD_BIG_CUS_DT |      1 |    500 |     20 |00:00:00.01 |       4 |      1 |
----------------------------------------------------------------------------------------------------------------------

(지금 · defaultRowPrefetch 5000 · 화면은 똑같이 20건만 쓰고 닫음)
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |    500 |00:00:00.29 |     506 |    471 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T_ORD_BIG         |      1 |    500 |    500 |00:00:00.29 |     506 |    471 |
|*  2 |   INDEX RANGE SCAN DESCENDING | IX_ORD_BIG_CUS_DT |      1 |    500 |    500 |00:00:00.01 |       6 |      2 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUS_ID"=:CUS)

[S1 · 엑셀 내려받기 · FULL + SORT ORDER BY · Used-Mem 1.9M]
  fetch size     페치   SQL*Net roundtrips   Buffers    경과
      10        2,740         2,741           5,826     1.9 초
    5000            6             7           5,826     0.3 초
    (1000으로 재 보면)  28       29           5,826     0.3 초

[S3 · 주문 한 건]  Buffers 3 · 페치 1 · 설정과 상관없이 같다

[WAS · 지난주 대비]  힙 사용량 1.1GB → 2.3GB · GC 시간당 40번 → 310번

문제

모든 문장의 fetch size를 5000으로 바꾼 뒤 [S1]은 빨라지고 [S2]는 느려진 이유를 위 계획과 숫자로 설명하시오.
[S1] ~ [S3]마다 fetch size를 어떻게 둘지 정하고, [S2]는 설정과 상관없이 20건만 읽도록 SQL을 고치시오.
고른 안의 대가를 쓰고, 전역 설정을 5000으로 두는 안과 견주어 무엇을 잃는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

// DataSourceConfig.java : 전역 설정 줄(defaultRowPrefetch 5000)을 지워 드라이버 기본값 10으로 되돌린다

// [S1] 내려받기 문장에만 1,000 : 왕복 2,741 → 29 (5000이면 7, 차이는 0.01초 안팎)
PreparedStatement ps1 = conn.prepareStatement(SQL_S1);
ps1.setFetchSize(1000);

// [S2] 화면이 쓰는 20건을 SQL에 적고, 한 번에 받도록 fetch size도 20
PreparedStatement ps2 = conn.prepareStatement(SQL_S2);
ps2.setFetchSize(20);

// [S3] 손대지 않는다 (한 건)

-- [S2] 고친 문장 : 설정이 무엇이든 20건에서 멈춘다
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE CUS_ID = :cus
 ORDER BY ORD_DT DESC
 FETCH FIRST 20 ROWS ONLY;

개선 후 실행계획 학습용 예시 · [S1]의 왕복 수는 교재 09화 실측과 같다

xplan

[S2 · 고친 문장 · fetch size가 무엇이든 같은 모양]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |      1 |        |     20 |00:00:00.01 |      24 |     19 |
|*  1 |  VIEW                           |                   |      1 |     20 |     20 |00:00:00.01 |      24 |     19 |
|*  2 |   WINDOW NOSORT STOPKEY         |                   |      1 |    500 |     20 |00:00:00.01 |      24 |     19 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | T_ORD_BIG         |      1 |    500 |     21 |00:00:00.01 |      24 |     19 |
|*  4 |     INDEX RANGE SCAN DESCENDING | IX_ORD_BIG_CUS_DT |      1 |    500 |     21 |00:00:00.01 |       3 |      1 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_ORD_BIG"."ORD_DT") DESC )<=20)
   4 - access("CUS_ID"=:CUS)

[S1 · setFetchSize(1000)]  페치 28 · roundtrips 29 · Buffers 5,826 · 0.3 초
[S2]  왕복 1번(실행과 첫 페치를 한 번에) · Buffers 506 → 24 · Reads 471 → 19 · 0.29 → 0.01 초
[WAS]  힙 사용량 2.3GB → 1.1GB 언저리 · GC 시간당 310번 → 45번 (학습용 예시)

모범 답안 서술

[S2]가 느려진 이유: [S2]의 계획에는 정렬이 없다. IX_ORD_BIG_CUS_DT를 거꾸로 읽으면 이미 ORD_DT DESC 순서라서, 오라클은 읽는 대로 행을 내준다(부분범위 처리). fetch size가 10일 때는 첫 페치 10행과 두 번째 페치 10행을 만들고 화면이 커서를 닫았으므로 20행만 읽었다(Buffers 24, Reads 19). 5000이 되자 첫 페치가 5,000행을 채우려고 그 고객의 주문 500건을 모두 만든다. 화면은 여전히 20건만 쓰지만 DB는 500건을 읽어 보내고, 흩어진 테이블 블록을 디스크에서 읽느라 Buffers 24 → 506, Reads 19 → 471, 0.01초 → 0.29초가 됐다. WAS도 문장마다 받은 행을 들고 있으므로 힙이 늘었다. [S1]이 빨라진 이유: [S1]은 FULL 뒤에 정렬(SORT ORDER BY)이 있다. 정렬은 첫 행을 내주기 전에 27,390건을 모두 읽어 정렬을 끝내므로 fetch size와 상관없이 Buffers는 5,826 그대로다. 바뀐 것은 왕복뿐이다(2,741 → 7). 문장마다 정한다: 전역 설정 줄을 지워 드라이버 기본값 10으로 되돌리고, 많이 받는 문장에만 따로 준다. [S1]은 setFetchSize(1000)이다. 왕복은 29번으로 5000의 7번과 0.01초 안팎 차이인데, 클라이언트가 한 번에 들고 있는 행은 5분의 1이다. [S2]는 화면이 쓰는 20건을 SQL에 FETCH FIRST 20 ROWS ONLY로 적는다. 계획에 WINDOW NOSORT STOPKEY가 생겨 20건에서 멈추므로, 누가 fetch size를 어떻게 바꾸든 Buffers는 24다. fetch size도 20으로 주어 실행과 첫 페치를 한 번의 왕복에 끝낸다. [S3]은 한 건이라 손대지 않는다. 대가와 비교: [S1]을 1,000으로 두면 5000보다 왕복이 22번 많다(0.5ms씩 약 0.01초). [S2]에 행 수를 적으면 '더 보기'는 다음 20건을 묻는 문장을 따로 둬야 한다. 전역 5000은 설정 한 줄로 끝나는 대신 모든 문장에 걸린다. 화면이 몇 건을 쓰는지 모르는 DB는 정렬 없는 문장마다 5,000행까지 미리 만들어 보내고, WAS는 그만큼 메모리를 잡는다. 몇 건만 쓰는 화면은 그 사실을 SQL에 적어야 DB가 멈출 수 있다.

해설

부분범위 처리는 양날이다. 정렬이 없는 계획은 fetch size만큼만 읽고 멈출 수 있다. 바꿔 말하면 fetch size를 키우면 그만큼 더 읽는다(교재 09화 §11).
ROWNUM <= 20도 같은 답이다(COUNT STOPKEY). ORDER BY는 인라인 뷰 안에 두고 바깥에서 ROWNUM을 건다.
'더 보기'를 키 기반 페이징으로 하려면 정렬 키가 유일해야 한다. ORD_DT만으로는 같은 날 주문이 경계에서 겹치므로 인덱스를 (CUS_ID, ORD_DT, ORD_SEQ)로 만들고 ORD_SEQ까지 정렬한다(교재 09화 포스트잇).
연결의 기본값(defaultRowPrefetch)보다 문장에 준 setFetchSize가 우선한다. 그래서 내려받기처럼 많이 받는 문장만 골라 주는 쪽이 안전하다.
[S1]은 fetch size로 Buffers를 줄일 수 없다. 정렬을 없애려고 6월 27,390건을 IX_ORD_BIG_DT로 읽으면 행마다 흩어진 테이블 블록을 찾아가므로 FULL 5,826보다 비싸다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · [S1]의 왕복 수는 교재 09화 실측과 같다 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · [S1]의 왕복 수는 교재 09화 실측과 같다 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황