점심 마감 보고서의 매장별 판매 건수를 더하면 총계와 맞지 않는다

기초SELECT유통529 / 700

상황

본사 레거시 유통은 매일 13:00에 오전 판매 보고서를 뽑아 MD들에게 보낸다. 보고서 프로그램은 SQL 셋을 차례로 실행해 매장별 판매 건수, 상품별 판매 수량과 금액, 오늘 총계를 한 장에 찍는다. 매장은 영업 중이라 POS가 계속 판매를 넣는다. MD가 물었다. "매장별 건수를 더하면 312건인데 총계는 315건이에요. 상품별 금액을 더해도 총계와 안 맞고요." 보고서를 다시 뽑으면 또 몇 건씩 다르다. 영업 전인 07:00에 뽑으면 맞는다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · 인덱스 IX_CF10_SLE_DT (SLE_DT) · 하루 약 700건, 13:00까지 약 310건
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 판매당 약 2.2행
POS는 결제가 끝나면 CF10과 CF11을 넣고 바로 COMMIT한다. 13:00 무렵에는 분당 2 ~ 4건이다.
보고서 프로그램은 세션 하나로 세 SQL을 실행하고, 결과를 하나씩 받아 양식에 채운 뒤 다음 SQL로 넘어간다(모두 약 3초).

원본 SQL

sql

-- ① 매장별 판매 건수
SELECT h.SHP_NO, COUNT(*) AS SLE_CNT
  FROM CF10 h
 WHERE h.SLE_DT = :today
 GROUP BY h.SHP_NO;

-- ② 상품별 판매 수량과 금액
SELECT d.ITM_NO, SUM(d.SLE_QTY) AS QTY, SUM(d.SLE_QTY * d.SLE_UPRC) AS AMT
  FROM CF10 h JOIN CF11 d ON d.SLE_NO = h.SLE_NO
 WHERE h.SLE_DT = :today
 GROUP BY d.ITM_NO;

-- ③ 오늘 총계
SELECT COUNT(DISTINCT h.SLE_NO) AS SLE_CNT, SUM(d.SLE_QTY * d.SLE_UPRC) AS AMT
  FROM CF10 h JOIN CF11 d ON d.SLE_NO = h.SLE_NO
 WHERE h.SLE_DT = :today;

실행계획 학습용 예시

xplan

[보고서 한 번 — 12월 14일 13:00 · 세 SQL 의 시작 시각과 읽기 시점(SCN)]
SQL   시작           읽기 시점 SCN    결과
①    13:00:00.21    71,402,118       매장 6곳 · 합계 312건
②    13:00:01.02    71,402,190       상품 94개 · 금액 합 4,880,900원 (313건분)
③    13:00:02.40    71,402,251       315건 · 4,902,600원
      (그 사이 COMMIT 된 POS 결제: 13:00:00.6 에 1건 · 13:00:01.9 와 13:00:02.1 에 2건)

[③ 오늘 총계 — ALLSTATS LAST]
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |      1 | 00:00:00.02 |    1063 |
|   1 |  SORT GROUP BY                         |                |      1 |      1 |      1 | 00:00:00.02 |    1063 |
|   2 |   NESTED LOOPS                         |                |      1 |    680 |    693 | 00:00:00.02 |    1063 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |    706 |    315 | 00:00:00.01 |       6 |
|*  4 |     INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |    706 |    315 | 00:00:00.01 |       3 |
|   5 |    TABLE ACCESS BY INDEX ROWID         | CF11           |    315 |      2 |    693 | 00:00:00.01 |    1057 |
|*  6 |     INDEX RANGE SCAN                   | CF11_PK        |    315 |      2 |    693 | 00:00:00.01 |     632 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("H"."SLE_DT"=:TODAY)
   6 - access("D"."SLE_NO"="H"."SLE_NO")

[같은 보고서 — 지난주]  07:00 (영업 전) 세 숫자 일치 · 13:00 다섯 번 가운데 네 번 불일치 (2 ~ 5건)

문제

세 SQL의 읽기 시점을 근거로 보고서 숫자가 서로 맞지 않는 이유를 설명하시오.
세 SQL이 같은 시점의 데이터를 보도록 고치시오. POS의 판매 입력을 막지 않아야 하며, 고친 방법의 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

SET TRANSACTION READ ONLY;      -- 이 트랜잭션의 모든 SELECT 가 이 순간의 데이터를 본다 (잠그지 않는다)

-- ① 매장별 판매 건수
SELECT h.SHP_NO, COUNT(*) AS SLE_CNT
  FROM CF10 h
 WHERE h.SLE_DT = :today
 GROUP BY h.SHP_NO;

