고객센터 고객 목록이 페이지를 넘길 때마다 반 박자씩 늦다

기초DDL한빛유통654 / 700

상황

고객센터 「고객 목록」은 고객 20명씩 한 페이지로 보여 주고, 줄마다 최근 주문일을 붙인다. 상담원 30명이 하루 약 4,000페이지를 넘긴다. 상담원들은 "페이지를 넘길 때마다 반 박자씩 늦다"라고 하고, 개발팀은 "실행계획을 떠 보면 4블록이라 화면 SQL은 문제가 없다"라고 한다. DBA가 AWR에서 읽은 블록 수 1위 SQL을 보내왔다. SELECT MAX(ORD_DT) FROM T_ORD WHERE CUS_ID = :B1인데, 개발팀은 "그런 SQL은 소스에 없다"라고 답했다.

테이블과 인덱스

M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ)만 있다 · CUS_ID 인덱스 없음 · 고객 한 명에 평균 50건 · ORD_DT는 시분초가 있는 DATE
함수 FN_LAST_ORD_DT는 2021년에 만들었고, 고객 목록과 고객 상세, 주문 등록 화면 세 곳이 쓴다.
통계 최신

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_LAST_ORD_DT(p_cus_id VARCHAR2) RETURN DATE IS
  v_dt DATE;
BEGIN
  SELECT MAX(ORD_DT) INTO v_dt
    FROM T_ORD
   WHERE CUS_ID = p_cus_id;
  RETURN v_dt;
END;
/

-- 고객 목록 (한 페이지 20명 · 다음 페이지는 앞 페이지 마지막 고객 :last_cus_id 뒤부터)
SELECT v.CUS_ID, v.CUS_NM, v.CUS_GD,
       FN_LAST_ORD_DT(v.CUS_ID) AS last_ord_dt
  FROM (SELECT CUS_ID, CUS_NM, CUS_GD
          FROM M_CUS
         WHERE CUS_ID > :last_cus_id
         ORDER BY CUS_ID) v
 WHERE ROWNUM <= 20;

실행계획 학습용 예시

xplan

[화면 SQL: DISPLAY_CURSOR (ALLSTATS LAST) · 한 페이지]
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |     20 |00:00:00.15 |       4 |
|*  1 |  COUNT STOPKEY                |          |      1 |        |     20 |00:00:00.15 |       4 |
|   2 |   VIEW                        |          |      1 |     20 |     20 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID| M_CUS    |      1 |     20 |     20 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN          | PK_M_CUS |      1 |     20 |     20 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   4 - access("CUS_ID">:LAST_CUS_ID)

