고객 2,000명만 확인하면 되는 점검이 고객 마스터를 75만 번 찾아간다

실전SELECT한빛유통236 / 700

상황

정산 화면 상단의 「고객 미확인 주문」 배지는 주문 원장의 고객번호가 고객 마스터에 없는(고객명이 붙지 않는) 주문이 몇 건인지 보여 준다. 정산 담당자 10명이 화면을 열 때마다 돌아서 하루 약 300번 실행되고, 한 번에 0.47초 걸린다. 이 SQL을 짠 조민우는 "고객은 2,000명뿐이니 스칼라 서브쿼리면 캐시돼서 2,000번만 간다. 06화의 지역명도 Starts 8이었다"라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · CUS_ID 2,000종류가 정확히 500번씩 돌아가며 들어 있다 (CUS_000001, CUS_000002, … 순서)
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
한빛유통 신 시스템은 FK가 0개라 주문의 고객번호가 마스터에 있다는 보장이 없다 (지금 고아는 0건)
통계 최신 · _query_execution_cache_max_size는 기본값 (131,072바이트)

원본 SQL

sql

SELECT COUNT(*)  AS ord_cnt,
       COUNT(nm) AS named_cnt,          -- ord_cnt - named_cnt = 고객 미확인 주문
       MAX(nm)   AS nm_sample
  FROM (SELECT (SELECT c.CUS_NM FROM M_CUS c WHERE c.CUS_ID = o.CUS_ID) AS nm
          FROM T_ORD_BIG o)

실행계획 실측 · 교재 07 · 08화

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.47 |     779K|
|   1 |  TABLE ACCESS BY INDEX ROWID| M_CUS     |    752K|      1 |    752K|00:00:00.32 |     774K|
|*  2 |   INDEX UNIQUE SCAN         | PK_M_CUS  |    752K|      1 |    752K|00:00:00.12 |   21011 |
|   3 |  SORT AGGREGATE             |           |      1 |      1 |      1 |00:00:00.47 |     779K|
|   4 |   TABLE ACCESS FULL         | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5826 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUS_ID"=:B1)

(DBMS_XPLAN 이 줄여 쓴 값 — 752K = 752,995 · 779K = 779,832 · 774K = 774,006)

문제

1번과 2번 줄의 Starts 752,995가 어떻게 나왔는지 설명하시오. 개발자가 예상한 2,000과 왜 다른지, 06화의 지역명 스칼라 서브쿼리는 왜 Starts가 8이었는지도 설명하시오.
같은 답을 내는 조인으로 바꾸시오. 이 점검의 목적에 비추어 내부 조인과 외부 조인 중 무엇이어야 하는지, 바꾸기 전에 무엇을 증명해야 하는지 쓰시오.
M_CUS에 PK가 없어 같은 고객번호가 두 줄이라면 스칼라 서브쿼리와 조인이 각각 어떻게 되는지 설명하시오. 스칼라 서브쿼리를 그대로 두는 편이 나은 경우가 있다면 언제인지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 스칼라 → 외부 조인 : 고객 마스터에 없는 주문도 행으로 남아야 '미확인' 으로 셀 수 있다
SELECT COUNT(*)        AS ord_cnt,
       COUNT(c.CUS_NM) AS named_cnt,
       MAX(c.CUS_NM)   AS nm_sample
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID(+) = o.CUS_ID;
-- ANSI : FROM T_ORD_BIG o LEFT JOIN M_CUS c ON c.CUS_ID = o.CUS_ID
-- 전제 : PK_M_CUS (CUS_ID) — 고객번호 하나에 고객은 한 줄 (조인이 주문을 불리지 않는다)

개선 후 실행계획 실측 · 교재 07 · 08화

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |      1 |00:00:00.17 |    5856 |       |       |          |
|   1 |  SORT AGGREGATE        |           |      1 |      1 |      1 |00:00:00.17 |    5856 |       |       |          |
|*  2 |   HASH JOIN RIGHT OUTER|           |      1 |   1000K|   1000K|00:00:00.13 |    5856 |  1423K|  1423K| 1679K (0)|
|   3 |    TABLE ACCESS FULL   | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |       |       |          |
|   4 |    TABLE ACCESS FULL   | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5826 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

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

모범 답안 서술

