포인트 대사 리포트의 한 줄 안에서 적립, 사용, 소멸과 잔액이 맞지 않는다

중급SELECT이커머스530 / 700

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

상황

이커머스 회계팀은 매월 1일 00:00에 「포인트 대사 리포트」를 받아 포인트 부채를 확인한다. 리포트는 고객마다 적립, 사용, 소멸 합계와 잔액을 한 줄에 보여 준다. 같은 시각에 「포인트 소멸 배치」도 돈다. 회계팀이 물었다. "한 줄 안에서 적립에서 사용과 소멸을 빼면 잔액이 되어야 하는데 37명이 안 맞아요. SQL 한 문장이면 한 시점이라고 들었는데요?" 낮에 다시 뽑으면 모두 맞는다.

테이블과 인덱스

customers: 5,230행 · 51블록 · PK customer_id
point_transactions: 45,000행 · 210블록 · PK tx_id만 있다 · tx_type은 EARN, USE, EXPIRE · 이 문제에서 amount는 부호 없이 적고 tx_type으로 더할지 뺄지를 정한다
함수 fn_point_sum(고객, 종류)와 fn_point_bal(고객)은 화면 여러 곳이 쓰는 공용 함수다.
「포인트 소멸 배치」: 매월 1일 00:00에 유효기간이 지난 적립을 고객마다 EXPIRE 한 행으로 넣고 고객마다 COMMIT한다. 이번 달 대상은 1,240명이고 약 40초 걸렸다.

원본 SQL

sql

-- 공용 함수 (둘 다 point_transactions 를 읽는다)
CREATE OR REPLACE FUNCTION fn_point_sum(p_cus NUMBER, p_tp VARCHAR2) RETURN NUMBER IS
  v NUMBER;
BEGIN
  SELECT NVL(SUM(amount), 0) INTO v
    FROM point_transactions
   WHERE customer_id = p_cus AND tx_type = p_tp;
  RETURN v;
END;

CREATE OR REPLACE FUNCTION fn_point_bal(p_cus NUMBER) RETURN NUMBER IS
  v NUMBER;
BEGIN
  SELECT NVL(SUM(CASE tx_type WHEN 'EARN' THEN amount ELSE -amount END), 0) INTO v
    FROM point_transactions
   WHERE customer_id = p_cus;
  RETURN v;
END;

-- 「포인트 대사 리포트」 (매월 1일 00:00:05)
SELECT c.customer_id, c.name, c.grade,
       fn_point_sum(c.customer_id, 'EARN')   AS earn_amt,
       fn_point_sum(c.customer_id, 'USE')    AS use_amt,
       fn_point_sum(c.customer_id, 'EXPIRE') AS expire_amt,
       fn_point_bal(c.customer_id)           AS bal_amt
  FROM customers c
 ORDER BY c.customer_id;

실행계획 학습용 예시

xplan

[리포트 — ALLSTATS LAST (11월 1일 00:00:05)]
--------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |   5230 | 00:00:09.41 |      51 |
|   1 |  SORT ORDER BY      |           |      1 |   5230 |   5230 | 00:00:09.41 |      51 |
|   2 |   TABLE ACCESS FULL | CUSTOMERS |      1 |   5230 |   5230 | 00:00:09.36 |      51 |
--------------------------------------------------------------------------------------------

[함수 안의 SQL — V$SQL (같은 실행)]
SQL_TEXT                                                          EXECUTIONS   BUFFER_GETS   ELAPSED
SELECT NVL(SUM(AMOUNT), 0) FROM POINT_TRANSACTIONS
 WHERE CUSTOMER_ID = :B2 AND TX_TYPE = :B1                           15,690     3,294,900    6.9초
SELECT NVL(SUM(CASE TX_TYPE WHEN 'EARN' THEN AMOUNT ELSE -AMOUNT END), 0)
  FROM POINT_TRANSACTIONS WHERE CUSTOMER_ID = :B1                     5,230     1,098,300    2.3초
  (실행 한 번에 TABLE ACCESS FULL 210블록)

[고객 2,417 의 한 줄 — 함수가 불린 순간]
00:00:07.204   fn_point_sum EARN    → 12,000
00:00:07.205   fn_point_sum USE     →  3,000
00:00:07.206   fn_point_sum EXPIRE  →      0
00:00:07.206   소멸 배치가 고객 2,417 에 EXPIRE 2,500 INSERT · COMMIT
00:00:07.207   fn_point_bal         →  6,500        ← 12,000 - 3,000 - 0 = 9,000 이어야 한 줄이 맞는다

[11월 1일 리포트]  5,230행 · 한 줄 안에서 맞지 않는 고객 37명 · 14:00 에 다시 뽑으면 0명

문제

리포트가 한 문장인데도 한 줄 안의 숫자가 서로 다른 시점의 것이 되는 이유를 설명하시오. 실행계획의 Buffers 51과 V$SQL의 숫자가 왜 이렇게 다른지도 쓰시오.
한 줄 안의 숫자가 같은 시점의 것이 되도록 리포트를 고치시오. 고친 뒤에도 회계팀이 알아야 할 점이 있다면 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 문장 · point_transactions 를 한 번만 읽는다 — 한 줄의 네 숫자가 같은 시점에서 나온다
SELECT c.customer_id, c.name, c.grade,
       NVL(p.earn_amt, 0)   AS earn_amt,
       NVL(p.use_amt, 0)    AS use_amt,
       NVL(p.expire_amt, 0) AS expire_amt,
       NVL(p.earn_amt, 0) - NVL(p.use_amt, 0) - NVL(p.expire_amt, 0) AS bal_amt
  FROM customers c
  LEFT JOIN (SELECT customer_id,
                    SUM(CASE WHEN tx_type = 'EARN'   THEN amount END) AS earn_amt,
                    SUM(CASE WHEN tx_type = 'USE'    THEN amount END) AS use_amt,
                    SUM(CASE WHEN tx_type = 'EXPIRE' THEN amount END) AS expire_amt
               FROM point_transactions
              GROUP BY customer_id) p
    ON p.customer_id = c.customer_id
 ORDER BY c.customer_id;
