1

I have an application that makes tens of thousands of calls per day to a stored procedure. Normally this executes in about 50 ms. However, periodically, it takes 2-3 seconds to execute. I have an application log file that shows what time and how long each call to the stored procedure took. I'm wondering...how can I track down this exact transaction within Oracle Enterprise Manager and display the associated wait events so that I can understand why this specific call is taking so long to execute.

1 Answer 1

1

The ASH report shows top wait events, top SQL and top sessions. Using Enterprise Manager, you can generate the ASH report by navigating to the performance page and then clicking 'Run ASH Report'. On thhe performance page, you can also click the 'Top Activity' link. On the Top Activity page, you can then choose a time interval by dragging the shaded area(may have different drag controller version to version and browser to browser), and then click Run ASH Report.

For details:
Identifying High-Load SQL Statements

3
  • The problem is that ASH only generates aggregate information. This would only be helpful if the statement in question always had a performance problem. However, it only had a performance problem the one time it was executed. Top Activity only shows the top consuming processes/connections. This statement was not likely one, it just performed poorly.
    – GregH
    Feb 18, 2016 at 17:29
  • You may not be able to get this level of detail out of Enterprise Manager. In this case, it may be better to query v$active_session_history directly, specifying a SAMPLE_TIME when the procedure was running slowly. Check the waits and see if the plan hash is different when it runs slow.
    – andba77
    Feb 18, 2016 at 19:41
  • @GregH if so if you must use Enterprise Manager then generate ADDM or AWR reports and analyze it. Or you can trace that SQL using 'SQL Tracing'.
    – atokpas
    Feb 18, 2016 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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