PG 정산 파일 10만 줄을 올리는 데 41초, 금액에 쉼표가 든 214줄은 오류 테이블로 빠진다

중급INSERT한빛유통491 / 700

상황

한빛유통은 매일 01:30에 PG사가 보내는 전날 결제 정산 파일(CSV, 약 10만 줄)을 대사용 테이블 T_PG_STLM에 올린다. 2020년에 만든 프로시저가 UTL_FILE로 한 줄씩 읽고 쉼표로 잘라 한 행씩 INSERT한다. 파일이 커지면서 적재가 41초로 늘었다. 지난달부터는 매일 200여 줄이 오류 테이블 T_PG_LOAD_ERR로 빠진다. PG사가 금액 칸을 "12,900"처럼 따옴표로 감싸고 천 단위 쉼표를 넣기 시작한 뒤부터다. 조민우: "오류 난 줄은 아침마다 제가 손으로 넣고 있어요."

테이블과 인덱스

파일 pg_stlm_YYYYMMDD.csv: DB 서버의 디렉터리 객체 PG_DIR에 PG사가 올린다. 첫 줄은 머리글이다. 10월 28일 파일은 거래 102,000줄(약 9.8MB)이다. 칸은 거래번호, 주문번호, 거래일시(2024-10-28 13:05:41), 거래구분(A 승인, C 취소), 금액, 수수료 순이다.
T_PG_STLM(이 문제용): PG_TID VARCHAR2(20) · ORD_SEQ NUMBER · TRX_DTT DATE · TRX_TP VARCHAR2(1) · TRX_AMT NUMBER · FEE_AMT NUMBER · LOAD_DT DATE · PK PK_T_PG_STLM (PG_TID) · 날마다 약 10만 행이 쌓이고 지우지 않는다. 낮에는 대사 화면이 읽기만 한다. 트리거와 FK는 없다.
T_PG_LOAD_ERR: 넣지 못한 줄과 오류 메시지를 남기는 테이블
운영 DB(19c)는 ARCHIVELOG이고 T_PG_STLM은 LOGGING이다. 01:30 ~ 02:30에는 이 테이블을 쓰는 다른 세션이 없다.

원본 SQL

sql

PROCEDURE P_PG_LOAD(p_dt VARCHAR2) IS                         -- p_dt = '20241028'
  f       UTL_FILE.FILE_TYPE;
  v_line  VARCHAR2(400);
  n       PLS_INTEGER := 0;
BEGIN
  f := UTL_FILE.FOPEN('PG_DIR', 'pg_stlm_' || p_dt || '.csv', 'r');
  UTL_FILE.GET_LINE(f, v_line);                                 -- 머리글 건너뛰기
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(f, v_line);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN EXIT;                             -- 파일 끝
    END;
    BEGIN
      INSERT INTO T_PG_STLM (PG_TID, ORD_SEQ, TRX_DTT, TRX_TP, TRX_AMT, FEE_AMT, LOAD_DT)
      VALUES (REGEXP_SUBSTR(v_line, '[^,]+', 1, 1),
              TO_NUMBER(REGEXP_SUBSTR(v_line, '[^,]+', 1, 2)),
              TO_DATE(REGEXP_SUBSTR(v_line, '[^,]+', 1, 3), 'YYYY-MM-DD HH24:MI:SS'),
              REGEXP_SUBSTR(v_line, '[^,]+', 1, 4),
              TO_NUMBER(REGEXP_SUBSTR(v_line, '[^,]+', 1, 5)),
              TO_NUMBER(REGEXP_SUBSTR(v_line, '[^,]+', 1, 6)),
              SYSDATE);
      n := n + 1;
      IF MOD(n, 1000) = 0 THEN COMMIT; END IF;
    EXCEPTION
      WHEN OTHERS THEN
        INSERT INTO T_PG_LOAD_ERR (LOAD_DT, LINE_TXT, ERR_MSG) VALUES (SYSDATE, v_line, SQLERRM);
    END;
  END LOOP;
  UTL_FILE.FCLOSE(f);
  COMMIT;
END;

실행계획 학습용 예시

xplan

[10월 28일 적재 — 세션 통계]
  경과 시간                       41.3 초
  CPU used by this session        33.8 초
  UTL_FILE.GET_LINE 호출          102,002
  INSERT 실행 (T_PG_STLM)         102,000   (성공 101,786 · 실패 214)
  INSERT 실행 (T_PG_LOAD_ERR)     214
  user commits                    102
  redo size                       약 34.6 MB
  undo change vector size         약 7.2 MB

