JOIN-080

힌트로 계획을 고정해 두었다는 「매장 월 상품 판매」가 통계를 새로 모은 주말 뒤로 두 배 넘게 읽는다

중급SELECT유통230 / 700

상황

「매장 월 상품 판매」는 매장 하나와 달 하나를 고르면 그 매장에서 그달 팔린 상품마다 영수증 수와 판매 수량, 금액을 낸다. 점장 여섯 명과 본사 영업팀이 달을 바꿔 가며 하루 300번쯤 연다. 2년 전 선배가 이 SQL에 힌트를 달고 계획이 해시 조인인 것을 확인했다. 힌트 대장에는 "해시 조인으로 고정, 8,682블록"이라고 적혀 있다. 지난 토요일 DBA 강도현이 1년 넘게 갱신되지 않던 판매 테이블들의 통계를 새로 모았다. 월요일부터 이 리포트가 0.17초에서 0.41초로 늘었다. 박팀장은 "힌트를 더 달아서 막을지, 힌트를 떼고 통계와 베이스라인에 맡길지 정해 와라"라고 했다. 아래는 같은 매장의 2023년 6월을 지난주와 이번 주에 돌린 실행계획이다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · IX_CF10_SLE_DT (SLE_DT) 리프 1,393블록 · 날짜순 적재 · 통계는 매주 모아 왔다
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) BLEVEL 2 · 영수증당 평균 2.19줄 · 영수증 번호 순으로 적재
CF11 통계: 지난주까지는 2022년 12월 말에 모은 값(548,120행 · 4,110블록)이었고, 지난 토요일 1,096,708행 · 8,204블록으로 바뀌었다
2023년 6월 판매 20,953건 · 이 매장은 3,492건(판매상품 7,656줄, 상품 104종)
SLE_DT가 VARCHAR2(8)이라 옵티마이저는 6월 한 달을 5,177건으로, 이 매장의 6월을 863건으로 본다(교재 03화)

원본 SQL

sql

SELECT /*+ LEADING(a b) */
       b.ITM_NO,
       COUNT(DISTINCT a.SLE_NO)    AS rcpt_cnt,
       SUM(b.SLE_QTY)              AS sle_qty,
       SUM(b.SLE_QTY * b.SLE_UPRC) AS sle_amt
  FROM CF10 a, CF11 b
 WHERE a.SHP_NO = :v_shp
   AND a.SLE_DT BETWEEN :d1 AND :d2
   AND b.SLE_NO = a.SLE_NO
 GROUP BY b.ITM_NO
 ORDER BY b.ITM_NO

실행계획 학습용 예시

xplan

[지난주 월요일 · 통계 갱신 전]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |    104 |00:00:00.17 |    8682 |          |
|   1 |  SORT GROUP BY                         |                |      1 |    104 |    104 |00:00:00.17 |    8682 |  372K (0)|
|*  2 |   HASH JOIN                            |                |      1 |   1890 |   7656 |00:00:00.16 |    8682 | 1421K (0)|
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |    863 |   3492 |00:00:00.02 |     478 |          |
|*  4 |     INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |   5177 |  20953 |00:00:00.01 |      60 |          |
|   5 |    TABLE ACCESS FULL                   | CF11           |      1 |    548K|   1096K|00:00:00.09 |    8204 |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SLE_NO"="A"."SLE_NO")
   3 - filter("A"."SHP_NO"=:V_SHP)
   4 - access("A"."SLE_DT">=:D1 AND "A"."SLE_DT"<=:D2)

