PART-012

파티션 테이블로 옮긴 월 마감 프로시저가 예전보다 빨라지지 않았다

실전PLSQL한빛유통387 / 700

상황

월 마감 프로시저 P_MON_CLOSE는 매월 2일 새벽에 지난달을 마감한다. 월 집계를 쌓고, 미결(PENDING) 주문을 일괄 취소하고, 취소 건수를 점검한다. 원본 T_ORD_BIG에서 월별 파티션 복사본 T_ORD_BIG_P14로 옮기면서 테이블 이름만 바꿨다. 배치 계정은 로그온 트리거로 NLS_DATE_FORMAT = 'YYYY-MM-DD'를 맞춘다. 오과장이 p_ym = '202406'으로 돌린 세 문장의 실행계획을 뽑아 왔다.

테이블과 인덱스

T_ORD_BIG_P14 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_DT) 월별 36칸과 빈 PMAX, 모두 37칸. P202406은 30번째 칸이고 FULL로 약 161블록, 37칸 전체는 5,868블록이다.
ORD_DT는 DATE이고 전부 자정이다. 2024년 6월은 27,390행이고 그중 PENDING이 2,191행, CANCEL이 548행이다.
T_MON_SUM 월 집계(문제 설정): 월마다 1행.
인덱스는 없다. ②의 + 0은 원본 T_ORD_BIG에 ORD_DT 인덱스가 있던 2019년에 넣었다(소스 주석).

원본 SQL

sql

PROCEDURE P_MON_CLOSE(p_ym IN VARCHAR2) IS                 -- p_ym = '202406'
  v_from   DATE         := TO_DATE(p_ym || '01', 'YYYYMMDD');
  v_to     DATE         := ADD_MONTHS(v_from, 1);
  v_from_s VARCHAR2(10) := SUBSTR(p_ym, 1, 4) || '-' || SUBSTR(p_ym, 5, 2) || '-01';
  v_cnt    NUMBER;
BEGIN
  -- ① 월 집계
  INSERT INTO T_MON_SUM (SUM_MON, ORD_CNT, ORD_AMT)
  SELECT v_from, COUNT(*), SUM(ORD_AMT)
    FROM T_ORD_BIG_P14
   WHERE TO_CHAR(ORD_DT, 'YYYYMM') = p_ym;

  -- ② 미결 주문 일괄 취소 (인덱스 타지 말라고 +0 · 2019.05 강도현)
  UPDATE T_ORD_BIG_P14
     SET ORD_ST = 'CANCEL'
   WHERE ORD_ST = 'PENDING'
     AND ORD_DT + 0 >= v_from
     AND ORD_DT + 0 <  v_to;

  -- ③ 취소 건수 점검
  SELECT COUNT(*) INTO v_cnt
    FROM T_ORD_BIG_P14
   WHERE ORD_ST = 'CANCEL'
     AND ORD_DT >= v_from_s
     AND ORD_DT <  v_to;

  COMMIT;
END;

실행계획 학습용 예시 · 교재 14화 규모

xplan

[① INSERT … SELECT]
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |               |      1 |        |      0 | 00:00:00.12 |    5873 |        |       |
|   1 |  LOAD TABLE CONVENTIONAL | T_MON_SUM     |      1 |        |      0 | 00:00:00.12 |    5873 |        |       |
|   2 |   SORT AGGREGATE         |               |      1 |      1 |      1 | 00:00:00.12 |    5868 |        |       |
|   3 |    PARTITION RANGE ALL   |               |      1 |  10000 |  27390 | 00:00:00.11 |    5868 |      1 |    37 |
|*  4 |     TABLE ACCESS FULL    | T_ORD_BIG_P14 |     36 |  10000 |  27390 | 00:00:00.10 |    5868 |      1 |    37 |
----------------------------------------------------------------------------------------------------------------------

   4 - filter(TO_CHAR(INTERNAL_FUNCTION("ORD_DT"),'YYYYMM')=:B1)

[② UPDATE]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |               |      1 |        |      0 | 00:00:00.16 |    8261 |        |       |
|   1 |  UPDATE               | T_ORD_BIG_P14 |      1 |        |      0 | 00:00:00.16 |    8261 |        |       |
|   2 |   PARTITION RANGE ALL |               |      1 |    200 |   2191 | 00:00:00.11 |    5868 |      1 |    37 |
|*  3 |    TABLE ACCESS FULL  | T_ORD_BIG_P14 |     36 |    200 |   2191 | 00:00:00.11 |    5868 |      1 |    37 |
-------------------------------------------------------------------------------------------------------------------

   3 - filter(("ORD_ST"='PENDING' AND INTERNAL_FUNCTION("ORD_DT")+0>=:B2 AND INTERNAL_FUNCTION("ORD_DT")+0<:B1))

