4. Array Processing 활용

조회 수 7914 추천 수 0 2013.09.11 10:24:00
휘휘 *.168.123.1
  • Array Processing 기능
    • 한번의 SQL 수행으로 다량의 로우를 동시에 INSERT/UPDATE/DELETE 할수있음
    • 네트워크를 통한 데이터베이스 CALL 을 감소시켜주고 SQL 수행시간과 CPU사용량을 줄여줌

* 실험 table자료 생성

SQL> DROP TABLE mtly PURGE;

Table dropped.

SQL> CREATE TABLE mtly
 2  AS
3 SELECT TO_CHAR(OBJECT_ID) custno
  4       , '200903' yyyymm
 5       , round(dbms_random.value(1000, 10000), -2) giro
 6    , round(dbms_random.value(1000, 10000), -2) at
 7       , round(dbms_random.value(1000, 10000), -2) card
 8       , round(dbms_random.value(1000, 10000), -2) cell
 9       , round(dbms_random.value(1000, 10000), -2) internet
10  FROM   ALL_OBJECTS ,(select level from dual connect by level<=6)
11 WHERE ROWNUM <= 30000;

Table created.

SQL> DROP TABLE bypay PURGE;

Table dropped.

SQL> CREATE TABLE bypay (
 2 custno      NUMBER
3 , yyyymm        VARCHAR2(6)
4 , paycode  VARCHAR2(1)
5 , payamt      NUMBER
6 ) ;

Table created.





첫번째



JavaArrayProcessing.java



import java.io.*;

import java.lang.*;

import java.util.*;

import java.net.*;

import java.sql.*;

import oracle.jdbc.driver.*;


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 custno, yyyymm"

                    + "     , giro, at, card, cell, internet "

                    + "FROM   mtly "

                    + "WHERE  yyyymm = ?";

                   

    String SQLStmt2 = "INSERT /*+ test3 */ INTO bypay  " 

            + "(custno, yyyymm, paycode, payamt) "

            + "VALUES(?, ?, ?, ?)";


    con.setAutoCommit(false);


    PreparedStatement stmt1 = con.prepareStatement(SQLStmt1);

    PreparedStatement stmt2 = con.prepareStatement(SQLStmt2);

    stmt1.setString(1, input_month);


    ResultSet rs = stmt1.executeQuery();

    while(rs.next()){

      String custno = rs.getString(1);

      String yyyymm = rs.getString(2);

      long giro = rs.getLong(3);

      long at = rs.getLong(4);

      long card = rs.getLong(5);

      long cell = rs.getLong(6);

      long internet = rs.getLong(7);

      if(giro > 0)     insertData (con, stmt2, custno, yyyymm, "A", giro);

      if(at > 0) insertData (con, stmt2, custno, yyyymm, "B", at);

      if(card > 0) insertData (con, stmt2, custno, yyyymm, "C", card);

      if(cell > 0)   insertData (con, stmt2, custno, yyyymm, "D", cell);

      if(internet > 0)   insertData (con, stmt2, custno, yyyymm, "E", internet);

      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);

  }


  public static Connection getConnection() throws Exception{

    String DB_CON     = "jdbc:oracle:thin:@localhost:1521:ora10g";

    String DB_USER    = "scott";

    String DB_PASS    = "tiger";


    DriverManager.registerDriver(new OracleDriver());

    Connection con = DriverManager.getConnection(DB_CON,DB_USER,DB_PASS);


    return con;

  }


  public static void releaseConnection(Connection con) throws Exception{

    con.commit();

    con.close();

  }

}



oracle@durinuri:~$ javac JavaArrayProcessing.java
oracle@durinuri:~$ java JavaArrayProcessing
elapsed time : 14199


oracle@durinuri:~/app/oracle/admin/XE/udump$ tkprof xe_ora_12027.trc javatrc.prt sys=no

TKPROF: Release 10.2.0.1.0 - Production on 일 7월 4 23:19:06 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.




TKPROF: Release 10.2.0.1.0 - Production on 일 7월 4 23:21:54 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: xe_ora_12027.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SELECT custno, yyyymm     , giro, at, card, cell, internet
FROM
  mtly WHERE  yyyymm = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.02       0.02          0          2          0           0
Fetch     3001      0.40       0.64         24       3135          0       30000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3003      0.43       0.67         24       3137          0       30000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41  

Rows     Row Source Operation
-------  ---------------------------------------------------
 30000  TABLE ACCESS FULL MTLY (cr=3135 pr=24 pw=0 time=330331 us)

********************************************************************************

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
 NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
 NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)
