DBMS_TRACE

The DBMS_TRACE package provides an API to allow the actions of PL/SQL programs to be traced. The scope and volume of the tracing is user configurable. This package can be used in conjunction with the DBMS_PROFILER package to identify performance bottlenecks.

The first step is to install the tables which will hold the trace data.

CONNECT sys/password@service AS SYSDBA
@$ORACLE_HOME/rdbms/admin/tracetab.sql

CREATE PUBLIC SYNONYM plsql_trace_runs FOR plsql_trace_runs;
CREATE PUBLIC SYNONYM plsql_trace_events FOR plsql_trace_events;
CREATE PUBLIC SYNONYM plsql_trace_runnumber FOR plsql_trace_runnumber;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_runs TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_events TO PUBLIC;
GRANT SELECT ON plsql_trace_runnumber TO PUBLIC;

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.

ALTER SESSION SET PLSQL_DEBUG=TRUE;
CREATE OR REPLACE [PROCEDURE | FUNCTION | PACKAGE BODY] ...

Or.

ALTER [PROCEDURE | FUNCTION | PACKAGE]  <libunit-name> COMPILE DEBUG [BODY];

For more information see:

Hope this helps. Regards Tim...

Back to the Top.