[③ SELECT COUNT(*)]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |      1 | 00:00:00.01 |     161 |        |       |
|   1 |  SORT AGGREGATE             |               |      1 |      1 |      1 | 00:00:00.01 |     161 |        |       |
|*  2 |   FILTER                    |               |      1 |        |   2739 | 00:00:00.01 |     161 |        |       |
|   3 |    PARTITION RANGE ITERATOR |               |      1 |    548 |   2739 | 00:00:00.01 |     161 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | T_ORD_BIG_P14 |      1 |    548 |   2739 | 00:00:00.01 |     161 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------

   2 - filter(TO_DATE(:B2)<:B1)
   4 - filter(("ORD_ST"='CANCEL' AND "ORD_DT">=TO_DATE(:B2) AND "ORD_DT"<:B1))

[같은 프로시저를 SQL Developer (NLS_DATE_FORMAT = 'DD-MON-RR') 에서 돌리면]
  ③ ORA-01861: literal does not match format string

문제

세 문장이 각각 몇 칸을 여는지 Pstart, Pstop과 Starts로 쓰고, 칸을 고르거나 고르지 못하는 까닭을 문장마다 설명하시오.
결과가 바뀌지 않게 프로시저를 고쳐 쓰고, 세 문장의 Buffers가 어떻게 바뀌는지 어림하시오.
②의 + 0을 지워도 되는지, ③은 칸을 고르는데도 왜 고쳐야 하는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_MON_CLOSE(p_ym IN VARCHAR2) IS
  v_from DATE := TO_DATE(p_ym || '01', 'YYYYMMDD');
  v_to   DATE := ADD_MONTHS(v_from, 1);
  v_cnt  NUMBER;
BEGIN
  -- ① 키를 맨몸으로 : 그 달 첫날 이상, 다음 달 첫날 미만
  INSERT INTO T_MON_SUM (SUM_MON, ORD_CNT, ORD_AMT)
  SELECT v_from, COUNT(*), SUM(ORD_AMT)
    FROM T_ORD_BIG_P14
   WHERE ORD_DT >= v_from
     AND ORD_DT <  v_to;

  -- ② + 0 을 지운다 (2019년 ORD_DT 인덱스를 피하던 처방 · 지금 테이블에는 인덱스가 없다)
  UPDATE T_ORD_BIG_P14
     SET ORD_ST = 'CANCEL'
   WHERE ORD_ST = 'PENDING'
     AND ORD_DT >= v_from
     AND ORD_DT <  v_to;

  -- ③ 문자 변수와 세션 NLS 에 기대지 않는다 : DATE 변수로 비교
  SELECT COUNT(*) INTO v_cnt
    FROM T_ORD_BIG_P14
   WHERE ORD_ST = 'CANCEL'
     AND ORD_DT >= v_from
     AND ORD_DT <  v_to;

  COMMIT;
END;

개선 후 실행계획 학습용 예시 · 교재 14화 규모

xplan

[① INSERT … SELECT]
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |               |      1 |        |      0 | 00:00:00.01 |     166 |        |       |
|   1 |  LOAD TABLE CONVENTIONAL     | T_MON_SUM     |      1 |        |      0 | 00:00:00.01 |     166 |        |       |
|   2 |   SORT AGGREGATE             |               |      1 |      1 |      1 | 00:00:00.01 |     161 |        |       |
|*  3 |    FILTER                    |               |      1 |        |  27390 | 00:00:00.01 |     161 |        |       |
|   4 |     PARTITION RANGE ITERATOR |               |      1 |  27390 |  27390 | 00:00:00.01 |     161 |    KEY |   KEY |
|*  5 |      TABLE ACCESS FULL       | T_ORD_BIG_P14 |      1 |  27390 |  27390 | 00:00:00.01 |     161 |    KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------

[② UPDATE]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT            |               |      1 |        |      0 | 00:00:00.04 |    2554 |        |       |
|   1 |  UPDATE                     | T_ORD_BIG_P14 |      1 |        |      0 | 00:00:00.04 |    2554 |        |       |
|*  2 |   FILTER                    |               |      1 |        |   2191 | 00:00:00.01 |     161 |        |       |
|   3 |    PARTITION RANGE ITERATOR |               |      1 |   2191 |   2191 | 00:00:00.01 |     161 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | T_ORD_BIG_P14 |      1 |   2191 |   2191 | 00:00:00.01 |     161 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------

