50
DBMS_TRACE

Oracle8i PL/SQL provides an API for tracing the execution of PL/SQL programs on the server. You can use the Trace API, implemented on the server as the DBMS_TRACE package, to trace PL/SQL functions, procedures, and exceptions.

DBMS_TRACE provides subprograms to start and stop PL/SQL tracing in a session. The trace data gets collected as the program executes, and it is written out to the Oracle Server trace file.

A typical session involves:

• Starting PL/SQL tracing in session (DBMS_TRACE.SET_PLSQL_TRACE).

• Running application which is to be traced.

• Stopping PL/SQL tracing in session (DBMS_TRACE.CLEAR_PLSQL_TRACE).

Requirements

This package must be created under SYS.

Restrictions

You cannot use PL/SQL tracing with the multi-threaded server (MTS).

Constants

trace_all_calls          constant INTEGER := 1; 
trace_enabled_calls      constant INTEGER := 2; 
trace_all_exceptions     constant INTEGER := 4; 
trace_enabled_exceptions constant INTEGER := 8; 
/* 
 * The version of the trace package. These constants will change as the 
 * package evolves. Use the PLSQL_TRACE_VERSION procedure (described below) 
 * to get the current version of the package. 
 */ 
trace_major_version      constant BINARY_INTEGER := 1; 
trace_minor_version      constant BINARY_INTEGER := 0; 

Using DBMS_TRACE

Controlling Data Volume

Profiling large applications may produce a huge volume of data. You can control the volume of data collected by enabling specific program units for trace data collection.

You can enable a program unit by compiling it debug. This can be done in one of two ways:

alter session set plsql_debug=true; 
create or replace ... /* create the library units - debug information will be 
generated */ 

or:

/* recompile specific library unit with debug option */ 
alter [PROCEDURE | FUNCTION | PACKAGE BODY]  <libunit-name> compile debug; 

Collecting Trace Data

Tracing Calls

Two levels of call tracing are available:

• Level 1: Trace all calls. This corresponds to the constant trace_all_calls.

• Level 2: Trace calls to enabled program units only. This corresponds to the constant trace_enabled_calls.

Enabling cannot be detected for remote procedure calls (RPCs); hence, RPCs are only traced with level 1.

Tracing Exceptions

Two levels of exception tracing are available:

• Level 1: Trace all exceptions. This corresponds to trace_all_exceptions.

• Level 2: Trace exceptions raised in enabled program units only. This corresponds to trace_enabled_exceptions.

  Note: For both call and exception tracing, level 1 overrides level 2. For example, if both level 1 and level 2 are enabled, then level 1 takes precedence.

Collected Data

If tracing is requested only for enabled program units, and if the current program unit is not enabled, then no trace data is written.

If the current program unit is enabled, 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 the stack depth.

Exception tracing writes out the line number. Raising the exception shows information on whether the exception is user-defined or pre-defined and the exception number, in the case of pre-defined exceptions.

Summary of Subprograms

Table 50-1 DBMS_TRACE Package Subprograms

SET_PLSQL_TRACE procedure

CLEAR_PLSQL_TRACE procedure

PLSQL_TRACE_VERSION 
procedure

SET_PLSQL_TRACE procedure

This procedure enables PL/SQL trace data collection.

Syntax

DBMS_TRACE.SET_PLSQL_TRACE ( 
   trace_level INTEGER); 

Parameters

Table 50-2 SET_PLSQL_TRACE Procedure Parameters

trace_level

CLEAR_PLSQL_TRACE procedure

This procedure disables trace data collection.

Syntax

DBMS_TRACE.CLEAR_PLSQL_TRACE; 

Parameters

None.

PLSQL_TRACE_VERSION procedure

This procedure gets the version number of the trace package. It returns the major and minor version number of the DBMS_TRACE package.

Syntax

DBMS_TRACE.PLSQL_TRACE_VERSION ( 
   major OUT BINARY_INTEGER, 
   minor OUT BINARY_INTEGER); 

Parameters

Table 50-3 PLSQL_TRACE_VERSION Procedure Parameters

major

minor