정산 분개 생성이 11초인데, 분개 규칙은 낮의 정산 등록과 같은 패키지라 SQL로 옮기기 어렵다

실전PLSQL한빛유통687 / 700

상황

야간 체인의 「정산 분개 생성」 단계는 정산 10만 건마다 분개 줄(매출, 수수료, 부가세, 포인트 사용)을 3 ~ 4줄씩 만들어 T_STLM_JNL에 넣는다. 지금은 11.4초가 걸리고, 오과장은 이 단계에 3초를 배정하고 싶어 한다. 분개 규칙은 패키지 프로시저 PKG_JNL_RULE.MAKE_LINES에 있다(등급과 결제 수단에 따른 수수료율, 부가세 끝수 처리, 포인트 안분이 40줄쯤 된다). 낮에는 정산 등록 화면이 같은 프로시저로 한 건씩 분개를 만든다. 박팀장은 대안을 숫자로 견주어 하나를 고르고, 무엇을 잃는지도 적어 달라고 했다.

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록 · M_CUS 고객: 2,000행 · 30블록 · CUS_GD A 1% · B 9% · C 90%
T_STLM_JNL 분개(이 배치가 채운다): STLM_SEQ · LINE_NO · ACCT_CD · DR_CR · JNL_AMT · JNL_DT · 정산 한 건에 3줄, 포인트를 쓴 22%는 4줄 · 이번 판 322,000줄
T_STLM_JNL은 배치를 시작할 때 비우고(TRUNCATE) 인덱스는 적재 뒤에 만든다 · 다른 세션은 체인이 끝난 뒤에 읽는다
PKG_JNL_RULE.T_LINES: 분개 줄 레코드의 PL/SQL 컬렉션 · DB는 NOARCHIVELOG · CPU 8코어 중 이 시간대에 4코어가 비어 있다

원본 SQL

sql

PROCEDURE P_MAKE_JNL IS
  v_lines PKG_JNL_RULE.T_LINES;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_STLM_JNL';
  FOR r IN (SELECT s.STLM_SEQ, s.STLM_DT, s.STLM_AMT, c.CUS_GD
              FROM T_ORD_STLM s, M_CUS c
             WHERE c.CUS_ID = s.CUS_ID) LOOP
    PKG_JNL_RULE.MAKE_LINES(r.STLM_SEQ, r.CUS_GD, r.STLM_AMT, v_lines);   -- 정산 한 건 → 분개 3 ~ 4줄
    FOR i IN 1 .. v_lines.COUNT LOOP
      INSERT INTO T_STLM_JNL (STLM_SEQ, LINE_NO, ACCT_CD, DR_CR, JNL_AMT, JNL_DT)
      VALUES (r.STLM_SEQ, i, v_lines(i).ACCT_CD, v_lines(i).DR_CR, v_lines(i).AMT, r.STLM_DT);
    END LOOP;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[세션 통계: P_MAKE_JNL 한 번 (정산 100,000건 → 분개 322,000줄)]
  경과 시간                    11.4 초 (CPU 9.6 초)
  recursive calls              323,140
  execute count                322,012
  session logical reads        361,870
  redo size                    78.4 MB
  PL/SQL 규칙 (DBMS_HPROF)     MAKE_LINES 100,000번 · 0.62 초

[TKPROF: 재귀 문장 둘]
  SELECT S.STLM_SEQ, S.STLM_DT, S.STLM_AMT, C.CUS_GD FROM T_ORD_STLM S, M_CUS C WHERE C.CUS_ID = S.CUS_ID
     Execute 1 · Fetch 1,001 · rows 100,000 · query 672
  INSERT INTO T_STLM_JNL (STLM_SEQ, LINE_NO, ACCT_CD, DR_CR, JNL_AMT, JNL_DT) VALUES (:B6, :B5, :B4, :B3, :B2, :B1)
     Execute 322,000 · rows 322,000 · cpu 7.9 · elapsed 8.6 · query 3,140 · current 355,600

문제

11.4초가 어디서 나오는지 세션 통계와 TKPROF로 설명하시오.
분개 규칙을 PKG_JNL_RULE 하나에 둔 채로, 파이프라인 테이블 함수와 INSERT … SELECT 한 문장으로 다시 쓰시오.
① BULK COLLECT와 FORALL, ② 2번의 파이프라인 함수, ③ 규칙을 SQL(CASE와 규칙표)로 옮긴 INSERT … SELECT를 견주어 하나를 고르고, 고른 안의 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 결과 줄의 SQL 타입
CREATE OR REPLACE TYPE T_JNL_OBJ AS OBJECT (STLM_SEQ NUMBER, LINE_NO NUMBER, ACCT_CD VARCHAR2(10),
                                            DR_CR VARCHAR2(1), JNL_AMT NUMBER, JNL_DT DATE);
