By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

oracle Performance decrease when number of connections increase

P: n/a
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','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')
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Jul 19 '05 #2

P: n/a
"and" <ac@egeneral.com.hk> wrote:
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
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.