I am performing a load test for my application. All my SQL queries which are fired from my application, individually look better and perform fast.
The same SQL are becoming bottleneck when they are part of a load where response time of this queries varies from 1 second to 100 seconds. There is no high usage of the CPU or memory in the database machine and there is no network latency as well.
I am wondering what else is causing this queries to perform slow under load conditions. During the load itself, sometimes I get the response time below a second, the same query gets response time of 100 seconds in another executions.
Are there any locks or waits happening during a synchronous access of the tables? Most of these queries are SELECT
queries only which should not be the cause for locks or waits.
The application environment is Oracle 11g, JBoss 5.1. The elapsed times of the queries are calculated at JDBC level.
I/O looks quiet better, though there are few high physical read ratios. If the I/O is a bottleneck then it should happen for all the time, right? I get slow response times once in a while. If there are locks/waits happening, is there a way to find them out during the test? may be with the help of a perf stats or tool?