JOIN-019

화면은 0.01초인데 같은 SQL로 받는 엑셀 다운로드는 40초가 걸린다

실전SELECT한빛유통169 / 700

상황

운영팀의 「기간 주문 조회」 화면은 기간을 넣으면 최신 주문부터 50건을 보여 준다. 같은 화면의 「엑셀 다운로드」 버튼은 똑같은 SQL을 열고 끝까지 내려받는다. 화면은 하루 3만 번, 다운로드는 하루 40번쯤 호출된다. 한 달을 내려받으면 40초가 걸린다는 민원에, 지난주 한 개발자가 힌트를 USE_HASH와 FULL로 바꿨다. 다운로드는 6초가 됐지만 이번에는 화면이 누를 때마다 0.3초씩 걸려 상담원들이 불만이다. 아래는 힌트를 바꾸기 전, 2024년 6월로 조회한 두 경우의 실행계획이다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ) (09화에서 만든 인덱스) · 한 달 27,390건 (인덱스 리프 약 91블록) · 같은 날짜의 주문은 테이블 전체에 흩어져 있고, 한 달치 블록은 대부분 버퍼 캐시에 없다
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
애플리케이션은 한 번에 50행씩 가져온다 (fetch size 50) · 통계 최신

원본 SQL

sql

SELECT /*+ LEADING(o c) USE_NL(c) INDEX_DESC(o IX_ORD_BIG_DT_SEQ) */
       o.ORD_DT, o.ORD_SEQ, o.CUS_ID, c.CUS_NM, c.CUS_GD, o.ORD_ST, o.ORD_AMT
  FROM T_ORD_BIG o, M_CUS c
 WHERE o.ORD_DT >= :v_from
   AND o.ORD_DT <  :v_to
   AND c.CUS_ID = o.CUS_ID
 ORDER BY o.ORD_DT DESC, o.ORD_SEQ DESC

실행계획 학습용 예시

xplan

[화면: 첫 50행을 받고 커서를 닫았을 때]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |      1 |        |     50 |00:00:00.01 |     106 |     0 |
|*  1 |  FILTER                          |                   |      1 |        |     50 |00:00:00.01 |     106 |     0 |
|   2 |   NESTED LOOPS                   |                   |      1 |  27390 |     50 |00:00:00.01 |     106 |     0 |
|   3 |    NESTED LOOPS                  |                   |      1 |  27390 |     50 |00:00:00.01 |      56 |     0 |
|   4 |     TABLE ACCESS BY INDEX ROWID  | T_ORD_BIG         |      1 |  27390 |     50 |00:00:00.01 |      53 |     0 |
|*  5 |      INDEX RANGE SCAN DESCENDING | IX_ORD_BIG_DT_SEQ |      1 |  27390 |     50 |00:00:00.01 |       3 |     0 |
|*  6 |     INDEX UNIQUE SCAN            | PK_M_CUS          |     50 |      1 |     50 |00:00:00.01 |       3 |     0 |
|   7 |    TABLE ACCESS BY INDEX ROWID   | M_CUS             |     50 |      1 |     50 |00:00:00.01 |      50 |     0 |
------------------------------------------------------------------------------------------------------------------------

[다운로드: 같은 계획으로 27,390행을 끝까지 받았을 때]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |      1 |        |  27390 |00:00:38.21 |   55459 | 26982 |
|*  1 |  FILTER                          |                   |      1 |        |  27390 |00:00:38.21 |   55459 | 26982 |
|   2 |   NESTED LOOPS                   |                   |      1 |  27390 |  27390 |00:00:38.19 |   55459 | 26982 |
|   3 |    NESTED LOOPS                  |                   |      1 |  27390 |  27390 |00:00:37.52 |   28069 | 26982 |
|   4 |     TABLE ACCESS BY INDEX ROWID  | T_ORD_BIG         |      1 |  27390 |  27390 |00:00:37.43 |   27481 | 26982 |
|*  5 |      INDEX RANGE SCAN DESCENDING | IX_ORD_BIG_DT_SEQ |      1 |  27390 |  27390 |00:00:00.01 |      91 |     0 |
|*  6 |     INDEX UNIQUE SCAN            | PK_M_CUS          |  27390 |      1 |  27390 |00:00:00.05 |     588 |     0 |
|   7 |    TABLE ACCESS BY INDEX ROWID   | M_CUS             |  27390 |      1 |  27390 |00:00:00.58 |   27390 |     0 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:V_FROM<:V_TO)
   5 - access("O"."ORD_DT"<:V_TO AND "O"."ORD_DT">=:V_FROM)
   6 - access("C"."CUS_ID"="O"."CUS_ID")

