월 상품 판매 집계 배치 안에서만 같은 SELECT가 블록 17만 개를 읽는다

중급PLSQL버거626 / 700

상황

매달 1일 새벽 PKG_MON.P_ITEM_SUM이 지난달 상품별 판매 수량을 MON_ITEM_SUM에 싣는다. 조민우가 이 INSERT의 SELECT 부분만 떼어 SQL Developer에서 돌리면 56,336블록인데, 배치 로그의 같은 문장은 168K 블록을 읽었다. 조민우가 배치와 같은 세션 조건에서 10053 트레이스를 떴다. 패키지 맨 앞 두 줄은 2015년에 들어갔고, 커밋 메시지에는 "인덱스 우선 튜닝 가이드 적용"이라고만 적혀 있다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · ORD_X01 (ORD_DT, ORD_HMS) 리프 3,774 · 클러스터링 팩터 22,223(날짜순 적재) · 한 달 약 41,700건
ORD_DT에는 HYBRID 히스토그램이 있어 한 달 범위의 E-Rows가 실제와 비슷하다.
ORD_ITEM: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) 높이 3 · 주문당 2.47줄(한 주문의 줄은 같은 블록에 있다)
MON_ITEM_SUM (YM, ITEM_ID, QTY): 이 배치가 쓰는 월 집계 테이블 · 달마다 92행 · 인덱스 없음
인스턴스 파라미터는 기본값(optimizer_index_cost_adj 100, optimizer_index_caching 0) · 통계 최신

원본 SQL

sql

PROCEDURE P_ITEM_SUM(p_ym VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1';    -- 2015 인덱스 우선 튜닝 가이드
  EXECUTE IMMEDIATE 'ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 100';
  DELETE FROM MON_ITEM_SUM WHERE YM = p_ym;
  INSERT INTO MON_ITEM_SUM (YM, ITEM_ID, QTY)
  SELECT p_ym, i.ITEM_ID, SUM(i.ORD_ITEM_QTY)
    FROM ORD o, ORD_ITEM i
   WHERE o.ORD_DT BETWEEN p_ym || '01' AND p_ym || '31'
     AND i.ORD_NO = o.ORD_NO
   GROUP BY i.ITEM_ID;
  COMMIT;
END;

실행계획 학습용 예시 · 10053은 필요한 줄만 옮겼다

xplan

[배치 안의 INSERT — p_ym = '201311']
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                        |              |      1 |        |      0 |00:00:01.38 |     168K|
|   1 |  LOAD TABLE CONVENTIONAL                | MON_ITEM_SUM |      1 |        |      0 |00:00:01.38 |     168K|
|   2 |   HASH GROUP BY                         |              |      1 |     92 |     92 |00:00:01.37 |     168K|
|*  3 |    FILTER                               |              |      1 |        |    103K|00:00:01.34 |     168K|
|   4 |     NESTED LOOPS                        |              |      1 |    103K|    103K|00:00:01.33 |     168K|
|   5 |      NESTED LOOPS                       |              |      1 |    103K|    103K|00:00:00.71 |     126K|
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| ORD         |      1 |  41700 |  41712 |00:00:00.03 |    1071 |
|*  7 |        INDEX RANGE SCAN                 | ORD_X01      |      1 |  41700 |  41712 |00:00:00.01 |     139 |
|*  8 |       INDEX RANGE SCAN                  | ORD_ITEM_PK  |  41712 |      2 |    103K|00:00:00.40 |     125K|
|   9 |      TABLE ACCESS BY INDEX ROWID        | ORD_ITEM     |    103K|      1 |    103K|00:00:00.36 |   41712 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:B1||'01'<=:B1||'31')
   7 - access("O"."ORD_DT">=:B1||'01' AND "O"."ORD_DT"<=:B1||'31')
   8 - access("I"."ORD_NO"="O"."ORD_NO")

[10053 — 배치와 같은 세션 조건 · 필요한 줄만]
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
optimizer_index_cost_adj            = 1
optimizer_index_caching             = 100
...
NL Join
  Outer table: Card: 41700.00  Cost: 11.21  Resp: 11.21  Degree: 1
  Inner table: ORD_ITEM  Alias: I
  Access Path: index (RangeScan)
    Index: ORD_ITEM_PK
    resc_io: 0.03  resc_cpu: 21934
  NL Join : Cost: 1262.48  Resp: 1262.48  Degree: 1
HA Join
  HA cost: 15409.66
Best:: JoinMethod: NestedLoop
       Cost: 1262.48  Degree: 1  Resp: 1262.48  Card: 102999.00

[같은 SELECT — SQL Developer 세션(파라미터 기본값)]  HASH JOIN · ORD_ITEM FULL · Buffers 56,336 · 0.61초
  (10053 : NL Join Cost 167,9xx · HA cost 16,4xx → HASH)
(126K = 126,207 · 125K = 125,136 · 168K = 167,919)

문제