-- 실행은 소멸 배치가 끝난 뒤로 옮긴다 (배치의 완료 기록을 확인하고 시작)

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

xplan

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Starts | E-Rows | A-Rows |    A-Time   | Buffers |  Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |      1 |        |   5230 | 00:00:00.05 |     261 |           |
|   1 |  SORT ORDER BY         |                    |      1 |   5230 |   5230 | 00:00:00.05 |     261 |  690K (0) |
|*  2 |   HASH JOIN OUTER      |                    |      1 |   5230 |   5230 | 00:00:00.04 |     261 | 1342K (0) |
|   3 |    TABLE ACCESS FULL   | CUSTOMERS          |      1 |   5230 |   5230 | 00:00:00.01 |      51 |           |
|   4 |    VIEW                |                    |      1 |   4980 |   4987 | 00:00:00.03 |     210 |           |
|   5 |     HASH GROUP BY      |                    |      1 |   4980 |   4987 | 00:00:00.03 |     210 | 1121K (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")

함수 안 SQL 실행 20,920번 → 0 · 소멸 배치가 끝난 00:01:00 에 돌리면 한 줄 안의 불일치 0명

모범 답안 서술

함수 안의 SELECT는 리포트 문장의 일부가 아니라, 함수가 불릴 때마다 따로 실행되는 별개의 문장(재귀 SQL)이다. READ COMMITTED에서 그 문장은 리포트가 시작한 00:00:05의 시점이 아니라 자기가 실행되는 순간의 시점을 본다. 리포트는 고객마다 함수 네 개를 부르므로 한 줄의 네 숫자가 서로 다른 네 순간에 읽힌다. 고객 2,417은 EXPIRE를 읽은 00:00:07.206과 잔액을 읽은 00:00:07.207 사이에 소멸 배치가 EXPIRE 2,500을 COMMIT해서, 소멸은 0인데 잔액은 소멸 뒤의 6,500이 되었다. 문장 수준 읽기 일관성은 그 문장이 직접 읽는 데이터에만 해당한다. 실행계획의 Buffers 51은 customers를 읽은 몫뿐이다. 함수 안 SQL은 별개의 커서라 그 일이 리포트의 실행계획에 잡히지 않는다. V$SQL을 보면 함수 안 SQL이 20,920번(5,230명 × 4) 실행됐고, 실행마다 point_transactions 210블록을 FULL로 읽어 모두 4,393,200블록을 읽었다. 9.4초의 거의 전부가 여기서 나온다. 고친 판은 함수를 걷어 내고, point_transactions를 고객별로 한 번 집계해 customers에 아우터 조인하는 한 문장으로 바꾼다. 한 문장이 직접 읽으므로 한 줄의 네 숫자가 모두 같은 시점에서 나오고, 잔액은 같은 행의 세 합계로 계산하므로 어긋날 수가 없다. 읽는 양은 4,393,200 → 261블록, 시간은 9.4초 → 0.05초로 준다. 회계팀이 알아야 할 점이 하나 남는다. 한 시점이 되었어도 00:00:05는 소멸 배치가 도는 도중이라 소멸이 반쯤 반영된 시점이다. 한 줄은 맞지만 고객에 따라 이번 달 소멸이 들어가거나 빠진다. 포인트 부채를 보려면 리포트를 소멸 배치가 끝난 뒤로 옮긴다. 함수를 꼭 써야 한다면 리포트 앞에 SET TRANSACTION READ ONLY를 두어 함수 안 SQL까지 트랜잭션 시작 시점을 보게 할 수 있지만, 20,920번의 실행은 그대로다.

해설

SQL에서 부르는 함수는 그 문장의 읽기 일관성 바깥에 있다. 함수 안 SELECT는 부를 때마다 새 시점을 본다. 거의 바뀌지 않는 코드 테이블을 읽는 함수라면 드러나지 않지만, 리포트가 도는 동안 바뀌는 데이터를 읽는 함수라면 한 줄 안에서도 숫자가 어긋난다.
함수를 스칼라 서브쿼리로 바꾸기만 해도 같은 문장 안으로 들어와 시점이 같아진다. 다만 고객마다 point_transactions를 다시 읽는 구조는 그대로라 Buffers는 거의 줄지 않는다. 한 번 집계해 조인하는 편이 둘 다 해결한다(교재 15b화).
결과 캐시나 DETERMINISTIC을 함수에 붙이는 것은 답이 아니다. 실행 횟수는 줄 수 있어도 시점 문제는 그대로이고, 바뀌는 데이터를 읽는 함수에 DETERMINISTIC을 붙이면 틀린 값을 재사용할 수 있다.
리포트 시각을 옮길 때는 시계가 아니라 배치가 끝났다는 기록(작업 로그의 완료 행)을 확인하고 시작하게 한다. 배치가 늦어지는 날에도 맞는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황