[DBMS_HPROF — 시간이 쓰인 곳]
  T_PG_STLM INSERT (SQL · 줄마다 REGEXP_SUBSTR 여섯 번 포함)   102,000 부름   29.7 초
  UTL_FILE.GET_LINE                                            102,002 부름    6.4 초
  COMMIT                                                           102 부름    1.2 초
  나머지 (PL/SQL 루프 · 예외 처리)                                             4.0 초

[T_PG_LOAD_ERR — 10월 28일]
  214줄 · 모두 ORA-01722: invalid number · 모두 금액이 "12,900"처럼 따옴표와 쉼표로 온 줄
  예) T2410280000123,88213,2024-10-28 13:05:41,A,"12,900",387

문제

적재 시간이 어디에 쓰이는지 세션 통계와 HPROF로 설명하고, 214줄이 오류로 빠진 이유를 쓰시오.
파일을 SQL로 읽는 외부 테이블과 직접 경로 적재로 다시 작성하시오. 잘못된 줄을 어떻게 남기고, 파일이 통째로 깨졌을 때는 어떻게 멈출지도 쓰시오.
고친 방법이 기대는 전제와 그 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 번만: 파일을 테이블처럼 읽는 외부 테이블 (금액 · 수수료는 문자로 받아 SELECT 에서 숫자로 바꾼다)
CREATE TABLE EXT_PG_STLM (
  PG_TID   VARCHAR2(20),
  ORD_SEQ  NUMBER,
  TRX_DTT  DATE,
  TRX_TP   VARCHAR2(1),
  TRX_AMT  VARCHAR2(20),
  FEE_AMT  VARCHAR2(20)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY PG_DIR
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    SKIP 1
    BADFILE PG_DIR:'pg_stlm_load.bad'
    LOGFILE PG_DIR:'pg_stlm_load.log'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    ( PG_TID   CHAR(20),
      ORD_SEQ  CHAR(18),
      TRX_DTT  CHAR(19) DATE_FORMAT DATE MASK "YYYY-MM-DD HH24:MI:SS",
      TRX_TP   CHAR(1),
      TRX_AMT  CHAR(20),
      FEE_AMT  CHAR(20) )
  )
  LOCATION ('pg_stlm.csv')
)
REJECT LIMIT 100;            -- 잘못된 줄이 100줄을 넘으면 SELECT 가 실패한다 (깨진 파일은 한 줄도 넣지 않는다)

PROCEDURE P_PG_LOAD(p_dt VARCHAR2) IS                       -- p_dt = '20241028'
BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE EXT_PG_STLM LOCATION (''pg_stlm_' || p_dt || '.csv'')';

  INSERT /*+ APPEND */ INTO T_PG_STLM (PG_TID, ORD_SEQ, TRX_DTT, TRX_TP, TRX_AMT, FEE_AMT, LOAD_DT)
  SELECT PG_TID, ORD_SEQ, TRX_DTT, TRX_TP,
         TO_NUMBER(REPLACE(TRX_AMT, ',')),
         TO_NUMBER(REPLACE(FEE_AMT, ',')),
         SYSDATE
    FROM EXT_PG_STLM;
  COMMIT;
  -- 넣은 행 수 + BADFILE 줄 수 = 파일의 거래 줄 수인지 LOGFILE 로 확인한다
END;

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

xplan

[INSERT — DISPLAY_CURSOR (ALLSTATS LAST)]
----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT            |             |      1 |        |      0 |00:00:01.63 |    1957 |
|   1 |  LOAD AS SELECT             | T_PG_STLM   |      1 |        |      0 |00:00:01.63 |    1957 |
|   2 |   EXTERNAL TABLE ACCESS FULL| EXT_PG_STLM |      1 |    102K|    102K|00:00:00.92 |      27 |
----------------------------------------------------------------------------------------------------

[10월 28일 파일 — 세션 통계]
  경과 1.6초 · INSERT 실행 1 · user commits 1 (+ ALTER TABLE 의 암시적 커밋)
  redo size 약 15.8 MB (LOGGING 이라 직접 경로라도 데이터 redo 는 남는다 · PK 인덱스 몫 포함) · undo 약 0.6 MB
  넣은 행 102,000 (따옴표 금액 214줄 포함) · BADFILE 0줄

[시험]
  거래일시가 '2024-10-28 25:61:00' 인 줄 3개를 섞은 파일 → 그 3줄만 BADFILE, 사유는 LOGFILE · 101,997행 적재
  중간에 칸이 밀려 잘못된 줄이 4,800줄인 파일 → REJECT LIMIT 을 넘어 ORA-30653 · 0행 · 롤백

모범 답안 서술

