There’s a nice Metalink Note 438452.1 about various less known Oracle performance tuning utilities.
If you haven’t heard about things like StackX, LTOM, HangFG, SQLTXPLAIN, OS_Watcher or OPDG then it’s time to check this note out! :)
Advanced Oracle Troubleshooting Guide, Part 4: Diagnosing a long parsing issue
June 3, 2008 By 2 Comments
There was a recent thread in Oracle Forums about a session getting stuck somewhere when a specific SQL was issued. The SQL executed did not return at all unless ORDERED hint was used. Even the EXPLAIN PLAN command (which only parses the statement, doesn’t execute it) did never return.
Classic tracing + tkprof techniques didn’t show much (just some recursive queries consuming insignificant amounts of time).
The proven V$SESSION_WAIT sampling technique didn’t reveal anything as it showed the session being constantly on CPU (the wait state = ‘WAITED KNOWN TIME’ which means session is on CPU) and SEQ# didn’t increase (which means that wait state did not change over time).
Due the symptoms described above I was well prepared to troubleshoot this issue. This looks exactly like one of the troubleshooting use cases I demonstrate in of my Advanced Oracle Troubleshooting class (nice embedded advertisment, huh? ;)
In such a case where tracing and V$ views don’t provide any useful information about what the session is doing, I normally look into few stack traces of the server process. In this case I asked the poster to do this and here is the result:
Snapper shortcut
June 2, 2008 By 2 Comments
I have a (very) small script called sn.sql which I use as a wrapper around snapper (maybe I should’ve called it Snapper Wrapper but it’s too long name for the purpose :)
The idea is to have to type less when running Snapper with default options (take 1 snapshot, output to screen and display Session tats,Wait events and Time model stats).
Whenever there’s a performance issue with a session I first quickly run @sn <seconds> <sid>, for example:
SQL> @sn 10 106
This takes a 10 second snapshot of session 106 and displays output:
-- Session Snapper v1.07 by Tanel Poder ( http://www.tanelpoder.com )
---------------------------------------------------------------------------------------------------------------------------------------------
HEAD, SID, SNAPSHOT START , SECONDS, TYPE, STATISTIC , DELTA, DELTA/SEC, HDELTA, HDELTA/SEC
---------------------------------------------------------------------------------------------------------------------------------------------
DATA, 106, 20080603 06:31:17, 10, TIME, hard parse elapsed time , 10124018, 1012402, 10.12s, 1.01s
DATA, 106, 20080603 06:31:17, 10, TIME, parse time elapsed , 10124018, 1012402, 10.12s, 1.01s
DATA, 106, 20080603 06:31:17, 10, TIME, DB CPU , 10113462, 1011346, 10.11s, 1.01s
DATA, 106, 20080603 06:31:17, 10, TIME, DB time , 10124018, 1012402, 10.12s, 1.01s
DATA, 106, 20080603 06:31:17, 10, WAIT, events in waitclass Other , 51, 5, 51us, 5.1us
-- End of snap 1
PL/SQL procedure successfully completed.
This might help you to save few seconds of typing every time you troubleshoot :)
