Java Database Connectivity (JDBC)

Hello,

I implemented the same update of a table in both PL/SQL and Java (PreparedStatement and batch update) and ran it on a local database. It didn't surprise me that PL/SQL was faster, but why is it that much faster (factor 4.5). If I look at the trace I see back that difference that seems to result from different execution plans for the otherwise identical update statement. Another thing that disturbes me is that batch updates are just a factor of 1.5 faster than non-batch updates. Also, Java is slightly faster on a non-IOT table. Below you'll find the TKPROF output both for the PL/SQL and Java versions.

Oracle 9.2, Java 1.4.2, JDBC from Oracle

Any help appreciated,
Stefan


PL/SQL:



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

UPDATE tsd_ip set value0_fl=:b4, state_l=:b3
where timeseries_l=:b2 and tstamp_ts=:b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 646000 50.82 96.79 1844 1938497 672745 646000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 646001 50.82 96.79 1844 1938497 672745 646000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25 (TSMDB) (recursive depth: 1)

Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'TSD_IP'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_TSD_IP' (UNIQUE)

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


Java:

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

update tsd_ip set value0_fl = :1, state_l = :2
where
timeseries_l = :3 and tstamp_ts = :4


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6460 0.47 0.41 0 0 0 0
Execute 6460 350.45 400.29 1835 2506726 658711 646000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12920 350.92 400.70 1835 2506726 658711 646000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25 (TSMDB)

Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE (cr=402 r=0 w=0 time=56804 us)
100 INDEX RANGE SCAN PK_TSD_IP (cr=402 r=0 w=0 time=52821 us)(object id 17891)


Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'TSD_IP'
100 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_TSD_IP' (UNIQUE)

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