FROM
(SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("MTLY") FULL("MTLY")
 NO_PARALLEL_INDEX("MTLY") */ 1 AS C1, CASE WHEN "MTLY"."YYYYMM"=:B1 THEN 1
 ELSE 0 END AS C2 FROM "MTLY" SAMPLE BLOCK (43.150685 , 1) SEED (1) "MTLY")
 SAMPLESUB


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.02          0          0          0           0
Fetch        1      0.03       0.13        110         66          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.03       0.17        110         66          0           1

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)

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  SORT AGGREGATE (cr=66 pr=110 pw=0 time=133039 us)
 13986   TABLE ACCESS SAMPLE MTLY (cr=66 pr=110 pw=0 time=88660 us)

********************************************************************************

INSERT /*+ test3 */ INTO bypay  (custno, yyyymm, paycode, payamt)
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.95       4.46          0        656       4974      150000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       31      0.95       4.46          0        656       4974      150000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41  



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute     31      0.97       4.48          0        658       4974      150000
Fetch     3001      0.40       0.64         24       3135          0       30000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3034      1.38       5.14         24       3793       4974      180000

Misses in library cache during parse: 2
Misses in library cache during execute: 2


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       70      0.02       0.03          0          0          0           0
Execute     79      0.11       0.16          0        162         36          37
Fetch       66      0.05       0.16        110        187          0          32
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      215      0.18       0.36        110        349         36          69

Misses in library cache during parse: 5
Misses in library cache during execute: 5

   3  user  SQL statements in session.
  78  internal SQL statements in session.
  81  SQL statements in session.
********************************************************************************
Trace file: xe_ora_12027.trc
Trace file compatibility: 10.01.00
Sort options: default

      1  session in tracefile.
      3  user  SQL statements in trace file.
     78  internal SQL statements in trace file.
     81  SQL statements in trace file.
     21  unique SQL statements in trace file.
   3791  lines in trace file.
     11  elapsed seconds in trace file.





두번째


stmt1.setFetchsize(1000); 명령추가 JavaArrayProcessing.java


oracle@durinuri:~$ javac JavaArrayProcessing.java


oracle@durinuri:~$ java JavaArrayProcessing
elapsed time : 23944




TKPROF: Release 10.2.0.1.0 - Production on Sun Jul 4 23:31:49 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: xe_ora_12475.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SELECT custno, yyyymm     , giro, at, card, cell, internet
FROM
  mtly WHERE  yyyymm = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      1      0.00       0.04          0          0          0           0
Fetch       31      0.15       1.30          0        167          0       30000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.15       1.38          0        167          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 MTLY (cr=167 pr=0 pw=0 time=197968 us)

********************************************************************************

INSERT /*+ test3 */ INTO bypay  (custno, yyyymm, paycode, payamt)
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.95      10.35          0        568       4428      150000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       31      0.95      10.35          0        568       4428      150000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 41  



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.03          0          0          0           0
Execute     31      0.95      10.40          0        568       4428      150000
Fetch       31      0.15       1.30          0        167          0       30000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       64      1.10      11.74          0        735       4428      180000

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       18      0.00       0.05          0          0          0           0
Execute     20      0.02       0.77          0         32          8           9
Fetch       17      0.00       0.00          0         35          0           9
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       55      0.02       0.82          0         67          8          18

Misses in library cache during parse: 0

   2  user  SQL statements in session.
  20  internal SQL statements in session.
  22  SQL statements in session.
********************************************************************************
Trace file: xe_ora_12475.trc
Trace file compatibility: 10.01.00
Sort options: default

      1  session in tracefile.
      2  user  SQL statements in trace file.
     20  internal SQL statements in trace file.
     22  SQL statements in trace file.
      9  unique SQL statements in trace file.
    280  lines in trace file.
     15  elapsed seconds in trace file.




  • Insert 문에 대한 execute call 이 30회 발생
  • Insert 된 로우수는 총 150,000건이므로 매번 5,000건식 Array Processing
    • 커서에서 Fetch 되는 각 로우마다 5번씩 insert 수행, 1000로우마다 한번씩 executeBatch를 수행
  • Select 결과
    • 첫번째의 경우 java의 기본단위인 10개 단위로 수행하여 3001번
    • 두번째의 경우 setFecthSize (1000)을 추가하여 1000개단위로 수행하여 31번 수행
  • 교제의 실험결과

PL/SQL
(Recursive)
JAVAJAVA
(Array 처리)
One-SQL
Parse Call5150,00011
Execute Call150,000150,000301
총소요시간7.26126,82초1.21초0.9초




  • Array Processing 의 효과를 극대화 하려면 모든과정이 Array단위로 진행되어야 함




참조) pl-sql 의 BULK COLLECT 을 사용하여 ARRAY PROCESSING 처리 할수도 있음
http://oracletoday.blogspot.com/2005/11/bulk-collect_15.html
http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html