3._데이터베이스_Call이_성능에_미치는_영향

2012.05.13 11:36

시와처 조회 수:3645

그림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 효율을 무신 한 경우를 조심해야 한다.

그림5-3.jpg [File Size:9.4KB/Download:193]

이 게시물을

번호	제목	글쓴이	날짜	조회 수
66	부록	남송휘	2012.06.05	2708
65	8._IO_효율화_원리	운영자	2012.06.05	4426
64	3._Deterministic_함수_사용_시_주의사항	정찬호	2012.05.29	4351
63	2._Cursor_Sharing	운영자	2012.05.28	6276
62	7._Result_캐시	운영자	2012.05.27	4426
61	3._Single_Block_vs._Multiblock_IO	정찬호	2012.05.22	4229
60	2._Memory_vs._Disk_IO	정찬호	2012.05.22	4446
59	1._블록_단위_IO	정찬호	2012.05.22	4211
58	6장._IO_효율화_원리	정찬호	2012.05.22	4085
57	1._Library_Cache_Lock_Pin	남송휘	2012.05.21	4290
56	6._RAC_캐시_퓨전	남송휘	2012.05.21	17797
55	5._Direct_Path_IO	남송휘	2012.05.21	7932
54	4._Prefetch	남송휘	2012.05.21	3889
53	8._PLSQL_함수_호출_부하_해소_방안	남송휘	2012.05.21	3773
52	5._Fetch_Call_최소화 [1]	박영창	2012.05.15	6065
51	7._PLSQL_함수의_특징과_성능_부하	남송휘	2012.05.14	6935
50	6._페이지_처리의_중요성	남송휘	2012.05.14	3379
49	4._Array_Processing_활용	시와처	2012.05.13	4043
»	3._데이터베이스_Call이_성능에_미치는_영향	시와처	2012.05.13	3645
47	10._Dynamic_SQL_사용_기준	남송휘	2012.05.07	4556

첫 페이지 1 2 3 4 끝 페이지

쓰기

태그

bysql.net

로그인

3._데이터베이스_Call이_성능에_미치는_영향

댓글 0