문제

두 실행계획의 A-Rows와 Buffers, Reads를 비교해, 같은 계획이 화면에서는 빠르고 다운로드에서는 느린 이유를 부분범위 처리로 설명하시오.
화면과 다운로드를 모두 만족하도록 SQL을 고치시오(두 SQL로 나눠도 된다). 각각의 조인 방식과 예상 Buffers를 쓰시오.
하나의 SQL로 두 요구를 맞추려는 다른 방법들(FIRST_ROWS(n) 힌트, 커버링 인덱스, fetch size 늘리기)을 비교하고 부작용을 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- [화면] 앞 51건만: NL 과 인덱스 역순, STOPKEY 로 멈춘다 (51번째 행은 '더 보기' 버튼을 켤지 정하는 데 쓴다)
SELECT *
  FROM (SELECT /*+ LEADING(o c) USE_NL(c) INDEX_DESC(o IX_ORD_BIG_DT_SEQ) */
               o.ORD_DT, o.ORD_SEQ, o.CUS_ID, c.CUS_NM, c.CUS_GD, o.ORD_ST, o.ORD_AMT
          FROM T_ORD_BIG o, M_CUS c
         WHERE o.ORD_DT >= :v_from
           AND o.ORD_DT <  :v_to
           AND c.CUS_ID = o.CUS_ID
         ORDER BY o.ORD_DT DESC, o.ORD_SEQ DESC)
 WHERE ROWNUM <= 51;

-- [다운로드] 끝까지 읽는 SQL: M_CUS(30블록)를 build 로, T_ORD_BIG 은 FULL 로 한 번 읽는다
SELECT /*+ LEADING(c o) USE_HASH(o) FULL(o) */
       o.ORD_DT, o.ORD_SEQ, o.CUS_ID, c.CUS_NM, c.CUS_GD, o.ORD_ST, o.ORD_AMT
  FROM M_CUS c, T_ORD_BIG o
 WHERE o.ORD_DT >= :v_from
   AND o.ORD_DT <  :v_to
   AND c.CUS_ID = o.CUS_ID
 ORDER BY o.ORD_DT DESC, o.ORD_SEQ DESC;
-- 다운로드 프로그램의 fetch size 는 50 에서 1,000 으로 늘린다 (Fetch Call 548 번 → 28 번)

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

xplan

[다운로드용: 해시 조인]
-----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |  27390 |00:00:00.41 |    5856 |  5826 |
|*  1 |  FILTER               |           |      1 |        |  27390 |00:00:00.41 |    5856 |  5826 |
|   2 |   SORT ORDER BY       |           |      1 |  27390 |  27390 |00:00:00.41 |    5856 |  5826 |
|*  3 |    HASH JOIN          |           |      1 |  27390 |  27390 |00:00:00.35 |    5856 |  5826 |
|   4 |     TABLE ACCESS FULL | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |     0 |
|*  5 |     TABLE ACCESS FULL | T_ORD_BIG |      1 |  27390 |  27390 |00:00:00.32 |    5826 |  5826 |
-----------------------------------------------------------------------------------------------------

