지난주부터 월말 정산 점검 계획에 카티션 조인이 끼어 있다

중급SELECT한빛유통599 / 700

상황

정산팀의 월말 점검 배치(PL/SQL)는 점검 대상 주문 번호를 전역 임시 테이블 GT_STLM_TGT에 약 40,000건 넣고, 상품 유형 10종마다 아래 SQL을 한 번씩 돌린다. 지난달까지 1초 안에 끝나던 점검이 이번 달에는 20초를 넘겼다. 지난주 월요일 DBA가 주간 통계 잡을 바꿨다. "임시 테이블도 빠짐없이" 모으려고 GATHER_SCHEMA_STATS(..., gather_temp => TRUE)로 고쳤다. 이 DB는 2016년 11g에서 올릴 때 업체가 GLOBAL_TEMP_TABLE_STATS를 SHARED로 걸어 두었다.

테이블과 인덱스

GT_STLM_TGT: 전역 임시 테이블(ON COMMIT PRESERVE ROWS) · ORD_SEQ · 배치 세션에서 40,000행(약 72블록)
GT_STLM_TGT 통계: NUM_ROWS 0 · BLOCKS 0 · SCOPE SHARED · 마지막 수집 지난주 월요일 03:12(DBA 세션에서 모았다. 그 세션의 임시 테이블은 비어 있었다)
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문당 1 ~ 3건(평균 2건) · 통계 최신
M_ITM: 100행 · 5블록 · ITM_TP 10종(유형마다 상품 10개) · PK_M_ITM
대상 주문 40,000건의 상세는 약 80,000건이고, 한 유형은 그중 약 8,000건이다.

원본 SQL

sql

SELECT i.ITM_ID, i.ITM_NM, COUNT(*) AS det_cnt, SUM(d.ORD_QTY * d.UNT_PRC) AS amt
  FROM GT_STLM_TGT g, T_ORD_DET d, M_ITM i
 WHERE d.ORD_SEQ = g.ORD_SEQ
   AND i.ITM_ID  = d.ITM_ID
   AND i.ITM_TP  = :tp
 GROUP BY i.ITM_ID, i.ITM_NM

실행계획 학습용 예시

xplan

[유형 하나 · 이번 달 점검]
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |     10 |00:00:01.93 |    1270K|
|   1 |  HASH GROUP BY                 |              |      1 |      1 |     10 |00:00:01.93 |    1270K|
|   2 |   NESTED LOOPS                 |              |      1 |      1 |   8012 |00:00:01.92 |    1270K|
|   3 |    NESTED LOOPS                |              |      1 |      1 |    800K|00:00:00.98 |     470K|
|   4 |     MERGE JOIN CARTESIAN       |              |      1 |      1 |    400K|00:00:00.12 |      77 |
|*  5 |      TABLE ACCESS FULL         | M_ITM        |      1 |     10 |     10 |00:00:00.01 |       5 |
|   6 |      BUFFER SORT               |              |     10 |      1 |    400K|00:00:00.08 |      72 |
|   7 |       TABLE ACCESS FULL        | GT_STLM_TGT  |      1 |      1 |  40000 |00:00:00.01 |      72 |
|*  8 |     INDEX RANGE SCAN           | PK_T_ORD_DET |    400K|      1 |    800K|00:00:00.71 |     470K|
|*  9 |    TABLE ACCESS BY INDEX ROWID | T_ORD_DET    |    800K|      1 |   8012 |00:00:00.82 |     800K|
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("I"."ITM_TP"=:TP)
   8 - access("D"."ORD_SEQ"="G"."ORD_SEQ")
   9 - filter("I"."ITM_ID"="D"."ITM_ID")

[USER_TAB_STATISTICS — GT_STLM_TGT]
 NUM_ROWS  BLOCKS  LAST_ANALYZED        SCOPE
        0       0  지난주 월 03:12      SHARED

문제

4번 줄의 MERGE JOIN CARTESIAN이 왜 나왔는지 E-Rows와 통계의 출처로 설명하시오. 지난달까지는 같은 SQL이 왜 괜찮았는지도 쓰시오.
이 임시 테이블의 통계를 바로잡고, 배치가 앞으로도 맞는 숫자를 쓰게 하는 SQL을 쓰시오. 주간 잡은 어떻게 할지와 그 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 0 으로 적힌 공유 통계를 지우고, 이 임시 테이블은 세션마다 자기 통계를 쓰게 한다 (12c 이후의 기본값으로)
BEGIN
  DBMS_STATS.DELETE_TABLE_STATS(ownname => USER, tabname => 'GT_STLM_TGT', no_invalidate => FALSE);
  DBMS_STATS.SET_TABLE_PREFS(USER, 'GT_STLM_TGT', 'GLOBAL_TEMP_TABLE_STATS', 'SESSION');
END;
/

-- ② 점검 배치 : 대상을 넣은 뒤 그 세션에서 통계를 모은다 (세션 전용 통계 · 다른 세션에는 보이지 않는다)
BEGIN
  INSERT INTO GT_STLM_TGT (ORD_SEQ)
  SELECT ORD_SEQ FROM T_ORD_STLM WHERE STLM_DT >= :mon_from AND STLM_DT < :mon_to;
  DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'GT_STLM_TGT');
  -- 유형 10종마다 아래 SQL (그대로)
