DBMS_TRACE

Debugging PLSQL code - DBMS_TRACE basically it displays flow of program. used for debugging only do not display any statistics etc, DBMS_TRACE 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.

This package can be used in conjunction with the DBMS_PROFILER package to identify performance bottlenecks.
CONNECT sys/password@service AS SYSDBA
@$ORACLE_HOME/rdbms/admin/tracetab.sql
This script create 2 tables and a sequence:
1. plsql_trace_runs
2. plsql_trace_events
3. plsql_trace_runnumber

alter procedure compile debug;

Next we create a dummy procedure to trace.

CREATE OR REPLACE PROCEDURE do_something (p_times  IN  NUMBER) AS
  l_dummy  NUMBER;
BEGIN
  FOR i IN 1 .. p_times LOOP
    SELECT l_dummy + 1
    INTO   l_dummy
    FROM   dual;
  END LOOP;
END;
/
Next we run our procedure three times with different tracing levels.

DECLARE
  l_result  BINARY_INTEGER;
BEGIN
  DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);
  do_something(p_times => 100);
  DBMS_TRACE.clear_plsql_trace;

  DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_sql);
  do_something(p_times => 100);
  DBMS_TRACE.clear_plsql_trace;

  DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_lines);
  do_something(p_times => 100);
  DBMS_TRACE.clear_plsql_trace;
END;
/

With the tracing complete we can identify the available RUNIDs using the following query.

SELECT r.runid,
       TO_CHAR(r.run_date, 'DD-MON-YYYY HH24:MI:SS') AS run_date,
       r.run_owner
FROM   plsql_trace_runs r
ORDER BY r.runid;

     RUNID RUN_DATE             RUN_OWNER
---------- -------------------- -------------------------------
         1 22-AUG-2003 08:27:18 TIM_HALL
         2 22-AUG-2003 08:27:18 TIM_HALL
         3 22-AUG-2003 08:27:18 TIM_HALL

We can then use the appropriate RUNID in the following query to look at the trace.

SET LINESIZE 200
SET TRIMOUT ON

COLUMN runid FORMAT 99999
COLUMN event_seq FORMAT 99999
COLUMN event_unit_owner FORMAT A20
COLUMN event_unit FORMAT A20
COLUMN event_unit_kind FORMAT A20
COLUMN event_comment FORMAT A30

SELECT e.runid,
       e.event_seq,
       TO_CHAR(e.event_time, 'DD-MON-YYYY HH24:MI:SS') AS event_time,
       e.event_unit_owner,
       e.event_unit,
       e.event_unit_kind,
       e.proc_line,
       e.event_comment
FROM   plsql_trace_events e
WHERE  e.runid = 1
ORDER BY e.runid, e.event_seq;

The content of the trace record depends on the trace level being used. The available options are:

trace_all_calls          constant INTEGER := 1;
trace_enabled_calls      constant INTEGER := 2;
trace_all_exceptions     constant INTEGER := 4;
trace_enabled_exceptions constant INTEGER := 8;
trace_all_sql            constant INTEGER := 32;
trace_enabled_sql        constant INTEGER := 64;
trace_all_lines          constant INTEGER := 128;
trace_enabled_lines      constant INTEGER := 256;
trace_stop               constant INTEGER := 16384;
trace_pause              constant INTEGER := 4096;
trace_resume             constant INTEGER := 8192;
trace_limit              constant INTEGER := 16;

Trace can be limited to specified programs by starting the trace with the DBMS_TRACE.%_enabled_% options. A program can have trace enabled using one of the following methods.