[화면용: STOPKEY]
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                   |      1 |        |     51 |00:00:00.01 |     108 |     0 |
|*  1 |  COUNT STOPKEY                     |                   |      1 |        |     51 |00:00:00.01 |     108 |     0 |
|   2 |   VIEW                             |                   |      1 |     51 |     51 |00:00:00.01 |     108 |     0 |
|*  3 |    FILTER                          |                   |      1 |        |     51 |00:00:00.01 |     108 |     0 |
|   4 |     NESTED LOOPS                   |                   |      1 |     51 |     51 |00:00:00.01 |     108 |     0 |
|   5 |      NESTED LOOPS                  |                   |      1 |     51 |     51 |00:00:00.01 |      57 |     0 |
|   6 |       TABLE ACCESS BY INDEX ROWID  | T_ORD_BIG         |      1 |  27390 |     51 |00:00:00.01 |      54 |     0 |
|*  7 |        INDEX RANGE SCAN DESCENDING | IX_ORD_BIG_DT_SEQ |      1 |     51 |     51 |00:00:00.01 |       3 |     0 |
|*  8 |       INDEX UNIQUE SCAN            | PK_M_CUS          |     51 |      1 |     51 |00:00:00.01 |       3 |     0 |
|   9 |      TABLE ACCESS BY INDEX ROWID   | M_CUS             |     51 |      1 |     51 |00:00:00.01 |      51 |     0 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  [다운로드] 1 - filter(:V_FROM<:V_TO)
             3 - access("C"."CUS_ID"="O"."CUS_ID")
             5 - filter(("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<:V_TO))
  [화면]     1 - filter(ROWNUM<=51)
             7 - access("O"."ORD_DT"<:V_TO AND "O"."ORD_DT">=:V_FROM)
             8 - access("C"."CUS_ID"="O"."CUS_ID")

모범 답안 서술

화면이 빠른 이유: 계획에 SORT가 없다. IX_ORD_BIG_DT_SEQ를 거꾸로 읽으면 이미 ORD_DT DESC, ORD_SEQ DESC 순서라 인덱스가 정렬을 대신하고, NL 조인은 주문 한 건을 읽을 때마다 고객명을 붙여 바로 내보낸다. 화면이 50행을 받고 커서를 닫으면 SQL도 거기서 멈춘다. A-Rows 50, Buffers 106이다. 이것이 부분범위 처리다. 다운로드가 느린 이유: 같은 계획으로 27,390행을 끝까지 받으면 4번 줄에서 주문 27,390건을 테이블 랜덤 액세스로 한 건씩 읽는다. 같은 날짜의 주문이 흩어져 있어 거의 매번 다른 블록이고, 그 블록 대부분이 캐시에 없어서 Reads가 26,982다. 싱글 블록 물리 읽기 2만 7천 번이 38초의 거의 전부다. 고객도 27,390번 찾아가 Buffers는 55,459가 된다. 해시로 바꾸면: T_ORD_BIG을 FULL로 읽으면 5,826블록을 멀티블록 읽기로 한 번에 훑으므로 다운로드는 0.4초 남짓이 된다. 하지만 화면도 같은 SQL이라 해시 조인과 정렬이 끝나야 첫 행이 나온다. 누를 때마다 5,856블록이고 하루 3만 번이면 약 1억 7,568만 블록이다. NL이면 약 318만 블록이다. 해법: 두 SQL로 나눈다. 화면 SQL은 ROWNUM <= 51과 NL, 인덱스 역순으로 51행에서 멈춘다(108블록). 다운로드 SQL은 M_CUS(30블록)를 build로 삼고 T_ORD_BIG을 FULL로 읽는 해시 조인이다(5,856블록, Reads 5,826을 멀티블록으로 읽는다). 다운로드 프로그램의 fetch size도 50 → 1,000으로 늘려 Fetch Call을 548번에서 28번으로 줄인다. 다른 방법들: FIRST_ROWS(n) 힌트는 앞의 n행을 빨리 내는 계획을 고르라는 뜻이라 화면에는 맞지만, 같은 SQL을 쓰는 다운로드는 다시 NL로 돌아가 느려진다. 한 SQL로 두 성격을 다 맞출 수는 없다. 커버링 인덱스 (ORD_DT, ORD_SEQ, CUS_ID, ORD_ST, ORD_AMT)를 만들면 다운로드의 테이블 랜덤 액세스는 사라지지만, 100만 행 테이블에 큰 인덱스가 하나 더 생겨 적재마다 갱신 비용이 들고, 고객 27,390번 찾아가기는 그대로 남는다. fetch size를 늘리는 것은 네트워크 왕복만 줄일 뿐 랜덤 액세스 2만 7천 번은 줄이지 못한다.

해설

SQL의 성격이 둘이다: 앞의 몇 행만 보는 SQL(화면)과 끝까지 읽는 SQL(다운로드, 배치)은 맞는 조인 방식이 다르다. 한 SQL을 두 용도로 쓰면 어느 한쪽은 손해를 본다(교재 09화 §1, 06화 §1).
ROWNUM을 꼭 넣는다: 화면 SQL에 STOPKEY가 없으면 옵티마이저는 전체 결과를 기준으로 비용을 계산해 해시 조인을 고를 수 있다. ROWNUM <= 51(또는 FETCH FIRST 51 ROWS ONLY)을 쓰면 앞의 51행을 기준으로 계획을 세운다.
다음 페이지: '더 보기'는 마지막 행의 (ORD_DT, ORD_SEQ) 다음부터 읽는 키 기반 페이징으로 이어 간다. 정렬 키 끝에 ORD_SEQ가 있어 행이 빠지거나 겹치지 않는다(교재 09화 §9).
물리 읽기와 논리 읽기: Buffers만 보면 다운로드는 55,459 → 5,856으로 9배다. 시간은 38초 → 0.4초로 훨씬 더 크게 준다. 랜덤 싱글 블록 읽기와 멀티블록 읽기의 차이는 Reads와 A-Time을 함께 봐야 보인다(교재 04화 §2).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황