Use Call과 Recursive Call의 성능차이

다음의 테이블을 살펴보자.


3.JPG


납입방법별_월요금집계의 데이타를 기초로 월요금납부실적의 데이터를 작성하려한다.


○PL/SQL을 이용하여 작성한 예를 보자. (Recursive Call)

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
INSERT INTO 납입방법별_월요금집계 ...지로....
END IF;

IF REC.자동이체 > 0 THEN
INSERT INTO 납입방법별_월요금집계 ...자동이체....
END IF;

IF REC.신용카드 > 0 THEN
INSERT INTO 납입방법별_월요금집계 ....신용카드...
END IF;

IF REC.핸드폰 > 0 THEN
INSERT INTO 납입방법별_월요금집계 ...헨드폰....
END IF;

IF REC.인터넷 > 0 THEN
INSERT INTO 납입방법별_월요금집계 ...인터넷....
END IF;
END LOOP;

CLOSE C;
END;


   데이터가 100만건일 경우 Fetch Call이 100만번, Execute Call이 100만번 발생하게 된다.


○다음은 Java로 작성한 예를 살펴보자. (User Call)

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.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 (...지로...);
if(자동이체 > 0) insertData (...자동이체...);
if(신용카드 > 0) insertData (...신용카드...);
if(핸드폰 > 0) insertData (...핸드폰...);
if(인터넷 > 0) insertData (...인터넷...);
}
rs.close();
stmt.close();
}


    전술한 PL/SQL과 완전 동일한 방식으로 작성되었다. (루프내 Fetch, Execute)


●위의 두경우에 대해 3만건의 데이터를 준비, 성능의 차이를 살펴보자. 

    ① PL/SQL의 경우 : Recursive Call

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)


    Fetch Call이 3만번가량, Execute Call 이 15만번가량 발생함을 알수 있다. (소요시간: 약7초대)


   ② Java의 경우 : User 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


    Fetch Call이 3천번가량, Execute Call이 30만번가량 발생함을 알수 있다. (소요시간: 약126초대)


결론.

Java의 경우, FetchSize가 기본 10으로 설정된 관계로 Select에서의 Fetch Call이 10배 좋음을 알수 있다. (Array Processing)

그러나, 캐싱기법을 사용하지 않는 Insert의 결과를 보면, 양쪽 동일하게 Execute Call이 발생함을 알수 있다.

소요시간을 보면, Recursive Call이 약 7초정도, User Call이 약 126초정도로 엄청난 차이를 보이는데.

서버단, 7초정도의 처리를 제외한 나머지인 119초라는 시간이 처리이외의 네트웍트래픽, 관련리소스 할당에 소요되는 시간등임을 알수 있다.

즉, User Call이 Recursive Call비해 현저하게 부하가 걸림.



One-SQL에 의한 성능향상

위의 SQL은 Insert Select문으로 전환함으로써, Execute Call의 횟수를 격감시킬수 있다.


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



I/O효율을 향상시키는 쿼리작성


바보같은 코드를 보자. (6년전에 실제 본적있음)

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.고객번호 ;


   일반적인 코드를 보자.

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 고객번호, 납입월 ;



★I/O효율의 핵심은 동일 레코드를 반복 액세스하지 않고 얼마만큼 블록 액세스 양을 최소화 하느냐에 달렸다.

   One-SQL과 Cross-Table을 만드는 패턴등으로 I/O효율을 높일수 있다.