오라클 성능 고도화 원리와 해법 I (2009년)
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'를 참고하였습니다.*
"코어 오라클 데이터베이스 스터디 모임" 에서 2009년에 "오라클 성능 고도화 원리와 해법 I " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3046
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.