by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

데이터베이스 Call이 성능에 미치는 영향




03. 데이터베이스 Call이 성능에 미치는 영향

가정 : '월요금납부실적' 테이블을 이용해 '납입방법별_월요금집계' 테이블 형태로 가공하는 ETL 배치 프로그램이 필요하다.

패턴1)

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;
  • 처리해야 할 월요금납부실적이 100만 건이면 이 테이블에 대한 Fetch Call이 100만 번(Array 단위 Fetch가 작동하지 않을 ?), 납입방법별_월요금집계 테이블로의 insert를 위한 Execute Call이 최대 100만번, 따라서 최대 200만 번의 데이터베이스 Call이 발생.
    (PL/SQL에서는 커서를 자동으로 캐싱하므로 insert를 위한 Parse Call은 소량만 발생)
  • PL/SQL문으로 코딩하면 네트워크 트래픽 없는 Recursive Call이므로 제법 빠르게 수행.

패턴2)

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, param2); 
    st.setString(3, param3); 
    st.setLong(4, param4); 
    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(); 
  } 
 
  public static void main(String[] args) throws Exception{ 
 
    long btm = System.currentTimeMillis(); 
 
    Connection con = getConnection(); 
 
    execute(con, "200903"); 
 
    System.out.println("elapsed time : " + (System.currentTimeMillis() - btm)); 
 
    releaseConnection(con); 
  }
  • 월요금납부실적 테이블에 30,000건을 넣고 실제 테스트
    CREATE TABLE 월요금납부실적 
    AS 
    SELECT TO_CHAR(OBJECT_ID) 고객번호 
         , '200903' 납입월 
         , round(dbms_random.value(1000, 10000), -2) 지로 
         , round(dbms_random.value(1000, 10000), -2) 자동이체 
         , round(dbms_random.value(1000, 10000), -2) 신용카드 
         , round(dbms_random.value(1000, 10000), -2) 핸드폰 
         , round(dbms_random.value(1000, 10000), -2) 인터넷 
    FROM   ALL_OBJECTS  
    WHERE ROWNUM <= 30000; 
     
    DROP TABLE 납입방법별_월요금집계 PURGE; 
     
    CREATE TABLE 납입방법별_월요금집계 ( 
      고객번호      NUMBER 
    , 납입월        VARCHAR2(6) 
    , 납입방법코드  VARCHAR2(1) 
    , 납입금액       NUMBER 
    ) ;
    

패턴1 Trace 결과)

SELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷 
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)
  • 7.26초 소요(실제 10.28초 소요)
  • select와 insert를 합쳐 총 18만번 가량의 데이터베이스 Call 발생.
  • 자동으로 커서를 캐싱하므로 Parse Call이 5번

패턴2 Trace 결과)

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
  • 126.82초 소요
  • 총 303,000번 가량의 데이터베이스 Call 발생
  • select 문에서 Fetch Call이 앞에서보다 1/10 수준으로 준 것은 JAVA에서 FetchSize 기본 설정이 10이기 때문.
  • insert문은 애플리케이션 커서 캐싱 기법을 사용하지 않았으므로 Execute Call과 같은 횟수만큼 Parse Call 발생
  • 순수하게 서버에서 처리한 시간은 10여 초
  • 나머지는 네트워크 구간에서 소비한 시간, 데이터베이스 Call이 발생할 때마다 매번 OS로부터 CPU와 메모리 리소스를 할당받으려고 소비한 시간
  • User Call이 Recursive Call에 비해 더 심각한 부하를 일으키는 이유

One-SQL로 통합)

INSERT INTO 납입방법별_월요금집계(납입월,고객번호,납입방법코드,납입금액) 
SELECT x.납입월, x.고객번호 
     , CHR(64 + Y.NO) 납입방법코드 
     , DECODE(Y.NO, 1, 지로, 2, 자동이체, 3, 신용카드, 4, 핸드폰, 5, 인터넷) 
