Java, Oracle, Bind Variables and Batch Update – Part II
In my last post, I have setup in Java different insert methods to a database. In this post, I will post what I found in the Oracle trace files / tkprof output and the different response times:
- Multiple Inserts using java.sql.Statement
- Multiple Inserts using java.sql.PreparedStatement, using Bind Variables
- Multiple Inserts using java.sql.Statement, with addBatch() & executeBatch()
- Multiple Inserts using java.sql.PreparedStatement, using Bind Variables with addBatch() & executeBatch()
For the impatient readers, the response times when I execute my Java code with each insert method is as follows:
| #1 | #2 | #3 | #4 |
| 8.012s | 9.083s | 9.093s | 0.15s |
Please do not place too much judgments on these timings, they may be affected by the network latency, CPU spikes at the time of testing. However, this study is intended to show the relative amount of work and time spent for each INSERT method.
#1. Multiple Inserts using java.sql.Statement
Oracle trace file / tkprof output:
INSERT INTO insert_test (str) values (:"SYS_B_0") call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1000 0.09 0.08 0 0 0 0 Execute 1000 0.31 0.23 2 1006 3065 1000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2000 0.40 0.32 2 1006 3065 1000 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 23 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 2 0.00 0.00 SQL*Net message to client 1002 0.00 0.00 SQL*Net message from client 1002 0.04 8.17 ********************************************************************************
* The parse count is 1000, and execute count is also 1000 – a 1:1 ratio.
* Some CPU resources were used to parse the statement, and consumed 9ms of CPU time.
* Times waited is 1002 – (my guess) – 1000 for executeUpdate(); 1 for stmt.close(); 1 for commit?
* Oracle waited 8.17s for Java client to issue 1002 calls, one by one.
#2. Multiple Inserts using java.sql.PreparedStatement, using Bind Variables
Oracle trace file / tkprof output:
INSERT INTO insert_test (str) values (:1) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1000 0.16 0.18 3 7 1065 1000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1001 0.16 0.18 3 7 1065 1000 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 23 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 3 0.00 0.00 SQL*Net message to client 1002 0.00 0.00 SQL*Net message from client 1002 0.06 8.52 ********************************************************************************
* Parse to Execute is a 1:1000 ratio. Almost negligible CPU resource and time spent on parsing.
* Oracle waited 8.52s for Java client to issue 1002 calls, one by one.
#3. Multiple Inserts using java.sql.Statement, with addBatch() & executeBatch()
Oracle trace file / tkprof output:
INSERT INTO insert_test (str) values (:"SYS_B_0") call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1000 0.09 0.08 0 0 0 0 Execute 1000 0.31 0.23 2 1006 3065 1000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2000 0.40 0.32 2 1006 3065 1000 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 23 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 2 0.00 0.00 SQL*Net message to client 1002 0.00 0.00 SQL*Net message from client 1002 0.04 8.17
* The statistics are similar to Method #1: Multiple Inserts using java.sql.Statement.
* This is because Oracle9i does not support batch updating with java.sql.Statement:
The Oracle implementation of standard update batching does not implement true batching for generic statements and callable statements. Although Oracle JDBC supports the use of standard batching syntax for Statement and CallableStatement objects, you will see performance improvement for only PreparedStatement objects. [source]
#4. Multiple Inserts using java.sql.PreparedStatement, using Bind Variables with addBatch() & executeBatch()
Oracle trace file / tkprof output:
INSERT INTO insert_test (str) values (:1) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.02 0.04 0 27 89 1000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.02 0.04 0 27 89 1000 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 23 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net more data from client 12 0.01 0.03 SQL*Net message to client 3 0.00 0.00 SQL*Net message from client 3 0.00 0.02
* The response time is 0.16secs.
* A (soft) parse of the SQL statement is made, with 1 execute to insert 1000 rows.
* 1 execute saves more time than the 1000 executes in previous methods.
* Communication between the Java client and Oracle is reduced to a minimum, as shown by 3 SQL*Net message from client.
* The 1000 bind values are sent in bulk using SQL*Net more data from client.
Frederick Tang Weblog 
Leave a comment