계획은 51블록인데 월말 포인트 명세는 9초가 걸린다

기초PLSQL이커머스651 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

CRM팀의 「월말 포인트 명세」는 고객 5,230명 전부의 포인트 거래 합계를 한 장으로 뽑는다. 담당자가 물었다. "실행계획을 떠 보면 51블록이에요. 그런데 9초가 걸려요. 서버가 작아서 그런가요?" 합계는 개발팀이 만든 함수 fn_point_sum이 고객마다 구한다.

테이블과 인덱스

customers: 5,230행 · 51블록 · PK(customer_id)
point_transactions: 45,000행 · 210블록 · PK(tx_id)만 있다 · customer_id 인덱스 없음 · 고객 한 명에 평균 약 9건 · 거래가 없는 고객이 있을 수 있다
함수 안의 SELECT는 바깥 문장과 따로 파싱되고 실행되는 재귀 SQL이다(dep=1).

원본 SQL

sql

CREATE OR REPLACE FUNCTION fn_point_sum(p_cust NUMBER) RETURN NUMBER IS
  v_sum NUMBER;
BEGIN
  SELECT NVL(SUM(amount), 0) INTO v_sum
    FROM point_transactions
   WHERE customer_id = p_cust;
  RETURN v_sum;
END;
/

SELECT c.customer_id, c.name, c.grade,
       fn_point_sum(c.customer_id) AS point_sum
  FROM customers c
 ORDER BY c.customer_id;

실행계획 학습용 예시

xplan

[바깥 문장 — DISPLAY_CURSOR (ALLSTATS LAST)]
------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |   5230 |00:00:09.12 |      51 |          |
|   1 |  SORT ORDER BY     |           |      1 |   5230 |   5230 |00:00:09.12 |      51 |  372K (0)|
|   2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |   5230 |   5230 |00:00:00.01 |      51 |          |
------------------------------------------------------------------------------------------------------

[세션 통계 — 이 문장 한 번 (V$MYSTAT 증가분)]
  경과 시간                  9.1 초 (거의 전부 CPU)
  recursive calls            5,236
  execute count              5,232
  session logical reads      1,098,412

[V$SQL — 함수 안의 문장 (dep=1)]
  SQL_TEXT                                                              EXECUTIONS  FETCHES  ROWS_PROCESSED  BUFFER_GETS
  SELECT NVL(SUM(AMOUNT), 0) FROM POINT_TRANSACTIONS WHERE CUSTOMER_ID = :B1     5,230    5,230           5,230    1,098,300
  계획 : SORT AGGREGATE ← TABLE ACCESS FULL POINT_TRANSACTIONS · filter("CUSTOMER_ID"=:B1) · 한 번 210 블록

문제

계획의 Buffers는 51인데 9초가 걸린 이유를 세션 통계와 V$SQL로 설명하시오.
결과가 바뀌지 않게 고치시오. 함수를 스칼라 서브쿼리로 감싸 캐시에 맡기는 안이 여기서 효과가 있는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 함수를 부르지 않는다 — 포인트 거래를 한 번 읽어 고객별로 묶고(GROUP BY) 고객에 붙인다
SELECT c.customer_id, c.name, c.grade,
       NVL(p.point_sum, 0) AS point_sum           -- 거래가 없는 고객은 0 (함수의 NVL 과 같다)
  FROM customers c
  LEFT OUTER JOIN (SELECT customer_id, SUM(amount) AS point_sum
                     FROM point_transactions
                    GROUP BY customer_id) p
    ON p.customer_id = c.customer_id
 ORDER BY c.customer_id;

