그림5-3.jpg



- "월요금 납부 실적"을 이용하여 "납입방법별_월요금집계" 로 가공하는 ETL 배치 프로그램


1. PL/SQL 로 작성한 일반적 작성 예


DECLARE 

  CURSOR C (INPUT_MONTH VARCHAR2) IS

     SELECT 고객번호,납입월,지로,자동이체,신용카드,핸드폰,인터넷

       FROM 월요금납부실적

      WHERE 납입월 = INPUT_MONTH;


  REC C%ROWTYPE;

  LTYPE VARCHAR2(1);


BEGIN


  OPEN C('200903'); 


  LOOP

    FETCH C INTO REC;

    EXIT WHEN C%NOTFOUND;

    IF REC.지로 > 0 THEN
      LTYPE = 'A'
      INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 납입방법코드, 납입금액)  
      VALUES (REC.고객번호, REC.납입월, LTYPE, REC.자동이체);
    END IF;

    IF REC.자동이체 > 0 THEN
      LTYPE = 'B'
      INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 납입방법코드, 납입금액)  
      VALUES (REC.고객번호, REC.납입월, LTYPE, REC.자동이체);
    END IF;

    IF REC.신용카드 > 0 THEN
      LTYPE = 'C'
      INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 납입방법코드, 납입금액)  
      VALUES (REC.고객번호, REC.납입월, LTYPE, REC.자동이체);
    END IF;

    IF REC.핸드폰 > 0 THEN
      LTYPE = 'D'
      INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 납입방법코드, 납입금액)  
      VALUES (REC.고객번호, REC.납입월, LTYPE, REC.자동이체);
    END IF;

    IF REC.인터넷 > 0 THEN
      LTYPE = 'E'
      INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 납입방법코드, 납입금액)  
      VALUES (REC.고객번호, REC.납입월, LTYPE, REC.자동이체);
    END IF;
  END LOOP;


  CLOSE C;

END; 


  • PL/SQL 코딩이므로 네트웍 트래픽이 없는 Recursive call


2. Java로 작성된 예

public class JavaLoopQuery{

  public static void insertData( Connection con

                               , String param1

                               , String param2

                               , String param3

                               , long param4) throws Exception{


    String SQLStmt = "INSERT INTO 납입방법별_월요금집계  "

            + "(고객번호, 납입월, 납입방법코드, 납입금액) "

            + "VALUES(?, ?, ?, ?)";

    PreparedStatement st = con.prepareStatement(SQLStmt);

    st.setString(1, param1);

    st.setString(2, param1);

    st.setString(3, param1);

    st.setLong(4, param1);

    st.execute();

    st.close();

  }


  public static void execute(Connection con, String input_month)

  throws Exception {

    String SQLStmt = "SELECT 고객번호, 납입월"

                   + "     , 지로, 자동이체, 신용카드, 핸드폰, 인터넷 "

                   + "FROM   월요금납부실적 "

                   + "WHERE  납입월 = ?";

    PreparedStatement stmt = con.prepareStatement(SQLStmt);

    stmt.setString(1, input_month);

    ResultSet rs = stmt.executeQuery();

    while(rs.next()){

      String 고객번호 = rs.getString(1);

      String 납입월 = rs.getString(2);

      long 지로 = rs.getLong(3);

      long 자동이체 = rs.getLong(4);

      long 신용카드 = rs.getLong(5);

      long 핸드폰 = rs.getLong(6);

      long 인터넷 = rs.getLong(7);

      if(지로 > 0)     insertData (con,고객번호, 납입월, "A", 지로);

      if(자동이체 > 0) insertData (con,고객번호, 납입월, "B", 자동이체);

      if(신용카드 > 0) insertData (con,고객번호, 납입월, "C", 신용카드);

      if(핸드폰 > 0)   insertData (con,고객번호, 납입월, "D", 핸드폰);

      if(인터넷 > 0)   insertData (con,고객번호, 납입월, "E", 인터넷);

    }

    rs.close();

    stmt.close();

  }


  static Connection getConnection() throws Exception { ............ }

  static void releaseConnection(Connection con ) throws Exception { ........... }

  

