한정 메뉴 매장별 판매 보고서는 두 번째로 열면 빠른데 아침 첫 조회만 다섯 배 넘게 느리다

중급SELECT버거643 / 700

상황

버거 본사 마케팅팀은 12월 한정 메뉴 「크리스마스 더블 버거」(I091)의 매장별 판매를 캠페인 내내 같은 보고서로 본다. 보고서 SQL은 기간과 메뉴가 글자로 박혀 있어 캠페인 동안 한 글자도 바뀌지 않는다. 팀원 다섯 명이 하루 열 번쯤 연다. 박팀장이 전했다. "아침에 처음 여는 사람만 0.5초 넘게 기다리고, 그다음부터는 0.1초도 안 걸린답니다. 날마다 그래요." POS 원장 통계는 매일 03:00에 기본값으로 다시 모은다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · 날짜순 적재 · 2013년 12월 41,666건 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS)
ORD_ITEM: 2,472,717행 · 55,265블록 · 날짜순 적재 · 2013년 12월 103,030줄 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS)
이 문제의 ITEM_ID는 'I091'처럼 네 글자다. 한정 메뉴 I091은 2013년 12월에만 팔렸다(8,300줄). ITEM_ID에는 FREQUENCY 히스토그램이 있다(92종).
통계: 매일 03:00 GATHER_SCHEMA_STATS 기본값(무효화는 AUTO_INVALIDATE)

원본 SQL

sql

-- 「한정 메뉴 매장별 판매」 (SQL_ID 3m8qv1x7c2k0p)
SELECT o.SHOP_NO, COUNT(*) AS LINE_CNT, SUM(i.ORD_ITEM_QTY) AS QTY
  FROM ORD_ITEM i, ORD o
 WHERE i.ITEM_ID = 'I091'
   AND i.ORD_DT BETWEEN '20131201' AND '20131231'
   AND o.ORD_NO = i.ORD_NO
   AND o.ORD_DT BETWEEN '20131201' AND '20131231'
 GROUP BY o.SHOP_NO

실행계획 학습용 예시

xplan

[V$SQL · SQL_ID 3m8qv1x7c2k0p · 오늘]
 CHILD  PLAN_HASH_VALUE  IS_REOPTIMIZABLE  EXECUTIONS  BUFFER_GETS  ELAPSED  LAST_LOAD_TIME
     0      1093361258   Y                         1       20,265   0.52초   08:52
     1      4027719634   N                         9       33,363   0.71초   09:10
[V$SQL_SHARED_CURSOR · 자식 1]  USE_FEEDBACK_STATS = Y

[자식 0 · 아침 첫 실행]
Plan hash value: 1093361258

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |    200 |00:00:00.52 |   20265 |   2911 |
|   1 |  HASH GROUP BY                        |              |      1 |     15 |    200 |00:00:00.52 |   20265 |   2911 |
|   2 |   NESTED LOOPS                        |              |      1 |     15 |   8300 |00:00:00.50 |   20265 |   2911 |
|   3 |    NESTED LOOPS                       |              |      1 |    352 |   8300 |00:00:00.41 |   19215 |   2804 |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM    |      1 |    352 |   8300 |00:00:00.06 |    2615 |      0 |
|*  5 |      INDEX RANGE SCAN                 | ORD_ITEM_X01 |      1 |   103K |   103K |00:00:00.01 |     312 |      0 |
|*  6 |     INDEX UNIQUE SCAN                 | ORD_PK       |   8300 |      1 |   8300 |00:00:00.33 |   16600 |   2804 |
|*  7 |    TABLE ACCESS BY INDEX ROWID        | ORD          |   8300 |      1 |   8300 |00:00:00.08 |    1050 |    107 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("I"."ITEM_ID"='I091')
   5 - access("I"."ORD_DT">='20131201' AND "I"."ORD_DT"<='20131231')
   6 - access("O"."ORD_NO"="I"."ORD_NO")
   7 - filter(("O"."ORD_DT">='20131201' AND "O"."ORD_DT"<='20131231'))

[자식 1 · 두 번째 실행부터]
Plan hash value: 4027719634

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |    200 |00:00:00.08 |    3707 |          |
|   1 |  HASH GROUP BY                        |              |      1 |    200 |    200 |00:00:00.08 |    3707 | 1105K (0)|
|*  2 |   HASH JOIN                           |              |      1 |    346 |   8300 |00:00:00.07 |    3707 | 1532K (0)|
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |   8300 |   8300 |00:00:00.04 |    2615 |          |
|*  4 |     INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |   103K |   103K |00:00:00.01 |     312 |          |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD          |      1 |  41666 |  41666 |00:00:00.02 |    1092 |          |
|*  6 |     INDEX RANGE SCAN                  | ORD_X01      |      1 |  41666 |  41666 |00:00:00.01 |     158 |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."ORD_NO"="I"."ORD_NO")
   3 - filter("I"."ITEM_ID"='I091')
   4 - access("I"."ORD_DT">='20131201' AND "I"."ORD_DT"<='20131231')
   6 - access("O"."ORD_DT">='20131201' AND "O"."ORD_DT"<='20131231')

Note
-----
   - statistics feedback used for this statement

문제

같은 SQL이 아침 첫 실행만 느리고 두 번째부터 빠른 이유를 두 자식 커서와 Note로 설명하시오. 다음 날 아침 다시 느려지는 이유와, 자식 0의 4번 줄 E-Rows 352가 실제 8,300과 다른 이유도 쓰시오.
첫 실행부터 빠른 계획으로 돌도록 고치시오. 통계(히스토그램, 컬럼 그룹)로 고쳐지는지와 고른 방법의 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 피드백으로 다시 세운 자식 1의 해시 계획(4027719634) 하나만 받아들인 계획으로 올린다
-- plan_hash_value 를 빼면 자식 0의 NL(1093361258)까지 함께 올라간다
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id          => '3m8qv1x7c2k0p',
                                             plan_hash_value => 4027719634);
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) loaded');
END;
/