-- (함수를 그대로 써야 하는 다른 호출부가 있다면) 함수 안 SQL 에 인덱스 — 한 번 210 → 2 블록 남짓
-- CREATE INDEX IX_PTX_CUST_AMT ON point_transactions (customer_id, amount);

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

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |      1 |        |   5230 |00:00:00.03 |     261 |          |
|   1 |  SORT ORDER BY         |                    |      1 |   5230 |   5230 |00:00:00.03 |     261 |  372K (0)|
|*  2 |   HASH JOIN OUTER      |                    |      1 |   5230 |   5230 |00:00:00.02 |     261 | 1453K (0)|
|   3 |    TABLE ACCESS FULL   | CUSTOMERS          |      1 |   5230 |   5230 |00:00:00.01 |      51 |          |
|   4 |    VIEW                |                    |      1 |   5229 |   5229 |00:00:00.01 |     210 |          |
|   5 |     HASH GROUP BY      |                    |      1 |   5229 |   5229 |00:00:00.01 |     210 | 1528K (0)|
|   6 |      TABLE ACCESS FULL | POINT_TRANSACTIONS |      1 |  45000 |  45000 |00:00:00.01 |     210 |          |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."CUSTOMER_ID"(+)="C"."CUSTOMER_ID")

[세션 통계]  경과 0.03 초 · recursive calls 0 · session logical reads 261

모범 답안 서술

바깥 문장의 계획은 고객 51블록만 보여 준다. 함수 안의 SELECT는 따로 파싱되고 실행되는 재귀 SQL이라 그 일(Buffers)이 바깥 계획에 나오지 않는다. 세션 통계의 recursive calls 5,236은 고객 5,230과 거의 같다. 행마다 함수가 한 번씩 호출됐고, 그때마다 함수 안 SELECT가 customer_id 인덱스가 없는 point_transactions를 처음부터 끝까지(210블록) 읽었다. V$SQL의 Executions = Fetches = Rows = 5,230은 '행마다 호출된 한 건 조회'의 지문이다. 210 × 5,230 = 1,098,300블록으로, 9초의 거의 전부가 여기서 나왔다. 서버가 작아서가 아니다. 함수 대신 포인트 거래를 한 번 읽어 고객별로 묶고(GROUP BY) 고객에 아우터 조인한다. 거래가 없는 고객도 함수처럼 0으로 남도록 LEFT OUTER JOIN과 NVL을 쓴다. Buffers는 약 110만 → 261, recursive calls는 5,236 → 0, 경과 시간은 9초 → 0.03초로 준다. 한 문장이므로 결과도 한 시점의 데이터다(함수 안 SQL은 호출마다 그 순간의 데이터를 본다). 스칼라 서브쿼리로 감싸는 안((SELECT fn_point_sum(c.customer_id) FROM dual))은 여기서 효과가 없다. 캐시는 같은 입력을 기억하는데, customer_id는 PK라 행마다 다른 값이기 때문이다. DETERMINISTIC도 같은 이유로 호출을 줄이지 못한다. 함수를 그대로 써야 하는 다른 호출부가 있다면 함수 안 SQL을 위해 (customer_id, amount) 인덱스를 둔다. 그러면 한 번에 210 → 2블록 남짓으로 줄지만, 호출 5,230번과 엔진 왕복은 그대로 남는다.

해설

스칼라 서브쿼리를 SQL 안에 바로 쓰면 PL/SQL 왕복은 사라진다((SELECT NVL(SUM(t.amount), 0) FROM point_transactions t WHERE t.customer_id = c.customer_id)). 하지만 인덱스가 없으면 그 서브쿼리 줄에 Starts 5,230이 찍히고, 같은 110만 블록이 그대로 보인다. 인덱스와 함께 써야 답이 된다.
캐시는 입력이 되풀이될 때만 의미가 있다. 15화의 보정률 함수는 고객 2,000명에 정산 10만 행이라, 스칼라 서브쿼리 캐시가 호출을 100,020 → 57,679로 줄이긴 했다(칸이 모자라 0이 되지는 않았다). 여기는 되풀이가 없다.
결과 캐시(RESULT_CACHE)도 맞지 않는다. 고객마다 답이 하나라 칸이 5,230개 필요하고, 포인트 거래가 커밋될 때마다 그 테이블에 기대는 답이 전부 지워진다(15b화).
'서버 증설'을 검토하기 전에 recursive calls와 V$SQL의 Executions를 먼저 본다. 같은 서버에서 9초 → 0.03초가 됐다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황