[V$SQL: 함수 안의 문장 (dep=1) · 하루 누적]
  SQL_TEXT                                           EXECUTIONS   ROWS_PROCESSED   BUFFER_GETS   ELAPSED
  SELECT MAX(ORD_DT) FROM T_ORD WHERE CUS_ID = :B1       81,240           81,240    72,791,040    566 초
  PROGRAM_ID → SQLP.FN_LAST_ORD_DT (PROGRAM_LINE# 4)

[그 문장의 계획: DISPLAY_CURSOR(sql_id => '그 문장', format => 'ALLSTATS LAST')]
------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      1 |00:00:00.01 |     896 |
|   1 |  SORT AGGREGATE    |       |      1 |      1 |      1 |00:00:00.01 |     896 |
|*  2 |   TABLE ACCESS FULL| T_ORD |      1 |     50 |     50 |00:00:00.01 |     896 |
------------------------------------------------------------------------------------
   2 - filter("CUS_ID"=:B1)

문제

화면 SQL의 계획은 4블록인데 페이지마다 늦어지는 이유를 위 자료로 설명하시오. 개발팀이 소스에서 그 SQL을 찾지 못한 이유도 쓰시오.
이 함수를 쓰는 세 화면이 모두 빨라지도록 고치시오. 함수를 조인으로 바꾸는 안과 견주고, 개선 뒤 한 페이지의 일량도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 함수 안 SQL(고객별 최근 주문일)이 읽을 인덱스: 고객 안에서 주문일 순으로 정렬된다
CREATE INDEX IX_T_ORD_CUS_DT ON T_ORD (CUS_ID, ORD_DT);

-- 화면 SQL과 함수는 그대로 둔다
--   한 페이지에 20번 호출 · 고객 상세 · 주문 등록 화면도 같은 함수라 함께 빨라진다

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

xplan

[함수 안 문장의 계획: 개선 뒤]
------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE              |                 |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   FIRST ROW                  |                 |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IX_T_ORD_CUS_DT |      1 |      1 |      1 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------
   3 - access("CUS_ID"=:B1)

[한 페이지]  화면 SQL 4 + 함수 안 SQL 20 × 3 = 64블록 (전: 4 + 20 × 896 = 17,924) · 0.15 초 → 0.01 초 미만
[하루]       함수 안 SQL BUFFER_GETS 72,791,040 → 약 243,720

모범 답안 서술

화면 SQL의 계획(4블록)에는 함수 안의 SELECT가 하는 일이 들어 있지 않다. FN_LAST_ORD_DT 안의 SELECT MAX(ORD_DT) …는 화면 SQL과 따로 파싱되고 실행되는 재귀 SQL(dep=1)이라서, 화면 SQL의 계획을 떠도 보이지 않고 V$SQL과 AWR에만 따로 잡힌다. 개발팀이 소스에서 못 찾은 것도 그래서다. 그 문장은 애플리케이션 소스가 아니라 함수 안에 있고, 바인드 이름도 :B1으로 바뀌어 있다. PROGRAM_ID가 그 함수를 가리킨다. 그 문장의 계획을 따로 떠 보면 CUS_ID 인덱스가 없어서 호출마다 T_ORD를 FULL로 896블록씩 읽는다. 한 페이지에 고객 20명이니 20번 호출되고, 한 페이지에 4 + 20 × 896 = 17,924블록을 읽는다. 하루로는 81,240번 실행에 72,791,040블록이다. 고칠 곳은 함수 안 SQL의 접근 경로다. T_ORD (CUS_ID, ORD_DT) 인덱스를 만들면 고객 안에서 주문일이 정렬되어 있으므로 INDEX RANGE SCAN (MIN/MAX)와 FIRST ROW로 맨 끝 한 건만 읽는다. 호출당 896 → 3블록, 한 페이지 17,924 → 64블록이 된다. 한 페이지에 20번이면 호출 수 자체는 문제가 아니다. 조인으로 바꾸려면 고객별 최근 주문일을 GROUP BY로 구해야 하는데, 인덱스가 없으면 페이지마다 896블록을 다 읽어 오히려 더 읽고, 인덱스가 있으면 함수와 일량이 같다. 인덱스 하나로 고객 상세와 주문 등록 화면까지 세 곳이 함께 빨라지므로 함수는 그대로 둔다. 대가는 주문을 등록할 때마다 인덱스를 하나 더 고치는 비용이다. 주문 한 건에 인덱스 블록 한두 개 정도라서, 하루 7천만 블록을 아끼는 것에 비하면 작다.

해설

함수 안 SQL은 따로 본다. 바깥 계획에 함수 호출은 줄로 나오지 않는다. V$SQL에서 PROGRAM_ID로 함수를 찾고, 그 문장의 SQL_ID로 DISPLAY_CURSOR를 따로 떠야 한다. TKPROF라면 같은 문장이 dep=1의 RECURSIVE 쪽에 잡힌다(교재 15화).
함수가 늘 나쁜 것은 아니다. 이 화면은 페이징을 먼저 하고 바깥에서 함수를 불러 20번만 호출한다. 같은 함수를 고객 2,000명 전체 목록에 쓰면 2,000번이 되고, 그때는 조인과 GROUP BY가 낫다.
(CUS_ID) 하나만으로 만들어도 범위는 좁아지지만, 고객의 주문 50건을 테이블에서 다 읽고 최댓값을 구한다(호출당 50블록 남짓). ORD_DT까지 넣어야 끝 한 건에서 멈춘다.
결과는 바뀌지 않는다. 주문이 없는 고객은 개선 전후 모두 NULL이다(MAX는 0건이어도 한 행을 돌려준다).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황