[③ SELECT COUNT(*)]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |      1 | 00:00:00.01 |     161 |        |       |
|   1 |  SORT AGGREGATE             |               |      1 |      1 |      1 | 00:00:00.01 |     161 |        |       |
|*  2 |   FILTER                    |               |      1 |        |   2739 | 00:00:00.01 |     161 |        |       |
|   3 |    PARTITION RANGE ITERATOR |               |      1 |    548 |   2739 | 00:00:00.01 |     161 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | T_ORD_BIG_P14 |      1 |    548 |   2739 | 00:00:00.01 |     161 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------

   (세 문장 모두) filter(:B2<:B1) · filter(("ORD_DT">=:B2 AND "ORD_DT"<:B1 …)) · 실행 때 고른 칸 — 30번 P202406

모범 답안 서술

①은 TO_CHAR(ORD_DT, 'YYYYMM') = p_ym으로 파티션 키를 가공했다. 3번 줄이 PARTITION RANGE ALL(Pstart 1, Pstop 37)이고 4번 줄의 Starts 36으로 36개월 칸을 다 읽었다(5,868블록). ②는 ORD_DT + 0이라서 역시 가공이다. 칸막이 경계는 ORD_DT 값에 붙어 있으므로, 0을 더한 값이 어느 칸에 드는지 옵티마이저는 따지지 않고 전부 연다(Pstart 1, Pstop 37, Starts 36, 갱신 몫을 더해 8,261블록). ③은 ORD_DT가 맨몸이라 칸을 고른다. 문자 변수 v_from_s와 DATE 컬럼을 비교하면 문자 쪽, 즉 값이 TO_DATE(:B2)로 바뀌므로 Pstart와 Pstop은 KEY이고 30번 칸 하나만 읽었다(Starts 1, 161블록). 고친 프로시저는 세 문장 모두 ORD_DT >= v_from AND ORD_DT < v_to로 쓴다. v_from과 v_to는 DATE 변수라서 형변환도 가공도 없고, PL/SQL 변수는 바인드 변수가 되므로 세 문장 모두 KEY로 30번 칸 하나만 연다. ①은 5,873 → 약 166, ②는 8,261 → 약 2,554, ③은 161 → 161이다. 세 문장을 합치면 14,295 → 약 2,881블록이다. ②의 + 0은 원본에 ORD_DT 인덱스가 있던 시절, 한 달 치 PENDING을 인덱스로 찾지 말고 FULL로 읽게 하려던 처방이었다. 지금 테이블에는 인덱스가 없고, 칸 하나를 FULL로 읽는 것이 원래 의도와도 맞으므로 지워도 된다. 나중에 인덱스가 생겨 다시 막아야 한다면 + 0 대신 FULL 힌트로 의도를 드러낸다. ③은 칸을 고르지만 '2024-06-01'을 날짜로 읽는 방식이 세션의 NLS_DATE_FORMAT에 달려 있다. 배치 계정은 로그온 트리거로 맞췄지만, SQL Developer(기본 DD-MON-RR)에서 같은 프로시저를 돌리면 ORA-01861로 멈춘다. 형식이 엇갈려 엉뚱한 날짜로 읽히면 에러 없이 답이 바뀔 수도 있다. 그래서 문자 변수를 없애고 DATE 변수로 비교한다.

해설

인덱스를 끄던 옛 습관이 칸 고르기까지 끈다: 규칙 기반 옵티마이저 시절의 + 0, || ''는 컬럼을 가공해 인덱스를 못 쓰게 하던 기법이다. 파티션 키에 쓰면 같은 이유로 Pruning도 막는다. 지우기 전에 그 줄이 무엇을 피하려 했는지 주석과 이력에서 먼저 확인한다.
DATE 컬럼과 문자를 비교하면 문자 쪽(값)이 바뀌므로 컬럼은 맨몸이다(교재 03화 §1.2). 칸 고르기는 되지만 그 문자를 어떻게 읽을지가 세션 NLS에 달려 있다. 교재 03화 실측처럼 YYYY-MM-DD 세션에서 '01-JUN-24'는 서기 1년으로 읽혀 에러 없이 100만 건을 돌려준다.
①의 TO_CHAR(ORD_DT, 'YYYYMM') = p_ym은 원본 테이블에서도 인덱스를 막던 모양이다(교재 03화 §1, 5,826블록). 파티션으로 옮기면 같은 가공이 칸 고르기까지 막는다.
마감을 다시 돌릴 일이 있다면 ①을 MERGE로 바꿔 같은 달이 두 번 쌓이지 않게 한다. 칸 고르기와는 다른 문제지만 마감 배치에서 자주 만난다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황