  public static void main(String[] args) throws Exception{

     Connection con = getConnection();

     execute(con,"200903");

     releaseConnection(con);

  }

}



3. 수행 결과

  - PL/SQL 수행 결과 (소요시간 7.26초, 18만번 가량의 call 발생)

ELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷

FROM 월요금납부실적 WHERE 납입월 = :B1


call       count       cpu    elapsed       disk      query    current        rows

------- --------  -------- ---------- ---------- ---------- ----------  ----------

Parse          1      0.00       0.00          0          0          0           0

Execute        1      0.00       0.00          0          1          0           0

Fetch      30001      0.60       0.70         40      30004          0       30000

------- --------  -------- ---------- ---------- ---------- ----------  ----------

total      30003      0.60       0.71         40      30005          0       30000


Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: FIRST_ROWS

Parsing user id: 41     (recursive depth: 1)


Rows     Row Source Operation

-------  ---------------------------------------------------

  30000  TABLE ACCESS FULL 월요금납부실적 (cr=30004 pr=40 pw=0 time=420269 us)


INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 납입방법코드, 납입금액)

VALUES (:B4 , :B3 , :B2 , :B1 )


call       count       cpu    elapsed       disk      query    current        rows

------- --------  -------- ---------- ---------- ---------- ----------  ----------

Parse          5      0.00       0.00          0          0          0           0

Execute   150000      6.34       8.41          0       2445     162371      150000

Fetch          0      0.00       0.00          0          0          0           0

------- --------  -------- ---------- ---------- ---------- ----------  ----------

total     150005      6.34       8.41          0       2445     162371      150000


Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: FIRST_ROWS

Parsing user id: 41     (recursive depth: 1)


   - Java 수행 결과 (소요시간 126.82초, 30만번 가량의 call 발생)

SELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷

FROM 월요금납부실적 WHERE 납입월 = :1



call      count       cpu    elapsed       disk      query    current        rows

------- -------  -------- ---------- ---------- ---------- ----------  ----------

Parse         1      0.00       0.00          0          0          0           0

Execute       1      0.00       0.02          2          2          0           0

Fetch      3001      0.14       0.18          9       3135          0       30000

------- -------  -------- ---------- ---------- ---------- ----------  ----------

total      3003      0.14       0.20         11       3137          0       30000


Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 54


Rows     Row Source Operation

-------  ---------------------------------------------------

  30000  TABLE ACCESS FULL 월요금납부실적 (cr=3135 pr=9 pw=0 time=165106 us)


INSERT INTO 납입방법별_월요금집계

(고객번호, 납입월, 납입방법코드, 납입금액)

VALUES (:1 , :2 , :3 , :4 )


 call     count       cpu    elapsed       disk      query    current        rows

------- ---------  -------- ---------- ---------- ---------- ----------  ----------

Parse      150000      1.98       2.00          0          0          0           0

Execute    150000      8.75       9.20         27     150143     606212      150000

Fetch           0      0.00       0.00          0          0          0           0

------- ---------  -------- ---------- ---------- ---------- ----------  ----------

total      300000     10.73      11.20         27     150143     606212      150000


Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 54



   - 결론

  • Java의 FetchSize의 Default 값이 10이기 때문에 Select 문에서 Java Fetch Call 이 PL/SQL 보다 1/10 줄었음
  • Java에서 insert 문은 애플리케이션커서캐싱 기법을 사용하지 않았으므로 Execute Call = Parse Call 발생
  • PL/SQL에서는 자동으로 커서를 캐싱하므로 Parse Call = 5 
  • java의 총소요 시간이 126.28초 인데 반해 서버의 일량은 10여초에 불과
       - 네트웍크 구간소요, call 발생시 OS에 리소스를 할당받으려고 소비한 시간


   - One Sql 로 수행한 결과 (call의 감소로 1초가 채 걸리지 않는다.)     

INSERT INTO 납입방법별_월요금집계(납입월,고객번호,납입방법코드,납입금액)

