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 executing a web service for 100 times. The average response time of this service is 1.5 seconds. But when I look at the response time of each execution individually, there are a few executions (at least 10) which are taking beyond 15 seconds to respond. The data for each request is exactly the same as any previous request (just appending a dynamic number to name...etc).

It certainly looks like a database issue, because my database is responding differently each time we execute a same query again and again. I am not able to understand why my database is behaving like this. My database is oracle 11g. I have experienced this issue with different UI flows as well previously.

Edited to add collected metrics.

enter image description here

share|improve this question
    
Are you licensed to use the performance & tuning packs? Do you have AWR/ Statspack/ ASH data/ reports available to analyze? If 10% of the calls are taking much longer than the average, can you trace the session and see what the wait events are? Is the web service running a simple SELECT statement? Or is it modifying data? If it is modifying data, might the problem be one of locking (i.e. multiple calls all trying to update the same row building up in a queue)? –  Justin Cave Jan 29 at 22:56
    
I don't have the license to use AWR/statspack/ASH data. My web service modifies the data as well. I don't think this is going to be a locking issue. I am executing the web service with single virtual user who is invoking the web service for 100 times one after another. This is not a concurrent invocation. –  user1614862 Jan 29 at 23:04
    
Statspack doesn't require a license. It does need to be installed in your database. Can you trace the session to see what the wait events are? –  Justin Cave Jan 29 at 23:06
    
Is your web service logging granular enough to be certain that it's the database? Are you logging immediately before and after the query execution so you know the exact time period? I say this, as there could be networking timeouts/factors involved. Is the web service running on the same box as the database? –  FreshPhilOfSO Jan 29 at 23:08
    
Database is running in a dedicated high end machine. Webservice is running in a separate machine. I am logging immediately.There is no network issue as well I am collecting avg. TTFB (Time taken for first bit) metrics also. There is no difference between avg. TTFB and avg. response time. –  user1614862 Jan 29 at 23:19
show 6 more comments

1 Answer

From your explanation, I don't believe you are correct that the data is exactly the same. It is more likely that the data is derived in the same manner, but is different. It is likely that in many cases many of the data blocks needed for the query are in memory, but in others they need to read from disk.

It does concern me that you average response time is 1.5 seconds. This is pretty slow for an online system.

Things to do to track this down (I am assuming a Unix/Linux server):

  • Monitor the disk activity on the partitions containing your database files. I use sar. Look for periodic changes in any value.
  • Check the total memory consumption. If processes or shared memory start paging, performance will degrade rapidly. I use sar for this as well.
  • Ensure your SGA is sufficiently large. The default size is very conservative, and usually should be increased.

Oracle has some very good documentation available online. Take the time to review and understand it.

share|improve this answer
    
I do believe that it is something to do with the disk performance, I do see that there are lot of paging and context switches happening in the database machine. My database is hosted on WINDOWS 2008 server. What could we do to increase the performance of these bottlenecks? –  user1614862 Feb 11 at 20:35
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.