1번과 2번 줄의 Starts는 752,995다. 주문 100만 행을 지나면서 고객 마스터를 75만 번 찾아갔다는 뜻이다 (1번 줄 774,006블록 중 752,995블록이 M_CUS 테이블 액세스이고, 21,011블록이 PK 인덱스다). 스칼라 서브쿼리 캐시는 입력 값과 결과를 짝지어 적어 두는 메모판이다. 크기가 정해져 있고 값마다 들어갈 칸도 정해져 있어서, 이미 다른 값이 들어 있는 칸에 부딪히면 적지 않는다. 2,000종류 중 자리를 얻은 고객은 495명뿐이었다(752,995 = 495 × 1 + 1,505 × 500). 자리를 못 얻은 1,505명은 나올 때마다 500번 모두 마스터를 찾아갔다. 06화의 지역명은 입력 값이 8종류라서 8칸이 서로 부딪히지 않았을 뿐이다 (07화: NDV가 64만 돼도 6개가 자리를 못 얻는다). 같은 답을 해시 조인으로 내면 M_CUS 2,000행(30블록)을 메모리에 한 번 올리고 주문을 한 번 훑는다. Buffers는 779,832 → 5,856으로 준다. 이 배지는 '고객 마스터에 없는 주문'을 세는 점검이다. 한빛유통 신 시스템은 FK가 0개라 고아 주문이 들어올 수 있고, 스칼라 서브쿼리는 그런 주문에 NULL을 돌려줄 뿐 행은 남긴다. 내부 조인으로 바꾸면 고아 주문이 COUNT(*)에서 사라져 ord_cnt − named_cnt가 언제나 0이 된다. 점검 결과가 거짓이 되는 것이다. 그래서 외부 조인이어야 한다(c.CUS_ID(+) = o.CUS_ID). 바꾸기 전에 증명할 것은 '1 쪽이 하나인가'다. PK_M_CUS가 고객번호 하나에 한 줄만 있음을 보장하므로 조인이 주문을 불리지 않는다. PK가 없어 같은 고객번호가 두 줄이면 스칼라 서브쿼리는 ORA-01427로 멈추지만, 외부 조인은 그 고객의 주문 500건을 1,000건으로 에러 없이 곱한다 (08화: 코드 중복 복사본에서 등급별 건수와 금액이 정확히 두 배가 됐다). 멈춰야 할 SQL이 결과를 두 배로 부풀려 내놓는 것이다. 입력 값의 종류가 적어 메모판에 다 올라가고(06화 M_RGN 8종류 → Starts 8), 1 쪽이 유일하다는 증명이 없어 '틀리면 멈춤'을 지키고 싶을 때는 스칼라 서브쿼리를 남겨도 된다. 다만 Starts가 8이어도 메모판은 100만 번 들여다보므로 CPU는 든다 (07화: 외부 조인의 두 배). 스칼라 서브쿼리 안을 MAX()로 감싸면 옵티마이저가 스스로 외부 조인으로 펼치지만(08화: 5,856), MAX()는 두 줄 중 하나를 에러 없이 골라 중복을 숨기므로 외부 조인과 같은 대가를 치른다.

해설

실측: 원본 계획과 숫자는 교재 07화 실습/02의 [B] 실험(COUNT(*) · MAX(nm))에서 잰 값이다. 입력 종류를 늘려 가며 재 보면 NDV 8 → Starts 8, 64 → 93,808, 2,000 → 752,995다. "종류가 적으면 캐시된다"는 절반만 맞는 규칙이다.
메모판을 키우면: _query_execution_cache_max_size를 8배로 늘리면 752,995 → 197,608이 된다 (07화, SYSDBA 실험). 원인이 칸 수라는 것을 확인하는 실험일 뿐 운영에서 쓸 손잡이가 아니고, 0이 되지도 않는다.
다른 정답: 고아만 세려면 NOT EXISTS(해시 안티 조인)로 써도 일량이 같다. MAX()로 감싸 옵티마이저에게 맡기는 답은 손으로 고치지 않아도 되지만, 앞에서 말한 대가를 리포트에 적어 둔다.
정합성 회귀: 바꾼 뒤 세 숫자(ord_cnt, named_cnt, nm_sample)를 원본과 대조한다. 그리고 고아 경계(마스터에 없는 고객번호를 넣은 복사본)에서 스칼라 서브쿼리, 외부 조인, 내부 조인의 결과를 대조한다. 08화 [C1]에서는 내부 조인만 주문 10,000건을 지웠다(MINUS 3 / 3).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 07 · 08화 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 07 · 08화 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황