엑셀 내려받기가 먼저 돈 날은 최근 주문 목록 첫 페이지가 느리다

기초SELECT한빛유통563 / 700

상황

정산팀의 「최근 주문」 화면은 최신 주문 20건을 보여 주고, 같은 화면의 「엑셀 내려받기」 버튼은 최근 5만 건을 파일로 내린다. 개발팀은 두 기능이 같은 DAO 메서드를 쓰게 하고 건수만 인자로 넘겼다(화면 20, 엑셀 50,000). 화면은 하루 2만 번쯤 호출된다. 조민우가 말했다. "어제는 첫 페이지가 바로 떴는데 오늘은 한 박자 늦어요. 정산팀이 아침 8시 52분에 엑셀부터 내려받았다고 하더라고요." 밤사이 통계를 다시 모아서 이 문장의 커서는 오늘 아침에 새로 만들어졌다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 행은 ORD_DT 순서와 상관없이 흩어져 있다(인덱스로 1,000건을 읽으면 테이블 블록도 1,000개 가까이 읽는다)
인덱스 IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ): 높이 3 · 리프 약 3,000블록
통계 최신 · 히스토그램은 ORD_ST에만 있다 · CURSOR_SHARING = EXACT · 바인드 피킹 켬

원본 SQL

sql

// OrderDao.java : 화면과 엑셀이 같은 메서드를 쓴다 (화면 n = 20, 엑셀 n = 50000)
public List<OrderRow> recent(int n) throws SQLException {
    String sql = "SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT "
               + "FROM (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD_BIG "
               + "ORDER BY ORD_DT DESC, ORD_SEQ DESC) WHERE ROWNUM <= ?";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setInt(1, n);
        return readRows(ps.executeQuery());
    }
}

실행계획 학습용 예시

xplan

[V$SQL — 오늘 09:30 · 커서는 08:52에 새로 만들어졌다]
 CHILD  PLAN_HASH    EXECUTIONS  BUFFER_GETS/EXEC  FIRST_LOAD_TIME
     0  2860115731        3,412             5,826  2024-06-17/08:52:07

[자식 0 · 화면(n = 20) 한 번 · FORMAT 'ALLSTATS LAST +PEEKED_BINDS']
Plan hash value: 2860115731

----------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      1 |        |     20 |00:00:00.21 |    5826 |          |
|*  1 |  COUNT STOPKEY           |           |      1 |        |     20 |00:00:00.21 |    5826 |          |
|   2 |   VIEW                   |           |      1 |  50000 |     20 |00:00:00.21 |    5826 |          |
|*  3 |    SORT ORDER BY STOPKEY |           |      1 |   1000K|     20 |00:00:00.21 |    5826 | 4096  (0)|
|   4 |     TABLE ACCESS FULL    | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5826 |          |
----------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (NUMBER): 50000

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=:1)
   3 - filter(ROWNUM<=:1)

[어제 — 같은 문장 · 커서는 07:40에 화면(n = 20)이 처음 불렀다]
 PLAN_HASH 1127394853 · INDEX FULL SCAN DESCENDING (IX_ORD_BIG_DT_SEQ) + TABLE ACCESS BY INDEX ROWID
 화면 한 번 24블록 · 엑셀 한 번 50,158블록 (A-Rows 50,000)

문제

화면이 20건만 달라고 하는데도 100만 행을 정렬하는 계획을 타는 이유를 위 자료로 설명하시오. 어제는 거꾸로 엑셀이 손해를 봤다는 점도 설명하시오.
누가 먼저 오든 화면과 엑셀이 각자 맞는 계획을 타도록 코드와 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

// 화면: 한 페이지 20건은 화면이 정한 상수다. 문장에 그대로 쓴다 (값이 하나라 커서도 하나)
public List<OrderRow> recentPage() throws SQLException {
    String sql = "SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT "
               + "FROM (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD_BIG "
               + "ORDER BY ORD_DT DESC, ORD_SEQ DESC) WHERE ROWNUM <= 20";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        return readRows(ps.executeQuery());
    }
}

