I have a transaction called "updatePolicyStatus" whose response time is usually under a 3 seconds for many instances during a peak load. This transaction processes bunch of business rules and fires big set of SQLs to database. But during a peak load, 10% of the total transactions response time is going beyond a 60 seconds. When I look at individual queries they are fine. I am not quiet sure what else is causing the transaction to become slow once in a while? My database I/O, network, CPU and memory everything is fine tuned. Could you please let me know what else could be the bottleneck? My database is Oracle 11g and it has 100 Gigs of data.

  • What do you mean by "When I look at individual queries they are fine"? What, specifically, are you looking at? Are you looking at the AWR/ ASH information for the executions of those statements during the period when the transaction time exceeds a minute? Or are you looking at the statements in isolation later? I'm not sure what you mean when you say that I/O, CPU, memory, and network are "fine tuned". Are you saying that some tool tells you that when things have plenty of additional capacity when performance is poor? What does AWR/ ASH tell you are the wait events? – Justin Cave Nov 12 '13 at 19:45
  • When I say "When I look at individual queries they are fine" means that, when my transaction is executed for 100 times during an hour of load test, I get response time below 3 seconds for 90 times and only for the remaining 10 times the response time for the same transaction goes beyond 60 seconds.When I say my resources (I/O, CPU, memory...etc) are fine tuned means that, they are not heavily overloaded by requests (I monitor it manually, CPU is always below 15% (8 CORE CPU), memory is below 50% (24 Gigs of RAM....etc). I am not using AWR/ASH, I use appDynamics and as well as visualVM. – user1614862 Nov 12 '13 at 20:26
  • Are you saying that you cannot use AWR/ASH data (possibly because of license issues)? Or that you don't want to? Or that you can but you're not sure how? I'd certainly want to zoom in on the data Oracle collects about individual executions to see if you're occasionally getting different query plans or whether there is some wait event that is happening periodically like row lock contention. – Justin Cave Nov 12 '13 at 20:34
  • I cannot setup AWR/ASH on my database, because it may effect my database's overall performance, isn't it. My DBA has enabled performance stats for this DB and collects metrics after every test. When you say AWR/ASH, do you mean Oracle Enterprise manager? I suspect the issue can be waits which may be occuring, because many of my queries are making full table scans during the execution. Is there any easy way to find out the these kind of waits (user i/o waits, lock contention...etc) other than AWR? – user1614862 Nov 12 '13 at 21:10
  • If you're licensed for it, the overhead of AWR statistics gathering is generally pretty minimal. If you've disabled AWR snapshots and ASH because of performance concerns, you're throwing away a lot of data that would help debug this issue. Enterprise Manager does have screens that display data from the AWR and ASH tables but you can also query the data directly. The easy way to see wait events, unfortunately, is to look at the AWR data. You could also trace the session (that will make the session much slower and can have a noticable impact on database performance). – Justin Cave Nov 12 '13 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.