취소 주문 정산 점검은 취소가 많은 고객일수록 느리다

중급SELECT한빛유통593 / 700

상황

정산팀의 「취소 주문 정산 점검」 화면은 고객 번호를 넣으면 그 고객의 취소 주문 가운데 정산에 들어간 주문을 보여 준다(취소는 정산되면 안 된다). 조민우가 말했다. "취소가 많은 고객일수록 느려요. 상담 중에 수십 번 누르는 화면이에요." 6월에는 T_ORD의 상태별 조회 화면 때문에 ORD_ST에 히스토그램을 걸었다(10화 정책서 1호와 같은 모양의 선호도).

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · 고객 2,000명 × 50건 · ORD_ST COMPLETE 90,000 · PENDING 8,000 · CANCEL 2,000(FREQUENCY 히스토그램) · CUS_ID는 히스토그램 없음
이 데이터는 고객마다 상태가 하나뿐이다. (CUS_ID, ORD_ST) 조합은 2,000종으로 고객 수와 같고, 취소 고객 40명의 주문 50건은 전부 CANCEL이다.
T_ORD_STLM: 100,000행 · 640블록 · T_ORD의 주문마다 한 건(ORD_SEQ) · 인덱스 없음(매일 밤 비우고 다시 싣는다)
확장 통계 없음 · 선호도 FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ORD_ST

원본 SQL

sql

SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_AMT, s.STLM_SEQ, s.STLM_DT, s.STLM_AMT
  FROM T_ORD o, T_ORD_STLM s
 WHERE o.CUS_ID = :cus
   AND o.ORD_ST = 'CANCEL'
   AND s.ORD_SEQ = o.ORD_SEQ

실행계획 학습용 예시 · 교재 10화의 상관 컬럼

xplan

[:cus = 'CUS_000051' (취소 고객) · 그날 아침 첫 조회로 태어난 자식 0 을 그대로 쓴다]
------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |     50 |00:00:00.17 |   32896 |
|   1 |  NESTED LOOPS      |            |      1 |      1 |     50 |00:00:00.17 |   32896 |
|*  2 |   TABLE ACCESS FULL| T_ORD      |      1 |      1 |     50 |00:00:00.01 |     896 |
|*  3 |   TABLE ACCESS FULL| T_ORD_STLM |     50 |      1 |     50 |00:00:00.16 |   32000 |
------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - :CUS (VARCHAR2(32), CSID=873): 'CUS_000002'

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("O"."CUS_ID"=:CUS AND "O"."ORD_ST"='CANCEL'))
   3 - filter("S"."ORD_SEQ"="O"."ORD_SEQ")

[USER_TAB_COL_STATISTICS — T_ORD]
 COLUMN_NAME   NUM_DISTINCT  HISTOGRAM
 CUS_ID                2000  NONE
 ORD_ST                   3  FREQUENCY   (CANCEL 2,000 · COMPLETE 90,000 · PENDING 8,000)

문제

2번 줄의 E-Rows 1을 손으로 계산하고, 실제 50과 왜 다른지 데이터로 설명하시오. 그 1 때문에 계획에서 무엇을 선택했는지도 쓰시오.
SQL은 고치지 않고 통계로 E-Rows를 바로잡으시오. 처음 만든 통계가 쓰이지 않는 함정이 하나 있다. 힌트나 인덱스로 푸는 방법과도 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 두 열을 한 묶음(컬럼 그룹)으로 — 조합 2,000종 = 고객 수 (숨은 가상 열 하나 · 인덱스가 아니다)
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'T_ORD', '(CUS_ID, ORD_ST)') AS ext FROM dual;

-- ② ORD_ST 에 히스토그램이 있으므로 그룹에도 히스토그램 (없으면 그룹을 무시한다)
--    조합 2,000종이 다 들어가는 FREQUENCY — 선호도로 걸어 밤의 수집도 같게
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(USER, 'T_ORD', 'METHOD_OPT',
    'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ORD_ST FOR COLUMNS SIZE 2048 (CUS_ID, ORD_ST)');
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD', no_invalidate => FALSE);
END;
/

-- SQL 은 그대로
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_AMT, s.STLM_SEQ, s.STLM_DT, s.STLM_AMT
  FROM T_ORD o, T_ORD_STLM s
 WHERE o.CUS_ID = :cus
   AND o.ORD_ST = 'CANCEL'
   AND s.ORD_SEQ = o.ORD_SEQ;