[이번 주 월요일 · 통계 갱신 뒤]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |    104 |00:00:00.41 |   18610 |          |
|   1 |  SORT GROUP BY                         |                |      1 |    104 |    104 |00:00:00.41 |   18610 |  372K (0)|
|   2 |   NESTED LOOPS                         |                |      1 |   1890 |   7656 |00:00:00.40 |   18610 |          |
|   3 |    NESTED LOOPS                        |                |      1 |   1890 |   7656 |00:00:00.22 |   10954 |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    863 |   3492 |00:00:00.02 |     478 |          |
|*  5 |      INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |   5177 |  20953 |00:00:00.01 |      60 |          |
|*  6 |     INDEX RANGE SCAN                   | CF11_PK        |   3492 |      2 |   7656 |00:00:00.15 |   10476 |          |
|   7 |    TABLE ACCESS BY INDEX ROWID         | CF11           |   7656 |      2 |   7656 |00:00:00.17 |    7656 |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("A"."SHP_NO"=:V_SHP)
   5 - access("A"."SLE_DT">=:D1 AND "A"."SLE_DT"<=:D2)
   6 - access("B"."SLE_NO"="A"."SLE_NO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   1 -  SEL$1
         -  LEADING(a b)

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("CF10"."SLE_DT"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "A"@"SEL$1")
      INDEX(@"SEL$1" "B"@"SEL$1" ("CF11"."SLE_NO" "CF11"."ITM_NO"))
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_NL(@"SEL$1" "B"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "B"@"SEL$1")
      END_OUTLINE_DATA
  */

문제

힌트가 있는데도 계획이 바뀐 이유를 Hint Report와 Outline Data로 설명하시오. 통계 갱신이 옵티마이저의 계산을 어떻게 바꿨는지 E-Rows와 CF11의 통계로 쓰시오.
이 블록의 계획이 통계에 흔들리지 않게 하는 두 길(빈틈없는 힌트 묶음, 힌트를 떼고 통계와 베이스라인에 맡기기)을 비교해 하나를 고르고 SQL로 쓰시오. USE_HASH(b) 하나만 더하면 왜 모자란지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 이 블록에서 옵티마이저가 정하는 것을 모두 적는다 : 순서 · 방식 · build 쪽 · 테이블마다의 접근 경로
SELECT /*+ LEADING(a b) USE_HASH(b) NO_SWAP_JOIN_INPUTS(b)
           INDEX(a IX_CF10_SLE_DT) FULL(b) */
       b.ITM_NO,
       COUNT(DISTINCT a.SLE_NO)    AS rcpt_cnt,
       SUM(b.SLE_QTY)              AS sle_qty,
       SUM(b.SLE_QTY * b.SLE_UPRC) AS sle_amt
  FROM CF10 a, CF11 b
 WHERE a.SHP_NO = :v_shp
   AND a.SLE_DT BETWEEN :d1 AND :d2
   AND b.SLE_NO = a.SLE_NO
 GROUP BY b.ITM_NO
 ORDER BY b.ITM_NO
-- 다른 길 : 소스를 못 고치면 위 계획을 지금 문장의 SQL 핸들에 베이스라인으로 받아들인다 (DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE)
-- 대장 : 전제 「매장 한 달 약 3,500건 · CF11 8,204블록」, 재검토 「매장 한 달 2,000건 미만 · CF11 16,000블록 초과 · 6개월」

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

xplan

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |    104 |00:00:00.17 |    8682 |          |
|   1 |  SORT GROUP BY                         |                |      1 |    104 |    104 |00:00:00.17 |    8682 |  372K (0)|
|*  2 |   HASH JOIN                            |                |      1 |   1890 |   7656 |00:00:00.16 |    8682 | 1421K (0)|
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |    863 |   3492 |00:00:00.02 |     478 |          |
|*  4 |     INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |   5177 |  20953 |00:00:00.01 |      60 |          |
|   5 |    TABLE ACCESS FULL                   | CF11           |      1 |   1096K|   1096K|00:00:00.09 |    8204 |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SLE_NO"="A"."SLE_NO")
   3 - filter("A"."SHP_NO"=:V_SHP)
   4 - access("A"."SLE_DT">=:D1 AND "A"."SLE_DT"<=:D2)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 5
---------------------------------------------------------------------------

   1 -  SEL$1
         -  LEADING(a b)

   3 -  SEL$1 / "A"@"SEL$1"
         -  INDEX(a IX_CF10_SLE_DT)

   5 -  SEL$1 / "B"@"SEL$1"
         -  FULL(b)
         -  NO_SWAP_JOIN_INPUTS(b)
         -  USE_HASH(b)

모범 답안 서술

Hint Report에 찍힌 힌트는 LEADING(a b) 하나뿐이다. 그런데 이번 주 계획의 Outline Data를 보면 옵티마이저가 정한 것은 훨씬 많다. a를 IX_CF10_SLE_DT로 읽는 것(INDEX_RS_ASC), b를 NL로 붙이는 것(USE_NL), b를 CF11_PK로 찾는 것(INDEX)은 모두 힌트가 아니라 옵티마이저의 선택이다. LEADING(a b)는 조인 순서만 고정했고, 조인 방식과 접근 경로는 비어 있었다. 2년 전 계획이 해시였던 것은 힌트 덕이 아니라, 그때 통계로는 해시가 더 싸게 계산됐기 때문이다. 옵티마이저 눈에 해시의 값은 CF11을 한 번 다 읽는 블록 수이고, NL의 값은 바깥 건수 × 한 건마다 찾아가는 블록 수(인덱스 3블록과 테이블 약 2.19블록)다. 바깥 건수는 문자 날짜 때문에 863건으로, 실제 3,492건의 4분의 1로 본다(4번 줄 E-Rows). 그래서 NL은 약 478 + 863 × 5.19 ≈ 4,960블록으로 계산된다. 지난주까지 CF11 통계는 548,120행, 4,110블록이라 해시가 약 4,590블록으로 조금 쌌다. 통계 갱신으로 CF11이 4,110블록에서 8,204블록이 되자 해시는 약 8,680블록이 되었고, 이번에는 NL이 싸 보였다. 실제로는 6번 줄 Starts가 3,492이고 Buffers가 18,610이다. USE_HASH(b) 하나만 더해도 이번 회귀는 막힌다. 하지만 빈자리가 여전히 남는다. 해시 조인에서는 옵티마이저가 두 입력을 맞바꿔 build를 정할 수 있어서, CF11 통계가 다시 작게 틀어지면(JOIN-004의 사고) 판매상품 쪽이 build가 될 수 있다. CF10을 인덱스로 읽을지 FULL로 읽을지도 비어 있다. 이 블록에서 옵티마이저가 정하는 것은 조인 순서, 조인 방식, build 쪽, 테이블마다의 접근 경로다. 통계에 흔들리지 않으려면 이것을 모두 적어야 하고, Outline Data가 그 목록이다. 길은 둘이다. 첫째는 네 가지를 모두 적은 힌트 묶음 LEADING(a b) USE_HASH(b) NO_SWAP_JOIN_INPUTS(b) INDEX(a IX_CF10_SLE_DT) FULL(b)이다. 소스에서 계획이 보이고, 통계가 바뀌어도 계획은 그대로다. 대가는 데이터가 바뀌어도 그대로라는 점이다. 해시의 값은 CF11 전체 크기를 따라 커지고 NL의 값은 매장 한 달 건수를 따르므로, 분기점(지금 약 1,580건)은 CF11이 커질수록 올라간다. 그래서 대장에 전제와 재검토 조건을 숫자로 적어야 한다. 둘째는 힌트를 떼고 통계와 베이스라인에 맡기는 길이다. 그런데 힌트를 떼면 지금 통계로는 NL을 고른다. 통계가 틀린 것이 아니라 문자 날짜의 추정이 틀렸기 때문이다. 통계를 지난주 값으로 되돌려 잠그면 판매상품을 읽는 다른 SQL까지 옛 크기로 계산된다. 해시 계획을 베이스라인으로 받아들이면 소스를 고치지 않고도 고정할 수 있지만, 베이스라인은 SQL 글자의 서명에 붙어 글자가 바뀌면 따라오지 않고(OPT-042), DBA가 따로 관리해야 한다. 추정 자체를 고치는 가상 컬럼은 운영 INSERT를 깨뜨리는 대가가 있다(교재 03화). 이 리포트는 소스를 고칠 수 있고, 어느 매장 어느 달이든 맞는 계획이 해시 하나다(매장 한 달 약 3,500건, 분기점 약 1,580건). 그래서 첫째 길을 고른다. Buffers는 18,610 → 8,682, 시간은 0.41 → 0.17초로 돌아온다. 대장에는 "매장 한 달 영수증 2,000건 미만(매장이 늘 때), CF11 16,000블록 초과, 또는 6개월"을 재검토 조건으로 적는다.

해설

Outline이 빈틈없는 힌트 묶음이다: DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST +OUTLINE')의 Outline Data에는 그 계획을 다시 만드는 데 필요한 지시가 모두 들어 있다. 내가 단 힌트와 Outline을 견주면 무엇이 비어 있는지 보인다. 베이스라인도 이 묶음을 저장해 두는 장치다.
힌트 묶음에서 자주 빠지는 것: 해시 조인의 build 쪽(SWAP_JOIN_INPUTS, NO_SWAP_JOIN_INPUTS), NL 안쪽의 인덱스, 드라이빙 테이블의 접근 경로다. LEADING과 USE_HASH만으로는 build가 고정되지 않는다.
다른 정답: 베이스라인: 소스를 고칠 수 없거나 배포가 멀면 둘째 길이 맞다. 완전한 힌트를 단 판으로 한 번 돌려 커서 캐시에 올린 뒤, 그 계획을 운영 문장의 SQL 핸들에 받아들인다(DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE). 나중에 소스의 LEADING(a b)를 떼는 배포를 하면 글자가 바뀌므로, 뗀 글자에 다시 받아들여야 한다(OPT-042).
힌트를 떼기만 해서는 안 된다: 이 회귀는 통계가 맞아진 뒤에 생겼다. 원인은 문자 날짜 범위의 추정이 실제의 4분의 1이라는 데 있다. 힌트를 떼고 옵티마이저에 맡기라는 06화의 결론은 옵티마이저가 건수를 제대로 볼 때 맞는 말이다 (교재 06화 §7, 03화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황