// 엑셀: 5만 건 내려받기는 다른 일이다. 문장을 따로 둔다 (FULL + 정렬이 맞는 일)
public void exportRecent(RowWriter out) throws SQLException {
    String sql = "SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT "
               + "FROM (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD_BIG "
               + "ORDER BY ORD_DT DESC, ORD_SEQ DESC) WHERE ROWNUM <= 50000";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setFetchSize(1000);
        out.writeAll(ps.executeQuery());
    }
}

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

xplan

[화면 문장 — ROWNUM <= 20 · 누가 먼저 오든 이 계획]
Plan hash value: 1127394853

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |     20 |00:00:00.01 |      24 |
|*  1 |  COUNT STOPKEY                |                   |      1 |        |     20 |00:00:00.01 |      24 |
|   2 |   VIEW                        |                   |      1 |     20 |     20 |00:00:00.01 |      24 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_ORD_BIG         |      1 |   1000K|     20 |00:00:00.01 |      24 |
|   4 |     INDEX FULL SCAN DESCENDING| IX_ORD_BIG_DT_SEQ |      1 |     20 |     20 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)

[엑셀 문장 — ROWNUM <= 50000 · 자기 문장 · 자기 계획]
 PLAN_HASH 2860115731 · TABLE ACCESS FULL + SORT ORDER BY STOPKEY · 5,826블록 (인덱스로 읽던 50,158블록 대신)

모범 답안 서술

자식 0은 아침 8시 52분에 엑셀이 처음 불러 만든 커서다. 오라클은 하드 파싱할 때 바인드 값 50,000을 엿보고(바인드 피킹, Peeked Binds의 :1 = 50000) "5만 건을 정렬해서 내보내는 일"로 계획을 세웠다. 2번 줄의 E-Rows가 50,000인 것이 그 흔적이다. 5만 건이면 인덱스를 거꾸로 따라가며 테이블을 5만 번 찾아가는 것보다 테이블을 한 번 다 읽고(5,826블록) 정렬하는 편이 싸다. 그 뒤로 화면이 20건을 달라고 와도 같은 커서를 쓰므로, 20건을 위해 100만 행을 읽고 정렬한다. 화면 한 번이 5,826블록이고 하루 2만 번이면 1억 블록이 넘는다. 어제는 순서가 반대였다. 화면(20)이 먼저 파싱해서 인덱스를 거꾸로 읽다 멈추는 계획이 섰고, 엑셀은 그 계획으로 5만 건을 하나씩 찾아가 50,158블록을 읽었다. 히스토그램과 상관없이 ROWNUM <= :n의 n 자체가 계획을 가른다. 그날 처음 온 값이 그날의 계획을 정한다. 한 페이지 20건은 사용자가 고르는 값이 아니라 화면이 정한 상수다. 그래서 화면 문장에는 ROWNUM <= 20을 그대로 쓴다. 값이 하나뿐이니 커서도 하나이고, 하드 파싱이 늘지 않는다. 엑셀 내려받기는 다른 일이므로 문장을 따로 나눈다. 두 문장은 텍스트가 달라 부모 커서도 다르고, 각자 맞는 계획을 탄다. 화면은 Buffers 5,826 → 24, 엑셀은 어느 날이든 5,826(FULL + 정렬)이다.

해설

FETCH FIRST :n ROWS ONLY로 바꿔도 같다. 오라클은 이를 ROW_NUMBER() 필터로 풀고 n을 엿봐서 계획을 세운다. 바인드 자리가 남아 있는 한 첫 값의 함정도 남는다.
화면이 페이지 크기를 고르게 한다면(20, 50, 100) 값마다 문장을 두어도 셋뿐이다. 100건까지는 인덱스 계획이 맞으므로 :n으로 둘 수도 있지만, 그러려면 상한을 서버 코드에서 막아야 한다.
커서가 새로 만들어지는 때(통계 재수집, 공유 풀에서 밀려남)마다 "오늘의 첫 손님"이 바뀐다. 그래서 증상이 날마다 달라 보인다.
엑셀 쪽 문장에 /*+ FULL(T_ORD_BIG) */를 붙여 두면 통계가 바뀌어도 의도가 문장에 남는다. 다만 힌트도 그때의 규모를 보고 내린 판단이라 유효기간을 적어 둔다(교재 06화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황