VIP 취소 주문 상황판은 늘 비어 있는데 1분마다 4만 블록을 읽는다

실전SELECT한빛유통622 / 700

상황

고객관리팀은 VIP(A 등급) 고객의 취소를 이탈 징후로 본다. 상황판 다섯 대가 「VIP 최근 취소 20건」을 1분마다 새로 고친다. 지금까지 VIP의 취소는 한 건도 없어서 화면은 늘 비어 있는데, AWR에서 이 SQL이 Buffer Gets 2위다. 정하은이 물었다. "지난번 취소 정산 점검처럼 컬럼 그룹을 만들어서 두 열의 관계를 알려 주면 되지 않을까요?" 박팀장은 고칠 방법을 둘 이상 비교해서 고르라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_ST COMPLETE 900,000 · PENDING 80,000 · CANCEL 20,000(FREQUENCY 히스토그램, 정책서 1호) · 매주 일요일에 인덱스를 단 채 재적재한다
취소는 고객 40명(모두 C 등급)에게서만 나온다. A와 B 등급 고객의 주문은 전부 COMPLETE다.
인덱스: IX_ORD_BIG_ST_DT (ORD_ST, ORD_DT): BLEVEL 2 · 리프 약 3,300 / IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT): BLEVEL 2 · 리프 약 3,400 · 행이 흩어져 있어 두 인덱스 모두 CF가 100만에 가깝다
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD A 20 · B 180 · C 1,800(FREQUENCY 히스토그램) · 고객당 주문 500건

원본 SQL

sql

SELECT *
  FROM (SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_AMT, c.CUS_ID, c.CUS_NM
          FROM T_ORD_BIG o, M_CUS c
         WHERE o.ORD_ST = 'CANCEL'
           AND c.CUS_GD = 'A'
           AND c.CUS_ID = o.CUS_ID
         ORDER BY o.ORD_DT DESC)
 WHERE ROWNUM <= 20

실행계획 학습용 예시 · 고객과 상태의 관계는 교재 10화의 적재 데이터 그대로

xplan

--------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |      1 |        |      0 |00:00:00.12 |   40489 |
|*  1 |  COUNT STOPKEY                   |                  |      1 |        |      0 |00:00:00.12 |   40489 |
|   2 |   VIEW                           |                  |      1 |     20 |      0 |00:00:00.12 |   40489 |
|   3 |    NESTED LOOPS                  |                  |      1 |     20 |      0 |00:00:00.12 |   40489 |
|   4 |     NESTED LOOPS                 |                  |      1 |   2000 |  20000 |00:00:00.07 |   20489 |
|   5 |      TABLE ACCESS BY INDEX ROWID | T_ORD_BIG        |      1 |  20000 |  20000 |00:00:00.05 |   20069 |
|*  6 |       INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_ST_DT |      1 |   2000 |  20000 |00:00:00.01 |      69 |
|*  7 |      INDEX UNIQUE SCAN           | PK_M_CUS         |  20000 |      1 |  20000 |00:00:00.01 |     420 |
|*  8 |     TABLE ACCESS BY INDEX ROWID  | M_CUS            |  20000 |      1 |      0 |00:00:00.04 |   20000 |
--------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=20)
   6 - access("O"."ORD_ST"='CANCEL')
   7 - access("C"."CUS_ID"="O"."CUS_ID")
   8 - filter("C"."CUS_GD"='A')

문제

옵티마이저가 취소 인덱스를 거꾸로 읽는 계획을 고른 이유를 조인 결과의 E-Rows를 손으로 계산해 설명하고, 그 계획이 실제로 취소 2만 건을 끝까지 읽은 이유를 쓰시오.
정하은의 제안이 이 SQL에 통하지 않는 이유를 쓰고, 고칠 방법을 둘 이상 비교해 하나를 골라 SQL이나 DDL로 쓰시오.
고른 방법의 대가와, 언젠가 VIP 취소가 생겼을 때 이 계획이 어떻게 되는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객에서 출발해 '그 고객의 취소'만 바로 찾는 인덱스
--   독립 가정 그대로(A 고객 20명 × 고객당 취소 10건 = 200건 추정)여도 이 길이 싸게 계산된다
CREATE INDEX IX_ORD_BIG_CUS_ST_DT ON T_ORD_BIG (CUS_ID, ORD_ST, ORD_DT) INVISIBLE;

-- 이 세션에서만 보이게 해서 계획과 Buffers 를 확인한 뒤 연다
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;
ALTER INDEX IX_ORD_BIG_CUS_ST_DT VISIBLE;

-- SQL 은 그대로
SELECT *
  FROM (SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_AMT, c.CUS_ID, c.CUS_NM
          FROM T_ORD_BIG o, M_CUS c
         WHERE o.ORD_ST = 'CANCEL'
           AND c.CUS_GD = 'A'
           AND c.CUS_ID = o.CUS_ID
         ORDER BY o.ORD_DT DESC)
 WHERE ROWNUM <= 20;

개선 후 실행계획 학습용 예시 · 고객과 상태의 관계는 교재 10화의 적재 데이터 그대로

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |      1 |        |      0 |00:00:00.01 |      90 |          |
|*  1 |  COUNT STOPKEY                 |                      |      1 |        |      0 |00:00:00.01 |      90 |          |
|   2 |   VIEW                         |                      |      1 |     20 |      0 |00:00:00.01 |      90 |          |
|*  3 |    SORT ORDER BY STOPKEY       |                      |      1 |    200 |      0 |00:00:00.01 |      90 | 1024  (0)|
|   4 |     NESTED LOOPS               |                      |      1 |    200 |      0 |00:00:00.01 |      90 |          |
|   5 |      NESTED LOOPS              |                      |      1 |    200 |      0 |00:00:00.01 |      90 |          |
|*  6 |       TABLE ACCESS FULL        | M_CUS                |      1 |     20 |     20 |00:00:00.01 |      30 |          |
|*  7 |       INDEX RANGE SCAN         | IX_ORD_BIG_CUS_ST_DT |     20 |     10 |      0 |00:00:00.01 |      60 |          |
|   8 |      TABLE ACCESS BY INDEX ROWID| T_ORD_BIG           |      0 |      1 |      0 |00:00:00.01 |       0 |          |
---------------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   6 - filter("C"."CUS_GD"='A')
   7 - access("O"."CUS_ID"="C"."CUS_ID" AND "O"."ORD_ST"='CANCEL')