/
CREATE OR REPLACE TYPE T_JNL_TAB AS TABLE OF T_JNL_OBJ;
/

-- ② PKG_JNL_RULE 안 (명세에도 같은 선언과 R_STLM, T_STLM 타입을 둔다)
--    커서를 받아 정산 한 행마다 분개 줄을 바로 내보낸다 · 규칙은 MAKE_LINES 그대로
FUNCTION FN_JNL_PIPE(p_cur SYS_REFCURSOR) RETURN T_JNL_TAB PIPELINED
  PARALLEL_ENABLE (PARTITION p_cur BY ANY)           -- 병렬로 돌릴 때를 위해
IS
  v_in    T_STLM;                                    -- TABLE OF R_STLM (STLM_SEQ, STLM_DT, STLM_AMT, CUS_GD)
  v_lines T_LINES;
BEGIN
  LOOP
    FETCH p_cur BULK COLLECT INTO v_in LIMIT 1000;
    EXIT WHEN v_in.COUNT = 0;
    FOR j IN 1 .. v_in.COUNT LOOP
      MAKE_LINES(v_in(j).STLM_SEQ, v_in(j).CUS_GD, v_in(j).STLM_AMT, v_lines);   -- 낮의 정산 등록과 같은 규칙
      FOR i IN 1 .. v_lines.COUNT LOOP
        PIPE ROW (T_JNL_OBJ(v_in(j).STLM_SEQ, i, v_lines(i).ACCT_CD, v_lines(i).DR_CR,
                            v_lines(i).AMT, v_in(j).STLM_DT));
      END LOOP;
    END LOOP;
  END LOOP;
  CLOSE p_cur;
  RETURN;
END;

-- ③ 배치: 한 문장 · 직접 경로 적재 · 커밋 한 번
PROCEDURE P_MAKE_JNL IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_STLM_JNL';
  INSERT /*+ APPEND */ INTO T_STLM_JNL (STLM_SEQ, LINE_NO, ACCT_CD, DR_CR, JNL_AMT, JNL_DT)
  SELECT STLM_SEQ, LINE_NO, ACCT_CD, DR_CR, JNL_AMT, JNL_DT
    FROM TABLE(PKG_JNL_RULE.FN_JNL_PIPE(CURSOR(
           SELECT s.STLM_SEQ, s.STLM_DT, s.STLM_AMT, c.CUS_GD
             FROM T_ORD_STLM s, M_CUS c
            WHERE c.CUS_ID = s.CUS_ID)));
  COMMIT;
END;

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

xplan

[세 판 비교 · 같은 정산 100,000건 → 분개 322,000줄 (두 번 돌린 범위)]
 판                                            경과           recursive calls   논리 읽기   redo      남는 것
 원래 (줄마다 INSERT)                          11.2 ~ 11.4 초         323,140     361,870   78.4 MB
 ① BULK COLLECT LIMIT 1000 + FORALL INSERT      1.5 ~ 1.7 초             430       9,860   21.3 MB   규칙 한 곳 · 일반 INSERT
 ② 파이프라인 함수 + INSERT APPEND (고른 안)    0.9 ~ 1.1 초             120       1,960    0.4 MB   규칙 한 곳 · 직접 경로
 ③ 규칙을 CASE와 규칙표로 옮긴 INSERT APPEND    0.4 ~ 0.5 초              40       1,830    0.4 MB   규칙 두 곳
 (② + 병렬 4: 0.4 초 안팎 · 세션에서 병렬 DML을 켜야 한다)

[② 의 계획]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                     |             |      1 |        |      0 |00:00:00.98 |    1960 |
|   1 |  LOAD AS SELECT                      | T_STLM_JNL  |      1 |        |      0 |00:00:00.98 |    1960 |
|   2 |   OPTIMIZER STATISTICS GATHERING     |             |      1 |   8168 |   322K |00:00:00.71 |     672 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH | FN_JNL_PIPE |      1 |   8168 |   322K |00:00:00.66 |     672 |
|*  4 |     HASH JOIN                        |             |      1 |   100K |   100K |00:00:00.05 |     672 |
|   5 |      TABLE ACCESS FULL               | M_CUS       |      1 |   2000 |   2000 |00:00:00.01 |      30 |
|   6 |      TABLE ACCESS FULL               | T_ORD_STLM  |      1 |   100K |   100K |00:00:00.01 |     642 |
---------------------------------------------------------------------------------------------------------------
   4 - access("C"."CUS_ID"="S"."CUS_ID")

모범 답안 서술

