[1] Array Processing 이점
- 한 번의 SQL 수행으로 다량의 로우를 동시에 insert/update/delet 할 수 있다.
- 궁극적으로 SQL 수행시간과 CPU 사용량을 획기적으로 줄인다.
[2] Array Processing 구현
[예시1] "납입방법_월요금집계" 테이블 가공하기
public class JavaArrayProcessing{
public static void insertData( Connection con , PreparedStatement st , String param1 , String param2
, String param3 , long param4) throws Exception{
st.setString(1, param1);
st.setString(2, param2);
st.setString(3, param3);
st.setLong(4, param4);
st.addBatch();
}
public static void execute(Connection con, String input_month)
throws Exception {
long rows = 0;
String SQLStmt1 = ""SELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷 ""
+ ""FROM 월요금납부실적 WHERE 납입월 = ?"";
String SQLStmt2 = ""INSERT /*+ test3 */ INTO 납입방법별_월요금집계 ""
+ ""(고객번호, 납입월, 납입방법코드, 납입금액) VALUES(?, ?, ?, ?)"";
con.setAutoCommit(false);
PreparedStatement stmt1 = con.prepareStatement(SQLStmt1);
PreparedStatement stmt2 = con.prepareStatement(SQLStmt2);
stmt1.setFetchSize(1000);
stmt1.setString(1, input_month);
ResultSet rs = stmt1.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, stmt2, 고객번호, 납입월, ""A"", 지로);
if(자동이체 > 0) insertData (con, stmt2, 고객번호, 납입월, ""B"", 자동이체);
if(신용카드 > 0) insertData (con, stmt2, 고객번호, 납입월, ""C"", 신용카드);
if(핸드폰 > 0) insertData (con, stmt2, 고객번호, 납입월, ""D"", 핸드폰);
if(인터넷 > 0) insertData (con, stmt2, 고객번호, 납입월, ""E"", 인터넷);
*if(++rows%1000 == 0) stmt2.executeBatch();* }
rs.close();
stmt1.close();
stmt2.executeBatch();
stmt2.close();
con.commit();
con.setAutoCommit(true);
}
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);
}[결과] 위의 프로그램 실행결과 트레이스
SELECT 쿼리
- insert문에 대한 Execute Call이 30회만 발생했다.
- insert된 로우 수가 150,000건이므로 매번 5,000건씩 Array Processing
- 커서에서 Fetch되는 각 로우마다 5번씩 insert를 수행하고
- 1,000 로우마다 한번식 executeBatch를 수행하기 때문이다.
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.00 0 0 0 0
Fetch 31 0.06 0.29 0 169 0 30000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33 0.06 0.29 0 169 0 30000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 41
Rows Row Source Operation
------- ---------------------------------------------------
30000 TABLE ACCESS FULL 월요금납부실적 (cr=169 pr=0 pw=0 time=25 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 32 0.00 0.00
SQL*Net message from client 32 0.34 0.94
SQL*Net more data to client 359 0.00 0.03
********************************************************************************
================================================================================ INSERT 쿼리
INSERT /*+ test3 */ INTO 납입방법별_월요금집계 (고객번호, 납입월, 납입방법코드, 납입금액)
VALUES (:1, :2, :3, :4);
================================================================================
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 30 0.63 0.81 2 1142 5106 150000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31 0.63 0.82 2 1142 5106 150000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 31 0.00 0.00
SQL*Net message from client 31 0.00 0.01
SQL*Net more data from client 1358 0.00 0.00
db file sequential read 2 0.00 0.00
================================================================================- 총소요시간 : 2405msec
- select문에 대해 Fetch Call이 30,000건을 읽는데 31회만 발생했다.
- 1,000개 단위로 Array Fetch 하도록 조정했기 때문이다.(JAVA에서 기본값은 10이다.)
[비교] 앞 절에서 수행한 3가지 테스트와 해당 Array Processing 결과 정리 (150,000행 insert)
| 구분 | PL/SQL(Recursive) | JAVA | JAVA(Array) | one-sql |
|---|---|---|---|---|
| Parse Call | 5 | 150,000 | 1 | 1 |
| Execute Call | 150,000 | 150,000 | 30 | 1 |
| 총 소요시간 | 7.27초 | 126초 | 1.21초 | 0.9초 |
- 네트워크를 경유해 발생하는 데이터베이스 Call은 심각한 성능부하를 초래한다.
- One-SQL로 통합하지 않더라도 Array Processing 만으로 그에 버금가는 성능개선 효과를 갖는다.
- Array Processing의 효과를 극대화하려면 연속된 일련의 처리과정이 모두 Array 단위로 진행되야한다.
- Fetch 와 insert가 같이 Array Processing 할 수 있도록 해야한다.
[예시2] PL/SQL을 이용해 데이터를 Bulk로 1,000건씩 Fetch해서 Bulk로 insert하기
1. 데이터를 Bulk로 읽을 Source 테이블 생성
create table emp
as select object_id empno, object_name ename, object_type job ,round(dbms_random.value(1000,5000), -2) sal
,owner deptno, created hirdate
from all_objects where rownum <= 10000;2. 데이터를 Bulk로 넣을 Target 테이블 생성
create table emp2
as select * from emp where 1=2;3. PL/SQL 생성
DECLARE
l_fetch_size NUMBER DEFAULT 1000; -- 1,000건씩 Array 처리
CURSOR c IS
SELECT empno, ename, job, sal, deptno, hirdate FROM emp;
TYPE array_empno IS TABLE OF emp.empno%type;
TYPE array_ename IS TABLE OF emp.ename%type;
TYPE array_job IS TABLE OF emp.job%type;
TYPE array_sal IS TABLE OF emp.sal%type;
TYPE array_deptno IS TABLE OF emp.deptno%type;
TYPE array_hiredate IS TABLE OF emp.hirdate%type;
-- array 담는 변수 선언
l_empno array_empno := array_empno ();
l_ename array_ename := array_ename ();
l_job array_job := array_job ();
l_sal array_sal := array_sal ();
l_deptno array_deptno := array_deptno ();
l_hiredate array_hiredate := array_hiredate();
--프로시저 정의
PROCEDURE insert_t( p_empno IN array_empno
, p_ename IN array_ename
, p_job IN array_job
, p_sal IN array_sal
, p_deptno IN array_deptno
, p_hiredate IN array_hiredate ) IS
BEGIN
FORALL i IN p_empno.first..p_empno.last
INSERT INTO emp2
VALUES ( p_empno (i)
, p_ename (i)
, p_job (i)
, p_sal (i)
, p_deptno (i)
, p_hiredate(i) );
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END insert_t;
BEGIN
OPEN c;
LOOP
--여기로 array로 담는듯
FETCH c BULK COLLECT
INTO l_empno, l_ename, l_job, l_sal, l_deptno, l_hiredate
LIMIT l_fetch_size;
insert_t( l_empno, l_ename, l_job, l_sal, l_deptno, l_hiredate );
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;4. 트레이스 결과
SELECT 쿼리
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO, HIRDATE FROM EMP;================================================================================
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 11 0.02 0.03 1 82 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.03 0.03 1 83 0 10000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
10000 TABLE ACCESS FULL EMP (cr=82 pr=1 pw=0 time=30 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.00 0.00
********************************************************************************
================================================================================INSERT 쿼리
================================================================================
INSERT INTO EMP2 VALUES ( :B1 , :B2 , :B3 , :B4 , :B5 , :B6 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.04 0.07 0 151 976 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.04 0.07 0 151 976 10000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net break/reset to client 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 12.20 12.20
================================================================================- 10,000건을 처리하는데 select문의 Fetch Call과 insert문의 Execute Call이 각각 10번씩만 발생했다.
- select의 Fetch Call이 11번이 발생한 것은 데이터가 더 있는지 확인하기 위한 것이다.
- EXP, IMP 명령을 통해 데이터를 Export, Import 할 때도 내부적으로 Array Proccessing이 활용된다.
- buffer 옵션으로 지정가능하며 byte 단위로 지정한다.
- = rows_in_array * maximum_row_size
- buffer 옵션으로 지정가능하며 byte 단위로 지정한다.
- Array Processing을 지원하는 인터페이스가 프로그램 언어별로 각기 다르다.
도광양회(韜光養晦) ‘빛을 감추고 어둠속에서 힘을 기른다’