첫번째

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.