473,327 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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','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
2 7975
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

38
by: Mike | last post by:
No flame wars, please! We're planning a move from a non-relational system to a relational system. Our choices have been narrowed to Oracle and DB2. Since we're moving from non-relational to...
0
by: Santa | last post by:
I am using Fritz Onion's "Asynchronous Pages" approach as mentioned in the article http://msdn.microsoft.com/msdnmag/issues/03/06/Threading/default.aspx to increase the performance of my ASPX...
22
by: Bob Darlington | last post by:
It has been suggested to me (by a potential client) that my app (which he is considering buying) should be web enabled to improve performance, particularly regarding screen refreshes. My initial...
9
by: AnandaSim | last post by:
Hi All, I've had Access 97, 2000 connections to the corporate Oracle database for a few years now - but seldom use it. When I did use it years ago, performance was not fast but the features were...
6
by: Mike | last post by:
Lets just say my app is done HOO HOO. Now, I'm accessing the database via a web service and one thing i noticed that my app is running real slow. When I first started working on the app is ran...
2
by: egoldthwait | last post by:
I need to convert a 17mb access 2000 db to Oracle and house it in a Citrix farm. The issue: we have never converted an Access Db to Oracle but can probably use Oracle's Workbench to assist with...
4
by: Jim Devenish | last post by:
I have converted an Access back-end to SQL Server back-end but am having some problems. The Access to Access application has been running well for some years. I have successfully copied all the...
8
by: NAdir | last post by:
Hi, thank you for your help. My VB.Net application contains a document that the user can refresh at any time. The refresh works fine and needs to loop through few datatables (hundreds of rows)....
2
by: and | last post by:
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.