How to track down wait events for a specific transaction in Oracle Enterprise Manager

oracleoracle-enterprise-manager

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.

Best Answer

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

Related Solutions

Logging/profiling an unpredictably slow stored procedure

You can set tracing at the ADMIN level. Your timing is suspiciously similar to an issue I dealt with in April/May this year. The admin level trace file showed a 5 second gap when reading from a socket. (10:44:05 -> 10:44:11) NTTRD is the read from tcp socket call. I'd definitely work this issue with Oracle support, but if you see gaps like the one below then the pause is somewhere in your network stack or hardware (i.e outside Oracle). In our case Oracle support pointed us towards network switch logs etc, but the culprit turned out to be an issue in the Microsoft Windows TCP stack down to the new Receive Window Auto-Tuning feature (see http://support.microsoft.com/kb/983528 for example if on Server 2008). Obviously it would be pure fluke if your issue was the same as ours, but if you can see the gaps in the admin level trace file it's a fair bet that it will turn out to be a network issue.

[08-APR-2011 10:44:05:938] nsprecv: reading from transport...
[08-APR-2011 10:44:05:938] nttrd: entry
[08-APR-2011 10:44:11:095] nttrd: socket 2156 had bytes read=60
[08-APR-2011 10:44:11:095] nttrd: exit
[08-APR-2011 10:44:11:095] nsprecv: 60 bytes from transport
[08-APR-2011 10:44:11:095] nsprecv: tlen=60, plen=60, type=6

Oracle Enterprise Manager 12c – How to Add a New Target

Well this task i am afraid it can not be described here, i mean it could but is to much to write and i will not re-invent the wheel.:)
So i recommend you follow this article How to add a new target on OEM , i wrote this article with this purpose(help guys like you ! kkkkk ).
i might get a down vote for my short answer ! but for those intrested -- fell free to read the article !! And thare a lot's of them on Oracle Enterprise Manager 12 C .

Best Answer

Related Solutions

Logging/profiling an unpredictably slow stored procedure

Oracle Enterprise Manager 12c – How to Add a New Target

Related Question