SELECT x.납입월

      ,x.고객번호

      ,CHR(64 + Y.NO) 납입방법코드

      ,DECODE(Y.NO, 1, 지로, 2, 자동이체, 3, 신용카드, 4, 핸드폰, 5, 인터넷)

  FROM (SELECT 1 DUMMY,납입월,고객번호,지로,자동이체,신용카드,핸드폰,인터넷

          FROM 월요금납부실적

         WHERE 납입월 = '200903'

        ) x

       ,(SELECT 1 DUMMY, LEVEL NO FROM DUAL CONNECT BY LEVEL <= 5) y

 WHERE x.DUMMY = y.DUMMY

   AND y.NO IN (DECODE(지로, 0, NULL, 1)

               ,DECODE(자동이체, 0, NULL, 2)

               ,DECODE(신용카드, 0, NULL, 3)

               ,DECODE(핸드폰, 0, NULL, 4)

               ,DECODE(인터넷, 0, NULL, 5)

                )

;


Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Parse        1    0.000        0.000          0          0          0          0

Execute      1    1.470        1.550          0       1037       3778     150000

Fetch        0    0.000        0.000          0          0          0          0

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Total        2    1.470        1.550          0       1037       3778     150000


Misses in library cache during parse:1

Optimizer mode: ALL_ROWS

Parsing user id: 54



- 소트머지조인 또는 해시 조인으로 유도 하기 위한 쿼리

INSERT INTO 납입방법별_월요금집계(납입월,고객번호,납입방법코드,납입금액)

SELECT /*+ USE_MERGE(X Y) NO_EXPAND NO_MERGE(X) */

       x.납입월

      ,x.고객번호

      ,CHR(64 + Y.NO) 납입방법코드

      ,DECODE(Y.NO, 1, 지로, 2, 자동이체, 3, 신용카드, 4, 핸드폰, 5, 인터넷)

  FROM (SELECT 1 DUMMY,납입월,고객번호,지로,자동이체,신용카드,핸드폰,인터넷

          FROM 월요금납부실적

         WHERE 납입월 = '200903'

        ) x

       ,(SELECT 1 DUMMY, LEVEL NO FROM DUAL CONNECT BY LEVEL <= 5) y

 WHERE x.DUMMY = y.DUMMY

   AND y.NO IN (DECODE(지로, 0, NULL, 1)

               ,DECODE(자동이체, 0, NULL, 2)

               ,DECODE(신용카드, 0, NULL, 3)

               ,DECODE(핸드폰, 0, NULL, 4)

               ,DECODE(인터넷, 0, NULL, 5)

                )

;


   - 납입방법별_월요금집계를 이용하야 월요금납부실적 가공시 빈번한 문제점

INSERT INTO 월요금납부실적

     (고객번호,납입월,지로,자동이체,신용카드,핸드폰,인터넷)

SELECT K.고객번호

      ,'200903' 납입월

      ,A.납입금액 지로

      ,B.납입금액 지로

      ,C.납입금액 지로

      ,D.납입금액 지로

      ,E.납입금액 지로

  FROM 고객 K

      ,(SELECT 고객번호,납입금액 FROM 납입방법별_월별요금집계

         WHERE 납입월 = '200903'

           AND 납입방법코드 = 'A'

       ) A

      ,(SELECT 고객번호,납입금액 FROM 납입방법별_월별요금집계

         WHERE 납입월 = '200903'

           AND 납입방법코드 = 'B'

       ) B

      ,(SELECT 고객번호,납입금액 FROM 납입방법별_월별요금집계

         WHERE 납입월 = '200903'

           AND 납입방법코드 = 'C'

       ) C

      ,(SELECT 고객번호,납입금액 FROM 납입방법별_월별요금집계

         WHERE 납입월 = '200903'

           AND 납입방법코드 = 'D'

       ) D

      ,(SELECT 고객번호,납입금액 FROM 납입방법별_월별요금집계

         WHERE 납입월 = '200903'

           AND 납입방법코드 = 'E'

       ) E

 WHERE A.고객번호(+) = K.고객번호       

   AND B.고객번호(+) = K.고객번호       

   AND C.고객번호(+) = K.고객번호       

   AND E.고객번호(+) = K.고객번호       

   AND E.고객번호(+) = K.고객번호       

;    

      


  • CALL 회수를 줄이려 ONE-SQL  QUERY를 구현하면서 I/O 효율을 무신 한 경우를 조심해야 한다.