어디서 11.4초가 나오나. TKPROF의 INSERT 줄이 Execute 322,000이다. 분개 줄마다 INSERT를 한 번씩 실행했으므로 PL/SQL에서 SQL로 32만 번 건너갔고(recursive calls 323,140), 한 줄을 넣을 때마다 블록을 current 모드로 찾아 고치며(current 355,600) redo를 남겼다(78.4MB). INSERT 줄의 elapsed가 8.6초로 경과 시간의 대부분이다. 반면 규칙 MAKE_LINES는 HPROF로 0.62초뿐이다. 느린 것은 규칙이 아니라 줄마다 실행한 INSERT다. 파이프라인 함수로 다시 쓴 판. FN_JNL_PIPE는 정산 커서를 인자로 받아 1,000건씩 읽고, 정산 한 건마다 MAKE_LINES를 불러 나온 분개 줄을 PIPE ROW로 바로 내보낸다. 배치는 INSERT /*+ APPEND */ … SELECT … FROM TABLE(PKG_JNL_RULE.FN_JNL_PIPE(CURSOR(SELECT …))) 한 문장이다. 규칙은 패키지에 그대로 있고, 32만 줄은 SQL 엔진이 한 문장 안에서 받아 직접 경로로 HWM 위에 블록째 쓴다(LOAD AS SELECT). 경과는 11.4초 → 1초 안팎, recursive calls는 323,140 → 120, redo는 78.4MB → 0.4MB다. 세 안의 비교. ① BULK COLLECT … LIMIT과 FORALL은 32만 번의 왕복을 천 줄씩 묶어 1.6초까지 줄이지만, FORALL이 하는 것은 여전히 일반 INSERT라 버퍼 캐시를 거치고 redo도 21.3MB가 남는다. 분개 줄을 배열에 모아 두는 코드도 필요하다. ③ 규칙을 CASE와 규칙표로 옮긴 INSERT … SELECT는 0.5초로 가장 빠르다. 그러나 낮의 정산 등록은 패키지의 규칙으로, 야간 배치는 SQL의 규칙으로 분개를 만들게 되어 규칙이 두 곳이 된다. 수수료율이 바뀌는 날 한 곳만 고치면 같은 정산의 분개가 낮과 밤에 다르게 만들어진다. 그래서 ②를 고른다. 규칙은 한 곳에 두고, 배정한 3초 안에 넉넉히 든다. 고른 안의 대가. APPEND 적재 중에는 T_STLM_JNL에 테이블 배타 잠금(TM 6)이 걸려 다른 세션이 DML을 하지 못하고, 같은 세션도 커밋 전에는 그 테이블을 읽지 못한다(ORA-12838). 체인이 끝난 뒤에만 읽는 배치 전용 테이블이라 괜찮다. 또 한 문장이므로 분개 한 줄에서 예외가 나면 문장 전체가 되돌아간다. 줄마다 건너뛰며 넣던 판과 달리, 규칙의 예외를 함수 안에서 미리 걸러 낼지 정해 두어야 한다. 파이프라인 함수의 E-Rows는 기본값(8,168)이지만 적재 한 문장이라 계획에는 영향이 없다.

해설

③이 숫자로는 가장 빠르다. 그래도 0.5초를 더 아끼려고 규칙이 두 곳이 되는 위험을 지지 않는다. PLSQL-014에서 보정률 규칙을 CASE로 옮기지 않은 것과 같은 판단이다. 규칙을 데이터(등급별 수수료율 표)로 바꿔 패키지와 SQL이 모두 그 표를 읽게 하는 길은 정산팀과 따로 정한다.
①을 고를 자리: 규칙에 맞지 않는 정산을 반송함에 남기고 나머지는 계속 넣어야 한다면 FORALL … SAVE EXCEPTIONS가 자연스럽다. ②에서 같은 일을 하려면 함수가 오류 표시 칸을 단 줄을 내보내고 INSERT ALL로 분개와 반송함에 갈라 넣는다.
병렬: PARALLEL_ENABLE (PARTITION p_cur BY ANY)를 붙여 두었으므로 ALTER SESSION ENABLE PARALLEL DML과 병렬 힌트로 0.4초 안팎까지 줄일 수 있다. 비어 있는 4코어를 쓰는 대신 체인의 다른 단계와 CPU를 나눠 쓰게 된다(교재 14화).
함수 안의 FETCH: 인자로 받은 커서도 명시적 커서처럼 FETCH … INTO로 읽으면 한 건씩이다. 함수 안에서도 BULK COLLECT … LIMIT으로 읽고, 나가는 조건은 COUNT = 0으로 둔다(PLSQL-016).
redo: NOARCHIVELOG라 직접 경로 적재의 redo가 0.4MB다. 운영이 ARCHIVELOG면 NOLOGGING과 백업 정책을 함께 정해야 한다(DML-001).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황