Contents:
The PL/SQL Trace Facility
Tracing for Production Support
Free Format Filtering
Structured Interface Filtering
Quick-and-Dirty Tracing
it is very important to be able to trace the activity in your PL/SQL code base.
offered a SQL trace facility which provides extensive data on the processing of your SQL statements. See Oracle Performance Tuning for more information on this feature, as well as other tuning/tracing utilities like TKPROF.
In addition to these standard Oracle facilities, you can build your own tracing utilities; the final section in this chapter offers an architecture and some implementational ideas for a utility designed specifically to trace execution within a running application. (Such a utility is particularly useful for production support.)
to the Oracle Server trace file. On Windows NT, you can find this trace file in the \OraNT\RDBMS80\TRACE directory. In UNIX, check the $ORACLE_HOME\rdbms\trace directory. The name of the file has the format ORANNNNN.TRC, where NNNNN is a left zero-padded number assigned internally by the Oracle Trace facility. Order your directory by date to find the latest trace file.
NOTE: You cannot use the PL/SQL tracing tool with the multi-threaded server option (MTS).
In order to trace the execution of a program, you will first have to enable that program by recompiling it with the debug option. You can do this either by altering your session and then issuing a CREATE OR REPLACE statement, or by directly recompiling an existing program unit with the debug option.
To alter your session to turn on PL/SQL debug mode for compilation, issue this command:
SQL> ALTER SESSION SET PLSQL_DEBUG=TRUE;
Then compile your program unit with a CREATE OR REPLACE statement. That program unit will then be available for tracing.
You can also recompile your existing, stored program with debug mode as follows:
SQL> ALTER [PROCEDURE|FUNCTION|PACKAGE] <program_name> COMPILE DEBUG;
So if you wanted to enable the emp_pkg package for tracing, you would issue this command:
SQL> ALTER PACKAGE emp_pkg COMPILE DEBUG;
From within a PL/SQL program you can also turn on debug mode for a module by using DBMS_SQL as follows:
CREATE OR REPLACE PROCEDURE debugpkg (name IN VARCHAR2)
IS
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk INTEGER;
BEGIN
DBMS_SQL.PARSE (cur,
'ALTER PACKAGE ' || name || ' COMPILE DEBUG',
DBMS_SQL.NATIVE);
fdbk := DBMS_SQL.EXECUTE (cur);
DBMS_SQL.CLOSE_CURSOR (cur);
END;
/For more information on DBMS_SQL, see Appendix C, Built-In Packages.
for your session. You can request tracing of program calls and/or exceptions raised in programs.
You do this with the ALTER SESSION command as follows:
SQL> ALTER SESSION SET EVENTS='10938 TRACE NAME CONTEXT LEVEL <number>';
where 10938 is the event number for PL/SQL tracing and <number> is a number indicating the level of tracing you desire. Valid tracing levels are:
| Level | Description |
|---|---|
| 1 | Trace all calls |
| 2 | Trace calls to enabled programs only |
| 4 | Trace all exceptions |
| 8 | Trace exceptions in enabled program units only |
You can activate multiple event levels for tracing by adding the level values. For example, the following statement sets tracing for levels 2 and 8:
SQL> ALTER SESSION SET EVENTS='10938 TRACE NAME CONTEXT LEVEL 10';
while this next command activates tracing for levels 2, 4, and 8:
SQL> ALTER SESSION SET EVENTS='10938 TRACE NAME CONTEXT LEVEL 14';
program units.
tracing has been enabled with a debug-mode compile.
NOTE: You cannot turn on tracing for remote procedure calls (RPCs) -- that is, programs which are stored in remote databases.
To make it easier for me to test and use this facility I created the following scripts:
alter package &1 compile debug; alter session set events='10938 trace name context level &1';
So I can now prepare a package for tracing with the following statement:
SQL> @compdbg PKGNAME
of an exception. The following code:
SQL> @trace 14 SQL> BEGIN 2 p.l (1); 3 raise no_data_found; 4 END; 5 / begin * ERROR at line 1: SQL>
resulted in this trace file:
Dump file D:\ORANT\RDBMS80\trace\ORA00089.TRC Wed Jun 11 13:22:52 1997 ORACLE V8.0.2.0.2 - Beta vsnsta=1 vsnsql=c vsnxtr=3 Windows NT V4.0, OS V5.101, CPU type 586 Oracle8 Server Release 8.0.2.0.2 - Beta With the distributed, heterogeneous, replication, objects and parallel query options PL/SQL Release 3.0.2.0.2 - Beta Windows NT V4.0, OS V5.101, CPU type 586 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 11 pid: 59 Wed Jun 11 13:22:52 1997 *** SESSION ID:(11.18) 1997.06.11.13.22.52.431 ------------ PL/SQL TRACE INFORMATION ----------- Levels set : 2 4 8 ------------ PL/SQL TRACE INFORMATION ----------- Levels set : 2 4 8 Trace: PACKAGE PLVPRO.P: P Stack depth = 2 Trace: PACKAGE BODY PLVPRO.P: P Stack depth = 2 Trace: PACKAGE BODY PLVPRO.P: L Stack depth = 2 Trace: PACKAGE BODY PLVPRO.P: DISPLAY_LINE Stack depth = 3 Trace: PACKAGE BODY PLVPRO.P: LINELEN Stack depth = 4 Trace: PACKAGE BODY PLVPRO.P: PUT_LINE Stack depth = 4 Trace: Pre-defined exception - OER 1403 at line 0 of ANONYMOUS BLOCK:
As you can see, trace files can get big fast, but they contain some extremely useful information.
| Главная |