-- ② 상품별 판매 수량과 금액
SELECT d.ITM_NO, SUM(d.SLE_QTY) AS QTY, SUM(d.SLE_QTY * d.SLE_UPRC) AS AMT
  FROM CF10 h JOIN CF11 d ON d.SLE_NO = h.SLE_NO
 WHERE h.SLE_DT = :today
 GROUP BY d.ITM_NO;

-- ③ 오늘 총계
SELECT COUNT(DISTINCT h.SLE_NO) AS SLE_CNT, SUM(d.SLE_QTY * d.SLE_UPRC) AS AMT
  FROM CF10 h JOIN CF11 d ON d.SLE_NO = h.SLE_NO
 WHERE h.SLE_DT = :today;

COMMIT;                         -- 읽기 전용 트랜잭션을 끝낸다 (다음 보고서는 다시 SET TRANSACTION 부터)

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

xplan

[고친 뒤 — 같은 13:00 (학습용 예시)]
SQL   시작           읽기 시점 SCN    결과
①    13:00:00.21    71,402,118       합계 312건
②    13:00:01.02    71,402,118       금액 합 4,871,300원 (312건분)
③    13:00:02.40    71,402,118       312건 · 4,871,300원
  그사이 COMMIT 된 POS 결제 3건은 다음 보고서에 들어간다 · POS 기다림 0
  ② · ③ 은 13:00:00.21 뒤에 바뀐 블록 몇 개를 언두로 되돌려 읽는다 · Buffers 는 거의 그대로

모범 답안 서술

오라클의 기본 격리 수준인 READ COMMITTED에서 읽기 일관성은 문장 하나의 범위다. 각 SQL은 자기가 시작한 순간의 SCN으로 데이터를 보고, 그 뒤에 COMMIT된 변경은 그 문장에 보이지 않는다. 보고서의 세 SQL은 13:00:00.21, 13:00:01.02, 13:00:02.40에 따로 시작했으므로 읽기 시점 SCN이 셋 다 다르다. 그 사이에 POS 결제가 13:00:00.6에 1건, 13:00:01.9와 13:00:02.1에 2건 COMMIT됐다. 그래서 ①은 312건, ②는 313건분의 금액, ③은 315건을 보았다. SQL 하나하나는 정확하지만 서로 다른 순간의 사진을 한 장에 붙인 셈이다. 영업 전 07:00에는 그 사이에 바뀌는 데이터가 없어서 맞았다. 고친 판은 맨 앞에 SET TRANSACTION READ ONLY를 둔다. 읽기 전용 트랜잭션에서는 모든 SQL이 트랜잭션이 시작한 순간의 데이터를 보므로(트랜잭션 수준 읽기 일관성) 세 SQL의 읽기 시점이 같아진다. 그사이 COMMIT된 결제는 다음 보고서에 들어간다. 읽기 일관성은 언두로 과거 사본을 만들어 지키는 것이라 아무것도 잠그지 않으므로 POS의 입력을 막지 않는다. 세 SQL이 끝나면 COMMIT으로 트랜잭션을 끝낸다. 대가도 있다. 읽기 전용 트랜잭션이 길어지면 그동안 바뀐 블록을 시작 시점으로 되돌릴 언두가 계속 필요하므로, 몇십 분짜리 보고서라면 ORA-01555 위험이 커진다. 이 보고서는 3초라 문제가 없다. 또 그 안에서는 INSERT나 UPDATE를 할 수 없으므로(ORA-01456), 보고서 결과를 테이블에 남기려면 트랜잭션을 끝낸 뒤에 한다.

해설

SERIALIZABLE도 같은 시점을 준다. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE로 열면 세 SELECT가 트랜잭션 시작 시점을 본다. 읽기만 한다면 READ ONLY가 뜻이 분명하고, 실수로 넣은 DML도 막아 준다.
다른 답은 세 결과를 한 문장으로 뽑는 것이다. GROUP BY GROUPING SETS ((h.SHP_NO), (d.ITM_NO), ())처럼 묶으면 한 문장이 한 시점을 보고 테이블도 한 번만 읽는다. 다만 매장별 건수는 판매상품 행이 아니라 판매로 세야 하므로 COUNT(DISTINCT h.SLE_NO)처럼 세는 기준을 맞춰야 한다.
세 SQL에 같은 SCN으로 AS OF SCN :scn을 주는 플래시백 쿼리도 한 시점을 만든다. 보고서가 여러 세션에 나뉘어 돌 때 쓸 만하다.
LOCK TABLE … IN SHARE MODE로 POS를 잠깐 멈추는 방법은 숫자를 맞추려고 판매를 막는 것이라 답이 아니다. 오라클에서는 읽기 때문에 쓰기가 기다릴 일이 없다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황