[Chapter 26] Tracing PL/SQL ExecutionChapter 26 26. Tracing PL/SQL ExecutionContents:The PL/SQL Trace FacilityTracing for Production SupportFree Format FilteringStructured Interface FilteringQuick-and-Dirty TracingAs you build more and more complex applications, it can be very difficult to keep track of which procedure calls which function; execution call stacks grow deep and bewildering. Yet there are times when it is very important to be able to trace the activity in your PL/SQL code base. Oracle offers a trace facility for your PL/SQL code which allows you to generate voluminous information about the particular paths your programs take to get their job done. Of course, Oracle has for years 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.) 26.1 The PL/SQL Trace FacilityPL/SQL8 offers a tracing tool for server-side PL/SQL. You can use this tool to trace the execution of PL/SQL programs and the raising of exceptions within those programs. The output from the trace is written 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). 26.1.1 Enabling Program Units for TracingIn 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.26.1.2 Turning On the TraceOnce you have enabled the desired program units for tracing, you can execute those programs (or the application code which makes use of those programs). To get trace output, however, you must turn on tracing 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';Lower trace levels supersede higher levels. So if you activate tracing for level combination 12, level 8 will be ignored and trace output will be produced for all exceptions, not just exceptions in enabled program units. As you can see, you have some control over the granularity of tracing. It is not possible, however, to activate tracing just within a specific program. It is either all programs or all programs in which 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. 26.1.3 A Sample Tracing SessionTo 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 PKGNAMEwhere PKGNAME is the name of my package. In the following session, I turn on tracing for all levels by passing 14 (2 + 4 + 8); then I call my PL/Vision substitute for DBMS_OUTPUT.PUT followed by the raising 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. 25.5 Overview of PL/SQL8 Enhancements26.2 Tracing for Production Support Copyright (c) 2000 O'Reilly & Associates. All rights reserved.