oracle Performance decrease when number of connections increase

[and]

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',' 303030303030313 535313736',9,7,-1,'11111111111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 111111111111111 111111111111111 111111111111111 1111
111111111111111 11111','03','04 10')

[Daniel Morgan]

Re: oracle Performance decrease when number of connections increase

and wrote:

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 ?

First off ... please do not cross-post to multiple usenet groups. Thank
you. This posting belongs in c.d.o.server and the other threads should
be ignored.

Now to your question: One statement one question.

Statement: Add the APPEND hint.

Question: Are you performing incremental commits?

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[ctcgag@hotmail.com]

Re: oracle Performance decrease when number of connections increase

"and" <ac@egeneral.co m.hkwrote:

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.

I'm tempted to ask if you are committing every insert individually, but in
neither case does it account for the difference, that I can see.

Did you run each several times, to make sure you aren't seeing some
fluke cause by external events?

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

You need to trace them in a way that shows you the wait events.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service New Rate! $9.95/Month 50GB