41.3초의 대부분은 파일을 한 줄씩 다루는 데서 나온다. UTL_FILE.GET_LINE이 102,002번, T_PG_STLM INSERT가 102,000번 실행되고, INSERT마다 REGEXP_SUBSTR로 줄을 여섯 번 자른다. HPROF로 보면 INSERT 102,000번이 29.7초(자르기 포함), 줄 읽기가 6.4초, 1,000줄마다 하는 커밋이 1.2초다. 한 행씩 넣으므로 redo와 undo도 행마다 따로 생긴다(redo 34.6MB). 214줄이 빠진 것은 자르는 방식 때문이다. [^,]+는 쉼표마다 자르므로 따옴표 안의 천 단위 쉼표까지 칸 구분으로 본다. "12,900"이 "12와 900" 두 조각으로 나뉘어 금액 칸에 "12가 들어가고, TO_NUMBER가 ORA-01722로 실패해 오류 테이블로 갔다. 고친 판은 파일을 외부 테이블로 연다. ORGANIZATION EXTERNAL(ORACLE_LOADER)은 파일을 테이블처럼 SELECT하게 해 주므로, 적재는 INSERT /*+ APPEND */ … SELECT 한 문장이 된다. 줄을 자르는 일은 로더가 하고, OPTIONALLY ENCLOSED BY '"'는 따옴표 안의 쉼표를 칸 구분으로 보지 않는다. 금액은 문자로 받아 SELECT에서 쉼표를 지우고 숫자로 바꾼다. 직접 경로라 고수위선 위에 블록째 쓰고 데이터 undo를 거의 만들지 않는다. 경과는 41.3초 → 1.6초, INSERT 실행은 102,000번 → 1번, 커밋은 102번 → 1번, redo는 34.6MB → 15.8MB가 된다(LOGGING이라 데이터 redo는 남는다). 214줄도 빠지지 않는다. 잘못된 줄은 BADFILE에 줄 그대로 남고 사유는 LOGFILE에 남는다. 예외를 잡아 오류 테이블에 넣던 일을 로더가 대신한다. REJECT LIMIT 100은 잘못된 줄이 100줄을 넘으면 SELECT를 실패시켜 INSERT 전체를 롤백한다. 파일이 중간에 잘리거나 칸이 밀려 통째로 깨진 날에는 반쯤 들어간 정산 대신 0행과 오류가 남는다. 적재 뒤에는 넣은 행 수와 BADFILE 줄 수의 합이 파일의 거래 줄 수와 같은지 본다. 전제와 대가도 있다. 외부 테이블은 DB 서버에서 보이는 디렉터리의 파일만 읽는다(UTL_FILE과 같은 전제). 날마다 바뀌는 파일 이름은 ALTER TABLE … LOCATION(DDL이라 암시적으로 커밋한다)으로 바꾼다. 직접 경로 적재 동안에는 T_PG_STLM에 배타 잠금(TM 6)이 걸리므로, 대사 화면이 쓰지 않는 01:30 ~ 02:30에만 돌린다. 같은 날 파일을 두 번 넣으면 PK 위반으로 문장 전체가 실패해 아무것도 들어가지 않는다.

해설

sqlldr도 답이다. SQL*Loader의 직접 경로(direct=true)도 블록째 넣고 BAD 파일을 남긴다. 다만 DB 밖의 프로그램이라 배치 서버에서 띄워야 하고, 적재와 뒤 단계(대사 SQL)를 한 프로시저로 묶기 어렵다. 외부 테이블은 SQL 한 문장이라 조인, 변환, 걸러내기를 적재와 함께 할 수 있다.
UTL_FILE을 BULK로 바꾸면? 줄을 배열에 모아 FORALL로 넣으면 INSERT 실행은 100번 남짓으로 준다. 그래도 줄 읽기와 자르기는 PL/SQL에서 줄마다 하고, 따옴표 처리는 직접 짜야 한다(교재 15화 §5).
파일이 커지면 SELECT /*+ PARALLEL(e 4) */ … FROM EXT_PG_STLM e와 ALTER SESSION ENABLE PARALLEL DML로 파일을 나눠 읽고 나눠 넣을 수 있다. 병렬 DML을 켜지 않으면 적재는 한 프로세스가 한다(DML-043).
외부 테이블은 통계가 없으면 행 수를 짐작으로 잡는다. 다른 테이블과 조인해 넣는다면 DBMS_STATS.GATHER_TABLE_STATS로 외부 테이블의 통계를 모아 두거나 동적 샘플링에 맡긴다.
날마다 쌓이는 테이블에 APPEND로 넣는 것은 지우지 않는 동안은 괜찮다. 오래된 정산을 DELETE로 지우기 시작하면 비운 블록을 다시 쓰지 못해 테이블이 커진다(DML-044).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황