Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Are you aware of Statspack, AWR reports or similar? tkprof? –  Phil Oct 30 '13 at 0:35
    
If some indexes or tables are getting flushed out of cache under load, you'll get (perhaps drastic) differences in performance from one execution to the other. And I/O spikes. See if you can run your tests with a larger cache. If that "fixes" the perf. inconsistencies, then either you need to live with a larger cache, or tune your queries and schema. –  Mat Oct 30 '13 at 5:22
    
I am not much aware of statspack or AWR reports. How do I know whether indexes or tables flushed out from my cache? I have more than 100 Gigs of data sapred across different tablespaces in a SAN disk. –  user1614862 Oct 30 '13 at 18:59
    
Does anybody know how to run "Who is Active v11.11"? –  user1614862 Oct 30 '13 at 19:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.