모범 답안 서술

왜 그 계획을 골랐나: 두 조건을 따로 보면 둘 다 정확하다. 취소는 히스토그램으로 20,000건, A 등급은 20명(1%)이다. 그러나 둘이 조인으로 엮이면 옵티마이저는 서로 상관없다고 보고 곱한다. 조인 결과는 20,000 × 20 / 2,000 = 200건이고, 취소 주문 100건 가운데 1건이 A 고객의 것이라고 본 셈이다. 그렇다면 취소 인덱스를 최근 날짜부터 거꾸로 읽으며 고객을 대어 보다가 2,000건쯤 읽으면 20건이 찬다. 고객 20명의 주문 1만 건을 다 모아 정렬하는 것보다 싸므로 6번 줄의 INDEX RANGE SCAN DESCENDING을 골랐다. 그러나 실제 취소는 고객 40명(모두 C 등급)에게서만 나와서 VIP 취소는 0건이다. 20건이 영영 차지 않으니 STOPKEY는 멈추지 못하고 취소 2만 건을 끝까지 읽었다. 주문 테이블 20,000번, 고객 테이블 20,000번을 찾아가 40,489블록을 읽고 빈 화면을 띄운다. 상황판 다섯 대가 1분마다 그렇게 한다. 정하은의 제안이 안 되는 이유: 컬럼 그룹은 한 테이블의 열끼리만 묶는다. 등급은 M_CUS에, 상태는 T_ORD_BIG에 있어서 이 관계는 조인 너머에 있다. 히스토그램도 열 하나의 분포라 알려 줄 수 없다. 지난번 취소 정산 점검(OPT-003)은 고객과 상태가 같은 테이블에 있어서 컬럼 그룹이 통했다. 고칠 방법 비교: ① LEADING(c o) USE_NL(o) INDEX(o IX_ORD_BIG_CUS_DT) 힌트는 고객 20명에서 출발하게 하지만, 지금 인덱스로는 고객마다 주문 500건(모두 1만 건)을 테이블에서 확인해야 해서 약 1만 블록을 읽고, 판단을 코드에 굳힌다. ② CUS_GD를 주문 테이블에 반정규화하면 (CUS_GD, ORD_ST) 컬럼 그룹을 만들 수 있지만, 등급이 바뀔 때마다 주문 수백 건을 고쳐야 한다. ③ SQL Plan Directive를 쓰는 적응형 통계(optimizer_adaptive_statistics)는 기본값이 꺼져 있고, 켜면 시스템 전체에 영향을 준다. ④ (CUS_ID, ORD_ST, ORD_DT) 인덱스를 만들면 고객 20명마다 '그 고객의 취소'만 바로 찾는다. 독립 가정 그대로(고객당 취소 10건, 모두 200건)여도 이 길의 비용이 20 × (3 + 10)으로 작게 계산되므로 옵티마이저가 스스로 고른다. 추정을 고치지 못해도 추정이 틀릴 때 덜 다치는 계획이 되는 것이다. 고른 것: ④다. INVISIBLE로 만들어 세션에서 확인한 뒤 연다. 고객 테이블 30블록과 인덱스 60블록, 모두 90블록이고(40,489 → 90), VIP 취소가 생겨도 고객 20명의 취소 몇 건을 더 읽을 뿐이라 계획은 그대로 싸다. 대가: 인덱스가 넷이 되어 일요일 재적재(인덱스를 단 채 일반 INSERT)와 평소 주문 INSERT가 그만큼 무거워진다(14화). 새 인덱스는 ORD_ST가 가운데 있어 '고객의 최근 주문' 정렬을 대신하지 못하므로 (CUS_ID, ORD_DT) 인덱스는 남긴다. 재적재 시간을 다시 재고, 정책서에 이 인덱스를 만든 이유(조인 너머의 상관)를 적는다.

해설

Statistics Feedback: 첫 실행에서 E-Rows와 A-Rows가 크게 어긋나면 다음 실행에서 계획을 다시 세울 수도 있다. 그러나 그 커서에만 남는 메모라 커서가 밀려나면 처음부터 다시 틀린다(교재 10화 §10).
반대 상황도 있다. 취소가 많은 C 등급을 같은 SQL로 물으면 취소 인덱스를 거꾸로 읽는 계획이 금방 멈춰서 오히려 빠르다. 값마다 맞는 계획이 다른 SQL이라 바인드로 바꾸면 첫 값이 계획을 정한다(11화).
상황판이 비어 있는 것이 정상인 화면은 요구도 다시 볼 만하다. 예를 들어 VIP 취소가 생길 때 알림을 보내는 방식이면 1분마다 묻지 않아도 된다.
지금 데이터는 CUS_ID와 ORD_ST 사이에도 관계가 있다(고객마다 상태가 하나). 새 인덱스의 (CUS_ID, ORD_ST) 접두는 그 관계 때문에 고객마다 한 상태에 몰려 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 고객과 상태의 관계는 교재 10화의 적재 데이터 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 고객과 상태의 관계는 교재 10화의 적재 데이터 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황