판매 전표 조회 화면에서 1년을 고르면 첫 페이지 50건이 뜨기까지 8초가 걸린다

중급PLSQL유통686 / 700

상황

본사 「판매 전표 조회」 화면(Java)은 기간을 고르면 프로시저 P_SLE_LIST를 부르고, 받은 전표를 최근 것부터 한 페이지에 50건씩 보여 준다. 화면 로그를 보면 조회의 95%가 첫 페이지만 보고 닫힌다. 프로시저는 기간의 전표를 컬렉션에 담아 OUT 인자로 돌려준다. 한 달을 고르면 1초, 1년을 고르면 첫 페이지가 뜨기까지 8초 가까이 걸린다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(블록당 약 109행) · SLE_DT VARCHAR2(8) · 날짜순으로 쌓였다 · 2023년 249,971건
인덱스 IX_CF10_DT_NO (SLE_DT, SLE_NO): 이 문제에서는 있다 · 리프 2,380블록 · 통계 최신
SQL 타입 T_SLE_OBJ (SLE_NO, SLE_DT, SLE_HMS, SHP_NO)와 T_SLE_TAB(TABLE OF T_SLE_OBJ)

원본 SQL

sql

PROCEDURE P_SLE_LIST(p_fr IN VARCHAR2, p_to IN VARCHAR2, o_list OUT T_SLE_TAB) IS
BEGIN
  SELECT T_SLE_OBJ(SLE_NO, SLE_DT, SLE_HMS, SHP_NO)
    BULK COLLECT INTO o_list
    FROM CF10
   WHERE SLE_DT BETWEEN p_fr AND p_to
   ORDER BY SLE_DT DESC, SLE_NO DESC;
END;

실행계획 학습용 예시

xplan

[프로시저 안 SQL: DISPLAY_CURSOR (ALLSTATS LAST) · 2023년]
---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    249K|00:00:01.38 |    4583 |          |
|   1 |  SORT ORDER BY     |      |      1 |    250K|    249K|00:00:01.38 |    4583 |   17M (0)|
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |    250K|    249K|00:00:00.09 |    4583 |          |
---------------------------------------------------------------------------------------------------
   2 - filter(("SLE_DT">=:B2 AND "SLE_DT"<=:B1))

[화면 한 번 · 2023년]
  프로시저 실행                    1.9 초 (SQL 1.38 초 + 컬렉션 채우기)
  OUT 컬렉션 전송과 Java 변환      5.9 초 · bytes sent via SQL*Net to client 21.4 MB
  session pga memory max           96 MB
  화면에 보인 행                   50

문제

첫 페이지 50건을 보여 주려고 서버와 화면이 한 일을 위 숫자로 설명하시오.
화면이 첫 페이지를 바로 띄우도록 프로시저를 고치시오. 화면(Java) 쪽에서 지켜야 할 것도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 결과를 담은 컬렉션 대신 결과를 가리키는 커서를 넘긴다: 화면이 가져가는 만큼만 읽는다
PROCEDURE P_SLE_LIST(p_fr IN VARCHAR2, p_to IN VARCHAR2, o_rc OUT SYS_REFCURSOR) IS
BEGIN
  OPEN o_rc FOR
    SELECT /*+ FIRST_ROWS(50) */ SLE_NO, SLE_DT, SLE_HMS, SHP_NO
      FROM CF10
     WHERE SLE_DT BETWEEN p_fr AND p_to
     ORDER BY SLE_DT DESC, SLE_NO DESC;      -- IX_CF10_DT_NO 의 역순: 정렬 없이 앞에서 멈춘다
END;

-- 화면(Java): cs.registerOutParameter(3, OracleTypes.CURSOR) → rs.setFetchSize(50)
--             → 50건을 읽고 rs.close(), cs.close() (try-with-resources 로 반드시 닫는다)

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

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |     50 |00:00:00.01 |       6 |
|   1 |  TABLE ACCESS BY INDEX ROWID | CF10          |      1 |     51 |     50 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IX_CF10_DT_NO |      1 |     51 |     50 |00:00:00.01 |       4 |
-------------------------------------------------------------------------------------------------------
   2 - access("SLE_DT">=:B2 AND "SLE_DT"<=:B1)