FROM   월요금납부실적 x 
     ,(SELECT LEVEL NO FROM DUAL CONNECT BY LEVEL <= 5) y 
WHERE x.납입월 = '200903' 
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.190        1.304          0       1590       3835     150000 
Fetch        0    0.000        0.000          0          0          0          0 
------- ------ -------- ------------ ---------- ---------- ---------- ---------- 
Total        2    1.190        1.304          0       1590       3835     150000 
 
Misses in library cache during parse: 0 
Optimizer goal: FIRST_ROWS 
Parsing user: IBS_ADM (ID=41) 
 
Rows     Row Source Operation 
-------  --------------------------------------------------- 
      0  STATEMENT 
 150000   NESTED LOOPS  (cr=710 pr=0 pw=0 time=600157 us) 
      5    VIEW  (cr=0 pr=0 pw=0 time=96 us) 
      5     CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=87 us) 
      1      FAST DUAL  (cr=0 pr=0 pw=0 time=4 us) 
 150000    TABLE ACCESS FULL 월요금납부실적 (cr=710 pr=0 pw=0 time=450321 us) 
 
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)  
) ; 
 
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: 0 
Optimizer goal: FIRST_ROWS 
Parsing user: IBS_ADM (ID=41) 
 
Rows     Row Source Operation 
-------  --------------------------------------------------- 
      0  STATEMENT 
 150000   MERGE JOIN  (cr=142 pr=0 pw=0 time=1004484 us) 
      5    SORT JOIN (cr=0 pr=0 pw=0 time=115 us) 
      5     VIEW  (cr=0 pr=0 pw=0 time=70 us) 
      5      CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=55 us) 
      1       FAST DUAL  (cr=0 pr=0 pw=0 time=4 us) 
 150000    FILTER  (cr=142 pr=0 pw=0 time=854395 us) 
 150000     SORT JOIN (cr=142 pr=0 pw=0 time=254934 us) 
  30000      VIEW  (cr=142 pr=0 pw=0 time=60074 us) 
  30000       TABLE ACCESS FULL 월요금납부실적 (cr=142 pr=0 pw=0 time=72 us)
  
  • 1초 소요
  • Array Processing 기법을 활용하면, DBMS 외부에서 수행되는 JAVA 같은 프로그램에서도 네트워크 트래픽을 획기적으로 줄여 줘 굳이 One-SQL로 작성하지 않더라도 같은 수준의 성능개선 효과를 얻을 수 있다(4절 내용)

가정 : "납입방법별_월요금집계" 테이블을 읽어 "월요금납부실적"을 가공

패턴1)

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    D.고객번호(+) = K.고객번호 
AND    E.고객번호(+) = K.고객번호 ; 
 
- I/O 비효율 개선 
INSERT INTO 월요금납부실적 
(고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷) 
SELECT 고객번호, 납입월 
     , NVL(SUM(DECODE(납입방법코드, 'A', 납입금액)), 0) 지로 
     , NVL(SUM(DECODE(납입방법코드, 'B', 납입금액)), 0) 자동이체 
     , NVL(SUM(DECODE(납입방법코드, 'C', 납입금액)), 0) 신용카드 
     , NVL(SUM(DECODE(납입방법코드, 'D', 납입금액)), 0) 핸드폰 
     , NVL(SUM(DECODE(납입방법코드, 'E', 납입금액)), 0) 인터넷 
FROM   납입방법별_월요금집계 
WHERE  납입월 = '200903' 
GROUP BY 고객번호, 납입월 ;
  • 또 다른 사례 (위시리스트에 5개 상품 넣기)
  • 상품별로 각각 넣으면 5번의 Parse Call과 5번의 Execute Call 발생
  • 상품을 일괄로 넣는 방식으로 처리 (p.362 참고)

문서에 대하여

  • 최초작성자 : [김종원]
  • 최초작성일 : 2010년 01월 09일
  • 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.