Hi all.I am new to java ranch.
We have a situtation where we are firiing same SP thru diffrent threads so that performance of SP may be may be increased. However, we noticed that the SP is taking more time to execute when we fire it paralley.
For eg: 10 threads are taking as much time as 2 threads. Please help us.
For eg: 10 threads are taking as much time as 2 threads. Please help us.
You are basically asking us for help with a performance issue, yet you have provided no performance data whatsoever about the running system.
Step 1... I would recommend looking at the load on the App Server and DB Server. If one of them is overloaded, then I would recommend focusing on that side of the system.
Step 2... For the DB and app server, there is no black magic here. You need profiling data. For example, if it is the App server that is under load, you need to profile it to see what is causing the load.
We did some basic profiling and found out that the load on DB server is increasing as we increase the number of threads. If we take a 2 CPU box (DB server). It seams that even simple querries are taking propional increase in timings with the number of threads. I can't reproduce the exact data but here is what is a sample of what we found out for a chunk code of code which is executed in loop:
Threads: 1 4 10 20
Time(sec): 2 3 5 6
Hope you can provide some help. We scanned the code for single row updates and things like that but there were none
Hope you can provide some help. We scanned the code for single row updates and things like that ...
I don't know what to tell you, as I already stated that there isn't a black magic solution here. For scaling issues, the only solution is to place the application under high load, and use a profiler to find the bottleneck... Searching for "things like that" is silly because it is the bottleneck that is preventing scaling.
Henry
Alan Mehio
Ranch Hand
Joined: Apr 04, 2005
Posts: 70
posted
0
Threads: 1 4 10 20
Time(sec): 2 3 5 6
as far as I understand, you are executing an SP from java. Now the question start from the java side: how do you obtain the database connection to execute the SP. do you use the same connection or each thread create its own db connection? Then how do you configure your java application to get the connection? is it via the container throught a JNDI look up for a data source? or through the manager or through connection pooling based on a data source configure from the application?
Regards,
Alan Mehio
London, UK
Apurv Adarsh
Greenhorn
Joined: Feb 07, 2009
Posts: 12
posted
0
Each thread is creating its own connection thru a connection pool whic is common to whole application. Oracle AWR shows that max per querry wait time is taken by a querry which is burried deep inside our rule engine.However this querry is optimized perfetly(cost = 16). we got a slight performancw bost when we switced our DB from 8 core cpu to 16 core cpu. This was expected but even then there was a bottelneck present.
Even our DBA was unable to explain this
0
Please Help
