일별 배치 튜닝 사례

특정 월말일에 일별 배치가 지연되어 곤란을 겪고 있다는 요청이 있었다. 조금 특별한 일별 배치 튜닝 사례를 살펴보자. 

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT a.dt, b.cd, LPAD ('X', 100, 'X') AS vl
  FROM (SELECT DATE '2050-01-01' + ROWNUM - 1 AS dt FROM XMLTABLE ('1 to 31')) a
     , (SELECT ROWNUM AS cd FROM XMLTABLE ('1 to 10000')) b;

CREATE INDEX t1_x1 on t1 (dt) NOLOGGING;

SET SERVEROUT ON

문제가 된 배치는 아래와 패턴이었다. 1달 이전 과거 데이터를 삭제하고, 당일 데이터를 입력하는 전형적인 일별 배치 패턴이다. 월말일인 2월 28일의 수행 시간이 증가한 것을 확인할 수 있다.

-- 2
DECLARE
    v_tm    PLS_INTEGER;
    v_dt    DATE;
BEGIN
    FOR i IN 1 .. 30
    LOOP
        v_tm := DBMS_UTILITY.GET_TIME ();
        v_dt := DATE '2050-01-31' + i;

        DELETE FROM t1 WHERE dt <= ADD_MONTHS (v_dt, -1);
        COMMIT;
        INSERT INTO t1 SELECT v_dt, ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000');
        COMMIT;

        v_tm := DBMS_UTILITY.GET_TIME () - v_tm;
        DBMS_OUTPUT.PUT_LINE (TO_CHAR (v_dt, 'YYYY-MM-DD') || ' : ' || TO_CHAR (v_tm / 100, 'FM90.90'));
    END LOOP;
END;
/
...

2050-02-26 : 0.17
2050-02-27 : 0.16
2050-02-28 : 0.87 -- !
2050-03-01 : 0.25
2050-03-02 : 0.11

PL/SQL 처리가 정상적으로 완료되었습니다.

개선안은 아래와 같다. DELETE 문에 ADD_MONTHS 함수 대신 산술 연산자를 사용했다. 원인은 단순했다. 2월 27일까지는 과거 데이터를 1일씩 삭제했지만, 2월 28일에 4일치 데이터(1월 28, 29, 30, 31일)를 삭제했기 때문이다. ADD_MONTHS 함수의 월말일 계산 방식으로 인해 벌어진 현상이었다.(ADD_MONTHS (2050-02-28, -1) 결과는 2050-01-31임) 과거 데이터 삭제시 월 기준보다 일자 기준을 사용하는 편이 바람직하다.

-- 3
DECLARE
    v_tm    PLS_INTEGER;
    v_dt    DATE;
BEGIN
    FOR i IN 1 .. 30
    LOOP
        v_tm := DBMS_UTILITY.GET_TIME ();
        v_dt := DATE '2050-01-31' + i;

        DELETE FROM t1 WHERE dt <= v_dt - 31;
        COMMIT;
        INSERT INTO t1 SELECT v_dt, ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000');
        COMMIT;

        v_tm := DBMS_UTILITY.GET_TIME () - v_tm;
        DBMS_OUTPUT.PUT_LINE (TO_CHAR (v_dt, 'YYYY-MM-DD') || ' : ' || TO_CHAR (v_tm / 100, 'FM90.90'));
    END LOOP;
END;
/
..
2050-02-26 : 0.39
2050-02-27 : 0.19
2050-02-28 : 0.17 -- !
2050-03-01 : 0.22
2050-03-02 : 0.36

PL/SQL 처리가 정상적으로 완료되었습니다.