콜센터 고객 화면을 열 때마다 TEMP에 주문 33만 건이 쓰였다 지워진다

중급SELECT한빛유통297 / 700

상황

콜센터 「고객 상담」 화면은 위쪽에 고객의 올해 주문 요약(건수, 금액)을, 아래쪽에 올해 최근 주문 10건을 보여 준다. 개발자는 두 부분을 SQL 하나로 받으려고 공통 부분을 WITH 절로 빼 두었고, 요약은 10건의 줄마다 붙어 나오게 했다. 화면은 하루 4,000번 열리고 한 번에 0.7초가 걸린다. DBA는 이 화면이 열릴 때마다 TEMP 사용량이 16MB씩 올라갔다 내려간다고 알려 왔다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 고객마다 500건(2,000행 간격으로 흩어져 있다) · 2024년 333,245건(고객당 약 167건)
인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT): 이미 있다 · BLEVEL 2
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
통계 최신 · 아래는 고객 CUS_000123으로 연 화면의 실행계획이다

원본 SQL

sql

WITH v AS (
  SELECT o.CUS_ID, o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.ORD_AMT, c.CUS_NM, c.CUS_GD
    FROM T_ORD_BIG o, M_CUS c
   WHERE c.CUS_ID = o.CUS_ID
     AND o.ORD_DT >= DATE '2024-01-01'
     AND o.ORD_DT <  DATE '2025-01-01'
)
SELECT s.CUS_NM, s.CUS_GD, s.ORD_CNT, s.ORD_AMT,
       d.ORD_SEQ, d.ORD_DT, d.ORD_ST, d.ORD_AMT AS AMT
  FROM (SELECT MAX(CUS_NM) AS CUS_NM, MAX(CUS_GD) AS CUS_GD,
               COUNT(*) AS ORD_CNT, SUM(ORD_AMT) AS ORD_AMT
          FROM v
         WHERE CUS_ID = :cus) s,
       (SELECT *
          FROM (SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
                  FROM v
                 WHERE CUS_ID = :cus
                 ORDER BY ORD_DT DESC, ORD_SEQ DESC)
         WHERE ROWNUM <= 10) d

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Reads | Writes |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                           |      1 |        |     10 | 00:00:00.74 |    9969 |  4072 |   2036 |
|   1 |  TEMP TABLE TRANSFORMATION                 |                           |      1 |        |     10 | 00:00:00.74 |    9969 |  4072 |   2036 |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)  | SYS_TEMP_0FD9D6624_2A1B3C |      1 |        |      0 | 00:00:00.49 |    5889 |     0 |   2036 |
|*  3 |    HASH JOIN                               |                           |      1 |   333K |   333K | 00:00:00.30 |    5856 |     0 |      0 |
|   4 |     TABLE ACCESS FULL                      | M_CUS                     |      1 |   2000 |   2000 | 00:00:00.01 |      30 |     0 |      0 |
|*  5 |     TABLE ACCESS FULL                      | T_ORD_BIG                 |      1 |   333K |   333K | 00:00:00.09 |    5826 |     0 |      0 |
|   6 |   MERGE JOIN CARTESIAN                     |                           |      1 |     10 |     10 | 00:00:00.25 |    4080 |  4072 |      0 |
|   7 |    VIEW                                    |                           |      1 |      1 |      1 | 00:00:00.12 |    2040 |  2036 |      0 |
|   8 |     SORT AGGREGATE                         |                           |      1 |      1 |      1 | 00:00:00.12 |    2040 |  2036 |      0 |
|*  9 |      VIEW                                  |                           |      1 |   333K |    167 | 00:00:00.12 |    2040 |  2036 |      0 |
|  10 |       TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6624_2A1B3C |      1 |   333K |   333K | 00:00:00.08 |    2040 |  2036 |      0 |
|  11 |    BUFFER SORT                             |                           |      1 |     10 |     10 | 00:00:00.13 |    2040 |  2036 |      0 |
|  12 |     VIEW                                   |                           |      1 |     10 |     10 | 00:00:00.13 |    2040 |  2036 |      0 |
|* 13 |      COUNT STOPKEY                         |                           |      1 |        |     10 | 00:00:00.13 |    2040 |  2036 |      0 |
|  14 |       VIEW                                 |                           |      1 |   333K |     10 | 00:00:00.13 |    2040 |  2036 |      0 |
|* 15 |        SORT ORDER BY STOPKEY               |                           |      1 |   333K |     10 | 00:00:00.13 |    2040 |  2036 |      0 |
|* 16 |         VIEW                               |                           |      1 |   333K |    167 | 00:00:00.12 |    2040 |  2036 |      0 |
|  17 |          TABLE ACCESS FULL                 | SYS_TEMP_0FD9D6624_2A1B3C |      1 |   333K |   333K | 00:00:00.08 |    2040 |  2036 |      0 |
----------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("C"."CUS_ID"="O"."CUS_ID")
   5 - filter(("O"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   9 - filter("CUS_ID"=:CUS)
  13 - filter(ROWNUM<=10)
  15 - filter(ROWNUM<=10)
  16 - filter("CUS_ID"=:CUS)

문제

이 화면이 열릴 때마다 TEMP를 쓰는 이유를 실행계획의 줄 번호로 설명하시오. 고객 조건(CUS_ID = :cus)이 왜 인덱스로 쓰이지 못했는지도 쓰시오.
결과가 바뀌지 않게 SQL을 고치시오. /*+ INLINE */ 힌트를 쓰는 안과 비교해 읽는 양을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 거를 조건(고객)은 처음부터 WITH 절 안에 둔다 : v 는 고객 한 명의 2024년 주문 167건만 담는다
WITH v AS (
  SELECT o.CUS_ID, o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.ORD_AMT, c.CUS_NM, c.CUS_GD
    FROM T_ORD_BIG o, M_CUS c
   WHERE c.CUS_ID = o.CUS_ID
     AND o.CUS_ID = :cus
     AND o.ORD_DT >= DATE '2024-01-01'
     AND o.ORD_DT <  DATE '2025-01-01'
)
SELECT s.CUS_NM, s.CUS_GD, s.ORD_CNT, s.ORD_AMT,
       d.ORD_SEQ, d.ORD_DT, d.ORD_ST, d.ORD_AMT AS AMT
  FROM (SELECT MAX(CUS_NM) AS CUS_NM, MAX(CUS_GD) AS CUS_GD,
               COUNT(*) AS ORD_CNT, SUM(ORD_AMT) AS ORD_AMT
          FROM v) s,
       (SELECT *
          FROM (SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
                  FROM v
                 ORDER BY ORD_DT DESC, ORD_SEQ DESC)
         WHERE ROWNUM <= 10) d

-- 다른 안 : 원래 SQL 의 WITH 에 /*+ INLINE */ → 두 참조가 각각 인덱스로 167건 (약 344블록)

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

xplan

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                           |      1 |        |     10 | 00:00:00.01 |     181 |
|   1 |  TEMP TABLE TRANSFORMATION                 |                           |      1 |        |     10 | 00:00:00.01 |     181 |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)  | SYS_TEMP_0FD9D6625_2A1B3C |      1 |        |      0 | 00:00:00.01 |     175 |
|   3 |    NESTED LOOPS                            |                           |      1 |    167 |    167 | 00:00:00.01 |     172 |
|   4 |     TABLE ACCESS BY INDEX ROWID            | M_CUS                     |      1 |      1 |      1 | 00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN                     | PK_M_CUS                  |      1 |      1 |      1 | 00:00:00.01 |       1 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED    | T_ORD_BIG                 |      1 |    167 |    167 | 00:00:00.01 |     170 |
|*  7 |      INDEX RANGE SCAN                      | IX_ORD_BIG_CUS_DT         |      1 |    167 |    167 | 00:00:00.01 |       3 |
|   8 |   MERGE JOIN CARTESIAN                     |                           |      1 |     10 |     10 | 00:00:00.01 |       6 |
|   9 |    VIEW                                    |                           |      1 |      1 |      1 | 00:00:00.01 |       3 |
|  10 |     SORT AGGREGATE                         |                           |      1 |      1 |      1 | 00:00:00.01 |       3 |
|  11 |      VIEW                                  |                           |      1 |    167 |    167 | 00:00:00.01 |       3 |
|  12 |       TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6625_2A1B3C |      1 |    167 |    167 | 00:00:00.01 |       3 |
|  13 |    BUFFER SORT                             |                           |      1 |     10 |     10 | 00:00:00.01 |       3 |
|  14 |     VIEW                                   |                           |      1 |     10 |     10 | 00:00:00.01 |       3 |
|* 15 |      COUNT STOPKEY                         |                           |      1 |        |     10 | 00:00:00.01 |       3 |
|  16 |       VIEW                                 |                           |      1 |    167 |     10 | 00:00:00.01 |       3 |
|* 17 |        SORT ORDER BY STOPKEY               |                           |      1 |    167 |     10 | 00:00:00.01 |       3 |
|  18 |         VIEW                               |                           |      1 |    167 |    167 | 00:00:00.01 |       3 |
|  19 |          TABLE ACCESS FULL                 | SYS_TEMP_0FD9D6625_2A1B3C |      1 |    167 |    167 | 00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("C"."CUS_ID"=:CUS)
   7 - access("O"."CUS_ID"=:CUS AND "O"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  15 - filter(ROWNUM<=10)
  17 - filter(ROWNUM<=10)

모범 답안 서술

본문에서 v를 두 번(요약 s와 최근 10건 d) 썼으므로 오라클은 v를 한 번만 계산해 임시 테이블로 만들어 두는 쪽을 골랐다(구체화, 1번 줄 TEMP TABLE TRANSFORMATION). 2번 줄 LOAD AS SELECT가 고객 조건 없이 2024년 주문 333,245건과 고객을 조인해 임시 테이블 SYS_TEMP_…에 쓰고(Writes 2,036, 약 16MB), 10번과 17번 줄이 그 임시 테이블을 통째로 두 번 읽는다(Reads 2,036씩). 고객 조건 CUS_ID = :cus는 임시 테이블 안으로 들어가지 못하고 9번, 16번 줄에서야 167건만 남긴다. 임시 테이블에는 인덱스도 없다. 그래서 IX_ORD_BIG_CUS_DT가 있는데도 쓰이지 않고, 한 번에 9,969블록과 TEMP 16MB를 쓴다. 고객 조건을 WITH 절 안으로 넣는다. 그러면 v는 고객 한 명의 2024년 주문 167건만 담는다. 이 167건은 IX_ORD_BIG_CUS_DT로 CUS_ID = :cus AND ORD_DT 범위를 바로 찾아 읽고(7번 줄 3블록, 테이블 170블록), 임시 테이블은 메모리 안에서 끝나 TEMP를 쓰지 않는다. Buffers 9,969 → 181, TEMP 16MB → 0이다. 원래 SQL의 WITH 절에 /*+ INLINE */을 붙여도 된다. 이때 v는 두 참조에 각각 풀려 들어가고, 바깥의 고객 조건도 함께 들어가 두 곳 모두 인덱스로 167건을 읽는다. 주문이 2,000행 간격으로 흩어져 있어 167건이 거의 167블록이므로 두 번이면 약 344블록이다. 둘 다 9,969보다 훨씬 작지만, 같은 167건을 한 번만 읽는 쪽(고객 조건을 넣은 WITH)이 낫다. 구체화 자체가 나쁜 것은 아니다. 두 번 쓰는 결과가 작으면 한 번 만들어 두는 것이 이득이고, 결과가 크면 TEMP에 쓰고 다시 읽는 비용이 원래 일보다 커진다. 구체화된 WITH 절에는 바깥 조건이 들어가지 못하므로, 거를 조건은 처음부터 WITH 절 안에 둔다.

해설

두 번 쓰면 구체화된다: 오라클은 본문에서 두 번 이상 참조한 WITH 절을 기본으로 임시 테이블로 만든다. 한 번만 참조하면 인라인 뷰처럼 본문에 풀린다. 계획에서 SYS_TEMP_를 보면 무엇이 몇 행 쓰였는지부터 본다.
분석 함수로 한 번에 읽는 안: COUNT(*) OVER (), SUM(ORD_AMT) OVER ()와 ROW_NUMBER() OVER (ORDER BY ORD_DT DESC, ORD_SEQ DESC)를 쓰면 WITH 없이 167건을 한 번 읽어 요약과 10건을 함께 낸다. 이것도 답이다.
MATERIALIZE 힌트의 함정: '임시 테이블이 빠르다'는 생각으로 /*+ MATERIALIZE */를 붙이면 지금 모양 그대로 고정된다. 힌트는 계획을 고정하므로 무엇을 구체화하는지 확인한 뒤에만 쓴다(교재 15화 §8, 06화 힌트 유효기간 대장).
고객 조건을 넣은 WITH도 구체화되지만 167행이라 메모리 안 임시 테이블(CURSOR DURATION MEMORY)로 끝난다. 임시 테이블을 만드는 일조차 아깝다면 INLINE이나 분석 함수 안을 고른다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황