hi everyone,
i am using oracle 9.2.0 and i have written a simple jdbc java program
to insert a record within a for loop to a table using jdbc thin
driver(refer to the bottom of this email for the sql statement). Using
the same program, when i insert 20000 records from 1 process, time is
about 100 seconds. However, when i insert 1000 records from 20
process, time is about 1000 seconds, which is 10 times.
Since i am developing a OLTP system which needs to insert lots of
transaction within one seconds from more than 30 ejb and each records
about 2k size, sytem is heavily depends on the database access. Do
anyone know how to speed it up ?
i have also attached the result of sprepsql script of statspack in the
followings for reference(to ease everyone viewing the log, i only cut
main content of the log, note that the elapse time for the case 1 is 1
seconds but case 2 is 39.1 seconds, don't know why ???)
thanks you very much
and
1. result of sprepsql.sql using 1 process inserting 20000 records
================================================== ===============
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 120,593 6.0 70.48
Disk Reads: 6,686 0.3 99.23
Rows processed: 20,000 1.0
CPU Time(s/ms): 9 .4
Elapsed Time(s/ms): 30 1.5
Sorts: 0 .0
Parse Calls: 20,000 1.0
Invalidations: 0
Version count: 1
Sharable Mem(K): 13
Executions: 20,000
2. result of sprepsql.sql using 20 process inserting 1000 records
================================================== ===============
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 123,350 6.2 71.89
Disk Reads: 6,801 0.3 99.49
Rows processed: 20,000 1.0
CPU Time(s/ms): 9 .5
Elapsed Time(s/ms): 781 39.1
Sorts: 0 .0
Parse Calls: 20,000 1.0
Invalidations: 0
Version count: 1
Sharable Mem(K): 13
Executions: 20,000
3. sql statement:
================================================== =================
insert into table1(intime, process_name, processkey, st
an, msgtype, status, commid, msg, party_code, mti) values (sysda
te, '03','036219','303030303030313535313736',9,7,-1,'11111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111111111111111111111111111111111 11111111111111
11111111111111111111','03','0410')