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? –  Phil 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

2 Answers 2

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

Yes, statspack is an easy free way to go. First thing to look at is the top 5 timed events. Unfortunately statspack won't tell you the problem for specific sql statements. To see where and how particular sql statements are spending their time, you need ASH. A graphical interface to ASH along with the collection of ASH (ie no perf pack license needed) is available with WASH. http://www.oraclerealworld.com/web-ash-w-ash/ I'd recommend lab128 which has a 2 day free trial http://www.lab128.com/lab128_download.html Lab128 can collect ASH itself or optionally use the ASH collection from the performance pack. One you have on of those running, see where you are spending time - CPU, I/O, Locks or other stuff. In both the above tools you can see where individual SQL spend time to help you distinguish a problem in one SQL vs loosing that SQLs problems in the overall stats of the database.

share|improve this answer

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.