개선 후 실행계획 학습용 예시 · 교재 10화의 상관 컬럼

xplan

-----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |     50 |00:00:00.02 |    1536 |          |
|*  1 |  HASH JOIN         |            |      1 |     50 |     50 |00:00:00.02 |    1536 | 1152K (0)|
|*  2 |   TABLE ACCESS FULL| T_ORD      |      1 |     50 |     50 |00:00:00.01 |     896 |          |
|   3 |   TABLE ACCESS FULL| T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     640 |          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("S"."ORD_SEQ"="O"."ORD_SEQ")
   2 - filter(("O"."CUS_ID"=:CUS AND "O"."ORD_ST"='CANCEL'))

모범 답안 서술

손으로 계산하면 100,000 × 1/2,000(CUS_ID) × 2,000/100,000(ORD_ST 히스토그램의 CANCEL 2%) = 1이다. 옵티마이저는 두 조건이 서로 상관없다고 보고(독립 가정) 선택도를 곱했다. 그러나 이 데이터는 고객마다 상태가 하나뿐이다. 취소 고객의 주문 50건은 전부 CANCEL이므로, 실제 행수는 고객 조건 하나의 몫(1/2,000)인 50이다. 히스토그램이 CANCEL을 정확히 2%로 알려 주자 곱셈은 더 정밀하게 틀렸다(히스토그램이 없으면 17). 1행이라고 믿은 옵티마이저는 NL을 골랐다. 안쪽 T_ORD_STLM에는 ORD_SEQ 인덱스가 없어서 바깥 한 행마다 테이블 전체를 읽는다. 3번 줄의 Starts 50 × 640 = 32,000블록이다. 취소가 없는 고객은 바깥 행이 0이라 빠르고, 취소 고객만 느리다. 그래서 열과 열 사이의 관계를 통계에 알려 준다. CREATE_EXTENDED_STATS로 (CUS_ID, ORD_ST) 컬럼 그룹을 만들고 다시 모은다. 여기에 함정이 하나 있다. ORD_ST에 히스토그램이 있는데 그룹에는 없으면 오라클은 그룹을 무시한다(여전히 1). 그러므로 그룹에도 히스토그램(조합 2,000종이 다 들어가는 FREQUENCY, SIZE 2048)을 준다. 그러면 E-Rows가 50이 되고 HASH JOIN을 타서 Buffers가 32,896 → 1,536으로 준다. 선호도에 걸어 두면 밤의 수집도 같은 규칙으로 모은다. 다른 방법도 있다. USE_HASH 힌트는 이 문장만 고치고 통계는 틀린 채 남겨 둔다(그날 규모에 맞춘 판단, 06화). T_ORD_STLM (ORD_SEQ) 인덱스를 만들면 NL이 싸지지만(약 1,100블록), 매일 밤 10만 건을 다시 싣는 테이블에 적재 비용이 더해진다(14화). ORD_ST 히스토그램을 지우면 추정이 17이 되어 해시로 가지만, 그 히스토그램이 필요했던 상태별 조회 화면이 값마다 맞는 계획을 잃는다.

해설

Statistics Feedback: 그날 첫 조회가 취소 고객이면 두 번째 실행부터 실제 행수로 계획을 다시 세운다(10화: 10 → 500). 그러나 그 커서에 남는 메모일 뿐이고, 첫 손님은 대부분 취소가 없는 고객이라 메모가 생기지 않는다. 위 계획도 그렇게 만들어졌다(Peeked Binds 'CUS_000002').
없는 조합: 그룹 FREQUENCY는 없는 조합(취소 없는 고객 + CANCEL)을 가장 적은 값의 절반(25)으로 본다. 실제로는 0이지만 해시로 가도 일은 작다.
바인드 + 그룹 히스토그램: 이제 엿본 :cus 값마다 추정이 달라진다. 값에 민감한 커서가 되므로 첫 값이 계획을 정할 수 있다(PARSE-002).
조인 너머의 상관은 알려 줄 수 없다. 이 데이터에서 A, B 등급 고객의 주문은 전부 COMPLETE다(M_CUS와 T_ORD 사이의 상관). 컬럼 그룹은 한 테이블의 열끼리만 묶는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 10화의 상관 컬럼 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 10화의 상관 컬럼 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황