-- 캠페인이 끝나 보고서 SQL 이 바뀌면 이 베이스라인은 지운다
-- DECLARE n PLS_INTEGER; BEGIN n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => '&handle'); END;

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

xplan

Plan hash value: 4027719634

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |    200 |00:00:00.08 |    3707 |          |
|   1 |  HASH GROUP BY                        |              |      1 |     15 |    200 |00:00:00.08 |    3707 | 1105K (0)|
|*  2 |   HASH JOIN                           |              |      1 |     15 |   8300 |00:00:00.07 |    3707 | 1532K (0)|
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |    352 |   8300 |00:00:00.04 |    2615 |          |
|*  4 |     INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |   103K |   103K |00:00:00.01 |     312 |          |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD          |      1 |  41666 |  41666 |00:00:00.02 |    1092 |          |
|*  6 |     INDEX RANGE SCAN                  | ORD_X01      |      1 |  41666 |  41666 |00:00:00.01 |     158 |          |
-------------------------------------------------------------------------------------------------------------------------

Note
-----
   - SQL plan baseline "SQL_PLAN_5t0q2c8m1v7k3d9e4a1b" used for this statement

모범 답안 서술

두 자식 커서: 자식 0은 아침 08:52의 첫 실행이 만든 커서다. 4번 줄을 352줄로 보고 NL을 골랐는데 실제로는 8,300줄이었다. 그래서 6번 줄 ORD_PK를 8,300번 찾아가 20,265블록을 읽었다. 첫 실행에서 E-Rows와 A-Rows가 이렇게 크게 어긋나면 오라클은 그 커서에 다시 최적화하라는 표시(IS_REOPTIMIZABLE = Y)를 해 두고, 두 번째 실행 때 실제 건수(8,300)로 계획을 다시 세운다. Statistics Feedback이다. 그렇게 생긴 것이 자식 1(USE_FEEDBACK_STATS = Y, Note statistics feedback used for this statement)이고, 12월 주문을 날짜 인덱스로 한 번 읽어 해시 조인하므로 3,707블록이다. 다음 날 다시 느린 이유: 피드백은 통계에 남는 것이 아니라 그 커서에만 붙은 메모다. 매일 03:00 통계 수집이 커서를 무효화하면(AUTO_INVALIDATE라 몇 시간 안에) 아침 첫 실행이 새로 하드 파싱하면서 메모 없는 자식 0이 다시 생긴다. 공유 풀에서 밀려나도 마찬가지다. 352가 틀린 이유: ITEM_ID 히스토그램은 I091이 8,300줄임을 정확히 안다. 그러나 옵티마이저는 메뉴 조건과 날짜 조건을 서로 상관없다고 보고 곱한다. 8,300 × 31/730 = 352다. 실제로는 이 메뉴가 12월에만 팔렸으므로 날짜 조건은 아무것도 거르지 않는다. 통계로는 고칠 수 없다: 컬럼 그룹은 그룹의 열이 모두 등치(=) 조건일 때만 쓰인다. 날짜는 범위 조건이라 (ITEM_ID, ORD_DT) 그룹을 만들어도 352 그대로다. 히스토그램은 이미 정확하다. 고친 것: 피드백으로 다시 세운 자식 1의 계획을 LOAD_PLANS_FROM_CURSOR_CACHE로 베이스라인에 올린다. plan_hash_value를 꼭 준다. 빼면 커서 캐시에 있는 자식 0의 NL까지 함께 받아들인 계획으로 올라간다. 받아들인 계획이 해시 하나뿐이므로 다음 날 아침 옵티마이저가 352로 NL을 만들어도 해시 계획을 쓴다. 첫 실행도 Buffers 20,265 → 3,707이다. 대가는 보고서 SQL마다 베이스라인을 하나씩 관리해야 한다는 점이다. 캠페인이 끝나 SQL이 바뀌면 지운다.

해설

동적 샘플링 힌트(DYNAMIC_SAMPLING(i 4))는 믿기 어렵다. 수준 4는 한 테이블의 여러 조건을 함께 표본으로 세지만, 표본은 64블록 남짓이다. 12월 줄은 날짜순으로 55,265블록 가운데 2,300블록 안팎에 몰려 있어서 표본에 12월 블록이 거의 걸리지 않는다. 날마다 추정이 흔들린다.
힌트로 정하는 길도 있다. /*+ LEADING(i o) USE_HASH(o) */를 넣으면 보고서 SQL이 바뀌어 새 문장이 되므로 배포가 필요하다. 그리고 다른 메뉴, 다른 기간에 쓰일 때도 해시를 강제한다. 캠페인 한 번의 보고서라 베이스라인이 가볍다.
7번 줄의 o.ORD_DT 조건도 추정을 깎는다. ORD_ITEM과 ORD에 같은 기간을 두 번 걸어서 조인 결과가 352 × 31/730 ≒ 15로 더 작아졌다(2번 줄). ORD를 12월 인덱스로 읽게 해 주는 조건이라 지우지는 않았지만, 같은 날짜를 두 테이블에 거는 SQL은 이렇게 조인 결과를 작게 본다는 것을 알아 둔다.
피드백은 같은 커서를 두 번 이상 쓸 때만 도움이 된다. 하룻밤에 한 번 도는 배치 SQL은 메모가 생겨도 다음 날 쓰이지 않을 수 있다(OPT-060).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황