END;
/
SELECT i.ITM_ID, i.ITM_NM, COUNT(*) AS det_cnt, SUM(d.ORD_QTY * d.UNT_PRC) AS amt
  FROM GT_STLM_TGT g, T_ORD_DET d, M_ITM i
 WHERE d.ORD_SEQ = g.ORD_SEQ
   AND i.ITM_ID  = d.ITM_ID
   AND i.ITM_TP  = :tp
 GROUP BY i.ITM_ID, i.ITM_NM;

-- ③ 주간 잡은 gather_temp => FALSE (기본값) 로 되돌린다

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

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |      1 |        |     10 |00:00:00.05 |     973 |
|   1 |  HASH GROUP BY        |             |      1 |     10 |     10 |00:00:00.05 |     973 |
|*  2 |   HASH JOIN           |             |      1 |   8000 |   8012 |00:00:00.05 |     973 |
|*  3 |    TABLE ACCESS FULL  | M_ITM       |      1 |     10 |     10 |00:00:00.01 |       5 |
|*  4 |    HASH JOIN          |             |      1 |  80000 |  80024 |00:00:00.04 |     968 |
|   5 |     TABLE ACCESS FULL | GT_STLM_TGT |      1 |  40000 |  40000 |00:00:00.01 |      72 |
|   6 |     TABLE ACCESS FULL | T_ORD_DET   |      1 |    200K|    200K|00:00:00.02 |     896 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ITM_ID"="D"."ITM_ID")
   3 - filter("I"."ITM_TP"=:TP)
   4 - access("D"."ORD_SEQ"="G"."ORD_SEQ")

Note
-----
   - Global temporary table session private statistics used

모범 답안 서술

7번 줄 GT_STLM_TGT의 E-Rows는 1인데 실제로는 40,000행이다. 지난주 DBA가 주간 잡에 gather_temp => TRUE를 주자, DBA 세션에서 비어 있던 임시 테이블을 0행으로 모았다. 이 DB는 GLOBAL_TEMP_TABLE_STATS가 SHARED라서 그 0행 통계가 공유 통계가 되어 모든 세션이 같이 쓴다. 배치 세션의 임시 테이블에 4만 건이 있어도 옵티마이저는 0행 통계를 믿는다. 1행짜리와 상품 10개(5번 줄)를 곱해도 10행이니 옵티마이저 눈에는 카티션 곱이 싸다. 그래서 MERGE JOIN CARTESIAN으로 상품 10개 × 대상 주문 40,000건 = 40만 행을 만들고, 그 40만 행마다 PK_T_ORD_DET를 찾아(8번 줄 Starts 400K) 상세 80만 건을 꺼낸 뒤 상품이 맞지 않는 것을 버렸다. Buffers가 1,270K이고 유형 10종이면 20초다. 지난달까지는 이 임시 테이블에 통계가 없었다. 통계가 없으면 옵티마이저는 하드 파싱 때 동적 샘플링으로 그 세션의 임시 테이블을 세어 4만 건 안팎으로 보았다. 0이 적힌 뒤로는 통계가 있으므로 세지 않는다. 0행 공유 통계를 지우고, 이 테이블의 선호도를 SESSION으로 되돌린다. 배치는 대상을 넣은 뒤 그 세션에서 GATHER_TABLE_STATS를 부른다. 세션 전용 통계는 그 세션에만 보이고, 그 통계로 만든 커서는 다른 세션과 나눠 쓰지 않는다. E-Rows가 40,000이 되어 해시 조인 두 번으로 풀리고 Buffers는 1,270K → 973으로 준다. 주간 잡은 gather_temp를 기본값(FALSE)으로 되돌린다. 대가도 있다. 세션 전용 통계는 그 세션이 수집을 한 번 더 하고 하드 파싱을 따로 하므로, 짧은 호출이 아주 많은 화면에는 부담이 된다. 이 배치처럼 한 세션이 큰 임시 테이블을 오래 쓰는 곳에 맞는다.

해설

지우기만 해도 지난달로 돌아간다. DELETE_TABLE_STATS만 하면 다시 동적 샘플링으로 센다. 다만 표본은 하드 파싱 때 한 번뿐이라, 같은 SQL을 다른 크기의 임시 테이블로 쓰는 세션이 있으면 첫 세션의 숫자를 나눠 쓴다(OPT-008).
11g 시절의 처방은 임시 테이블 통계를 지우고 잠가 늘 동적 샘플링을 하게 하거나, 대표 통계를 넣고 잠그는 것이었다. SHARED를 걸어 둔 DB라면 아직 그 처방이 남아 있는지 확인한다.
카티션 조인은 대개 결과가 아니라 E-Rows 1의 증상이다. 계획에 MERGE JOIN CARTESIAN과 BUFFER SORT가 보이면 먼저 그 아래 줄의 E-Rows가 1인지 본다.
ON COMMIT DELETE ROWS 임시 테이블이라면 커밋하는 순간 행이 사라진다. 세션 전용 통계를 모으는 위치는 행이 살아 있는 동안이어야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황