7.2 DBMS_TRACE: Providing a PLSQL Trace Facility | Oracle PL/SQL Programming: Guide to Oracle8i Features


Team-Fly

	Oracle PL/SQL Programming Guide to Oracle 8 i Features By Steven Feuerstein
	Table of Contents

	Chapter 7. New and Enhanced Built-in Packages in Oracle8i

7.2 DBMS_TRACE: Providing a PL/SQL Trace Facility

Earlier versions of Oracle offered some PL/SQL trace capabilities, but Oracle8 i provides an API that allows you to more easily specify and control the tracing of the execution of PL/SQL procedures, functions, and exceptions. DBMS_TRACE provides programs to start and stop PL/SQL tracing in a session. When tracing is turned on, the engine collects data as the program executes. The data is then written out to the Oracle Server trace file.

The PL/SQL trace facility provides you with a trace file that shows you the specific steps executed by your code. The PL/SQL profiler (described earlier in this chapter) offers a much more comprehensive analysis of your application, including timing information and counts of the number of times a specific line was executed.

7.2.1 Installing DBMS_TRACE

This package may not have been installed automatically with the rest of the built-in packages. To determine whether DBMS_TRACE is present, connect to SYS and execute this command:

 BEGIN DBMS_TRACE.CLEAR_PLSQL_TRACE; END; /

If you see this error:

 PLS-00201: identifier 'DBMS_TRACE.CLEAR_PLSQL_TRACE'             must be declared

then you must install the package. To do this, remain connected as SYS and run the following files in the order specified:

\Oracle\Ora81\Rdbms\Admin\dbmspbt.sql

\Oracle\Ora81\Rdbms\Admin\prvtpbt.plb

The directory shown here is the default for a Windows NT installation. Your Oracle 8.1 home directory may be different, but these files will always be found in the Rdbms\Admin subdirectory under the Oracle 8.1 home directory.

7.2.2 DBMS_TRACE Programs

The programs in the DBMS_TRACE package are listed in Table 7.5.

Table 7.5. DBMS_TRACE Programs

Program	Description
SET_PLSQL_TRACE procedure	Starts PL/SQL tracing in the current session
CLEAR_PLSQL_TRACE procedure	Stops the dumping of trace data for that session
PLSQL_TRACE_VERSION procedure	Gets the major and minor version numbers of the DBMS_TRACE package

To trace execution of your PL/SQL code, you must first start the trace with a call to:

 DBMS_TRACE.SET_PLSQL_TRACE (  trace_level  INTEGER);

in your current session, where trace_level is one of the following values:

 DBMS_TRACE.trace_all_calls          CONSTANT INTEGER := 1;  DBMS_TRACE.trace_enabled_calls      CONSTANT INTEGER := 2;  DBMS_TRACE.trace_all_exceptions     CONSTANT INTEGER := 4;  DBMS_TRACE.trace_enabled_exceptions CONSTANT INTEGER := 8;

To turn on tracing from all programs executed in your session, issue this call:

 DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_calls);

To turn on tracing for all exceptions raised during the session, issue this call:

 DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_exceptions);

You then run your code; when you are done, you stop the trace session by calling:

 DBMS_TRACE.CLEAR_PLSQL_TRACE;

You can then examine the contents of the trace file. The names of these files are generated by Oracle; you will mostly need to pay attention to the modification date of the files to figure out which file to examine. The location of the trace files is discussed later in Section 7.2.4. You cannot use PL/SQL tracing with the multithreaded server (MTS).

7.2.3 Controlling Trace File Contents

The trace files produced by DBMS_TRACE can get really big. You can minimize the trace output and focus it by obtaining trace information only for specific programs that you have enabled for trace data collection.

You cannot use this approach with remote procedure calls.

To enable a specific program for tracing, you can alter the session to enable any programs that are created or replaced in the session. To take this approach, issue this command:

 ALTER SESSION SET PLSQL_DEBUG=TRUE;

If you don't want to alter your entire session, you can recompile a specific program unit in debug mode as follows (not applicable to anonymous blocks):

 ALTER [PROCEDURE  FUNCTION  PACKAGE BODY]  program_name  COMPILE DEBUG;

After you have enabled the programs in which you're interested, the following call will initiate tracing just for those program units:

 DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_calls);

You can also restrict the trace information to only those exceptions raised within enabled programs with this call:

 DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_exceptions);

If you request tracing for all programs or exceptions and also request tracing only for enabled programs or exceptions, the request for "all" takes precedence.

7.2.4 Format of Collected Data

If you request tracing only for enabled program units and the current program unit is not enabled, then no trace data is written. If the current program unit is enabled for tracing, then call tracing writes out the program unit type, name , and stack depth. If the current program unit is not enabled, then call tracing writes out the program unit type, line number, and stack depth.

Exception tracing writes out the line number. Raising an exception records trace information on whether the exception is user defined or predefined, and records the exception number in the case of predefined exceptions. If you raise a user -defined exception, you will always see an error code of 1.

In Oracle8 i under Windows NT, the trace files are written to the following directory (by default):

Oracle\Admin\Oracle81\udump

Here is an example of the output from a trace of the procedure showemps:

 *** 1999.06.14.09.59.25.394 *** SESSION ID:(9.7) 1999.06.14.09.59.25.344 ------------ PL/SQL TRACE INFORMATION ----------- Levels set :  1    Trace:  ANONYMOUS BLOCK: Stack depth = 1 Trace:   PROCEDURE SCOTT.SHOWEMPS: Call to entry at line 5 Stack depth = 2 Trace:    PACKAGE BODY SYS.DBMS_SQL: Call to entry at line 1 Stack depth = 3 Trace:     PACKAGE BODY SYS.DBMS_SYS_SQL: Call to entry at line 1 Stack depth = 4 Trace:     PACKAGE BODY SYS.DBMS_SYS_SQL: ICD vector index = 21 Stack depth = 4 Trace:    PACKAGE PLVPRO.P: Call to entry at line 26 Stack depth = 3 Trace:    PACKAGE PLVPRO.P: ICD vector index = 6 Stack depth = 3 Trace:    PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3 Trace:    PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3 Trace:     PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 4


Team-Fly

Top