[화면 한 번 · 2023년]  첫 페이지까지 0.02 초 · bytes sent 4.6 KB · session pga memory max 1.3 MB
                       (화면이 50건을 받고 커서를 닫았다)

모범 답안 서술

첫 페이지 50건을 보여 주려고 서버는 1년치 249,971행을 모두 읽고(FULL 4,583블록) 17MB를 써서 정렬했고, 그 25만 행을 객체로 만들어 컬렉션에 담았다(PGA 96MB). 프로시저가 끝나야 OUT 컬렉션이 화면으로 넘어가므로, 21.4MB를 보내고 Java가 25만 개의 객체로 바꾼 다음에야 첫 페이지가 떴다(1.9 + 5.9초). 화면에 보인 것은 50건이고, 나머지 249,921행은 버려졌다. 컬렉션은 결과를 다 담아야 넘길 수 있는 그릇이라서, 화면이 몇 건을 보든 일은 늘 전부다. 프로시저가 컬렉션 대신 SYS_REFCURSOR를 열어 돌려주면, 화면에 넘어가는 것은 결과가 아니라 결과를 가리키는 커서다. 화면이 첫 페치로 50건을 가져가면 서버도 50건만큼만 읽고 멈추고, 화면이 커서를 닫으면 나머지는 읽지 않는다. 부분범위 처리다. 그러려면 50건을 읽는 데 정렬이 필요 없어야 한다. 정렬 순서(SLE_DT DESC, SLE_NO DESC)가 IX_CF10_DT_NO의 역순과 같으므로, 인덱스를 거꾸로(INDEX RANGE SCAN DESCENDING) 읽으면 정렬 없이 순서대로 나온다. 다만 옵티마이저는 기본(ALL_ROWS)으로는 25만 행을 다 가져갈 것으로 보고 FULL과 정렬을 고를 수 있으므로, FIRST_ROWS(50)으로 앞의 50건을 빨리 주는 계획을 고르게 한다. Buffers는 4,583 → 6, PGA는 96MB → 1.3MB, 첫 페이지는 8초 가까이에서 0.02초가 된다. 화면 쪽도 지켜야 할 것이 있다. 페치 크기를 페이지에 맞춰(setFetchSize(50)) 첫 페치에서 한 페이지를 받고, 다 쓴 커서(ResultSet)와 문장을 반드시 닫는다. 닫지 않으면 세션에 열린 커서가 쌓이다가 ORA-01000으로 끊긴다.

해설

다음 페이지: 웹 화면은 요청마다 커서를 새로 연다. 2페이지는 앞 페이지 마지막 행의 (SLE_DT, SLE_NO) 뒤부터 읽는 키 기반 페이징으로 이어 가면 몇 페이지든 일량이 같다(교재 09화).
커서만 바꾸면 모자라다: FIRST_ROWS 없이 FULL과 정렬이 골라지면, REF CURSOR로 바꿔도 첫 페치 전에 25만 행 정렬이 끝나야 한다. 커서로 넘기는 것과 정렬을 없애는 것이 함께 있어야 첫 페이지가 바로 뜬다.
전부 내려받는 화면도 REF CURSOR가 낫다. 결과가 서버 PGA에 한꺼번에 쌓이지 않고 페치 크기만큼씩 흘러간다. 이때는 페치 크기를 크게(500 ~ 1,000) 잡는다.
시간을 재는 곳: REF CURSOR의 일은 프로시저가 끝난 뒤 화면이 페치할 때 일어난다. 프로시저 실행 시간만 재면 늘 0.01초로 보이므로, 화면 쪽 시간과 그 SQL의 V$SQL을 함께 본다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황