10053의 어느 부분에서 배치 세션만 다른 계획을 세운 이유를 찾을 수 있는지 쓰고, 두 파라미터가 NL과 해시 조인의 비용을 각각 어떻게 바꿨는지 설명하시오.
배치를 고치고 그 효과를 숫자로 쓰시오. 이런 세션 설정을 남겨 두면 안 되는 이유도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_ITEM_SUM(p_ym VARCHAR2) IS
BEGIN
  -- 2015 '인덱스 우선 튜닝 가이드'의 세션 설정 두 줄을 뺐다 (2024-12 재검토 · 비용을 깎으면 일은 그대로다)
  DELETE FROM MON_ITEM_SUM WHERE YM = p_ym;
  INSERT INTO MON_ITEM_SUM (YM, ITEM_ID, QTY)
  SELECT p_ym, i.ITEM_ID, SUM(i.ORD_ITEM_QTY)
    FROM ORD o, ORD_ITEM i
   WHERE o.ORD_DT BETWEEN p_ym || '01' AND p_ym || '31'
     AND i.ORD_NO = o.ORD_NO
   GROUP BY i.ITEM_ID;
  COMMIT;
END;

개선 후 실행계획 학습용 예시 · 10053은 필요한 줄만 옮겼다

xplan

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                        |              |      1 |        |      0 |00:00:00.62 |   56339 |          |
|   1 |  LOAD TABLE CONVENTIONAL                | MON_ITEM_SUM |      1 |        |      0 |00:00:00.62 |   56339 |          |
|   2 |   HASH GROUP BY                         |              |      1 |     92 |     92 |00:00:00.62 |   56336 |          |
|*  3 |    FILTER                               |              |      1 |        |    103K|00:00:00.55 |   56336 |          |
|*  4 |     HASH JOIN                           |              |      1 |    103K|    103K|00:00:00.54 |   56336 | 2671K (0)|
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD          |      1 |  41700 |  41712 |00:00:00.03 |    1071 |          |
|*  6 |       INDEX RANGE SCAN                  | ORD_X01      |      1 |  41700 |  41712 |00:00:00.01 |     139 |          |
|   7 |      TABLE ACCESS FULL                  | ORD_ITEM     |      1 |   2472K|   2472K|00:00:00.31 |   55265 |          |
----------------------------------------------------------------------------------------------------------------------

모범 답안 서술

10053 맨 위의 PARAMETERS WITH ALTERED VALUES(Compilation Environment)에 기본값과 다른 두 줄 optimizer_index_cost_adj = 1, optimizer_index_caching = 100이 있다. 패키지 첫머리의 ALTER SESSION 두 줄이다. SQL Developer 세션에는 이 줄이 없으므로 같은 SELECT라도 비용 계산이 달라진다. OPTIMIZER_INDEX_COST_ADJ = 1은 인덱스로 가는 경로의 비용을 1%로 친다. OPTIMIZER_INDEX_CACHING = 100은 NL 안쪽 인덱스 블록이 모두 캐시에 있다고 가정해 그 몫을 거의 0으로 본다. 그래서 주문 한 건마다 ORD_ITEM_PK를 찾아가는 비용이 resc_io: 0.03이 되고, NL 비용은 41,700 × 0.03 + 11 = 1,262다. 해시 조인 비용의 대부분은 ORD_ITEM FULL이라 두 파라미터와 상관없이 15,409 그대로다. 그래서 NL을 골랐다(기본값이면 NL 약 167,900, 해시 약 16,400으로 해시). 실제 일은 깎이지 않는다. 8번 줄 Starts가 41,712로 주문 41,712건마다 ORD_ITEM_PK를 탐색했고(125K), 9번 줄에서 테이블을 41,712블록 더 읽어 Buffers는 168K(167,919)다. 두 줄을 빼면 해시 조인으로 돌아가 Buffers 168K → 56,336, 시간 1.38초 → 0.62초가 된다. 세션 설정은 그 세션에서 도는 모든 SQL에 퍼지므로 이 패키지의 다른 문장도 같은 왜곡 아래에서 계획을 세운다. 어떤 SQL에 정말 다른 설정이 필요하다면 OPT_PARAM 힌트로 그 SQL 하나로 좁히고, 이유와 재검토 조건을 힌트 대장에 적는다.

해설

같은 SQL, 다른 계획: SQL Developer와 배치의 커서는 V$SQL_SHARED_CURSOR에 OPTIMIZER_MISMATCH로 갈라진다. "내 세션에서는 빠른데 배치에서는 느리다"면 먼저 두 세션의 옵티마이저 환경(V$SES_OPTIMIZER_ENV)부터 견준다(11화 §5).
더 줄이려면: ORD_ITEM에도 ORD_DT가 있으므로 i.ORD_DT 범위를 더하면 ORD_ITEM_X01로 한 달 치만 읽어 약 3,700블록이 된다. 두 테이블의 일자가 늘 같은지(일자가 다른 줄이 0건인지) 먼저 확인해야 한다.
OPTIMIZER_INDEX_CACHING은 NL 안쪽 인덱스와 IN-List Iterator의 인덱스 비용에만 쓰인다. 그래서 두 값이 함께 있으면 NL이 유난히 싸 보인다.
2015년의 가이드는 그때 그 서버에서 근거가 있었을 수 있다. 문제는 근거와 측정 조건이 남지 않아 아무도 다시 볼 수 없었다는 점이다(06화 힌트 유효기간 대장과 같은 이야기).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 10053은 필요한 줄만 옮겼다 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 10053은 필요한 줄만 옮겼다 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황