Database Reference
In-Depth Information
Enabling SQL Trace with SET_EV
Let's assume that the session of user HR is a resource hog. So we need to trace SQL statements
and wait events to figure out what's going on. The following example shows how to retrieve the
SID
and
SERIAL#
from
V$SESSION
to enable tracing with
DBMS_SYSTEM.SET_EV
:
SQL> CONNECT / AS SYSDBA
Connected.
SQL> SELECT sid, serial# FROM v$session WHERE username='HR';
SID SERIAL#
---------- ----------
140 862
SQL> EXECUTE dbms_system.set_ev(140, 862, 10046, 8, '')
PL/SQL procedure successfully completed.
Taking Named Dumps
Named dumps may only be taken in the same session that calls
DBMS_SYSTEM.SET_EV
. To take an
immediate
ERRORSTACK
dump in a foreign session, use
ORADEBUG
(see Chapter 37). The following
anonymous block takes an
ERRORSTACK
dump at level 3:
SQL> VARIABLE sid NUMBER
SQL> VARIABLE serial NUMBER
SQL> BEGIN
SELECT sid, serial# INTO :sid, :serial
FROM v$session
WHERE sid=(SELECT sid FROM v$mystat WHERE rownum=1);
sys.dbms_system.set_ev(:sid, :serial, 65535, 3, 'errorstack');
END;
/
The same result is attained by a much simpler
ALTER
SESSION
statement.
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK LEVEL 3';
An error stack dump shows which subroutine a program was executing at the time the
dump was taken. It also lists the entire call stack, i.e., in what sequence subroutines called each
other. The currently executed subroutine is at the top of the call stack. ORACLE call stacks are
similar to call stacks obtained by reading core files with debuggers, such as
adb
,
sdb
, or
gdb
.
Taking an
ERRORSTACK
dump does not terminate an ORACLE process. However, you should
expect the process to become unresponsive while it writes the trace file.
A level 3 error stack dump includes open cursors and is thus useful to find the SQL state-
ment text corresponding to a certain cursor number, in case the
PARSING
IN
CURSOR
entry for
a specific cursor is missing in a SQL trace file. In Oracle10
g
, cursors are dumped as in the
example below:
Cursor#1(07470C24) state=BOUND curiob=07476564
curflg=4c fl2=0 par=00000000 ses=6998B274
sqltxt(66F31AA0)=ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK LEVEL 3'
