RSS Feed

Method R Discussion Group

The last thing you want to see when you run the Hotsos Profiler is "[no hv] <no text for statement>" as your most time-consuming SQL statement. This means that the Profiler has found an expensive database call, but it doesn't have a PARSING IN CURSOR section in its input data that allows the Profiler to know which SQL statement is associated with the call.

There are several reasons this can occur. One is that you've initiated or concluded an Oracle trace in the midst of some complicated stack of recursive SQL statements.

A more common way is when you're using the Hotsos Profiler extension for Oracle SQL Developer. You'll see the problem if you trace the execution of a PL/SQL block two or more times consecutively within a single Oracle session. The Oracle kernel puts the PARSING IN CURSOR information out for the first database call that a given session executes on a given cursor, but not for subsequent calls upon that cursor.

The problem is, our Profiler extension uses a ALTER SESSION SET TRACEFILE_IDENTIFIER command to restrict the scope of what's being profiled to exactly the part of your Oracle session represented by your single script execution. So the Oracle kernel *has* emitted the PARSING IN CURSOR information for your cursors; it's just in another file now, because the extension has opened a new trace file with the ALTER SESSION command.

There are a couple of workarounds. First, you can see all your SQL text in the /first/ profile you've created for a given session. You can refer back to that profile report by using the View > Profiler History panel. Second, you can ensure that you'll see all your SQL text by disconnecting, reconnecting, and running your block again. The new session will emit your statement text information into the trace file.

I have seen the issue of missing SQL statement text due to no PARSING IN CURSOR in a number of cases without ever using ALTER SESSION SET TRACEFILE_IDENTIFIER. I don't know how high the probability is, but it appears to happen quite often, when tracing is enabled in the middle of an active session.

To counter this, I added ORADEBUG DUMP ERRORSTACK 3 to my script that enables extended SQL trace. The resulting trace file contains the cursors of the session. Cursor numbers in the trace match those in WAIT, PARSE, EXEC, FETCH entries (numbers in V$OPEN_CURSORS do not match).

Thus, there's a good chance (or even 100%) that the level 2 errorstack dump contains the missing SQL statement text (in 10g at least, would need to check 9i or 11g to be certain how these releases behave). Details are in my upcoming book "Secret ORACLE".

Norbert