Fw how to use DBMS_TRACE

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] COMPILE DEBUG [BODY];
For further information see:
DBMS_TRACE
DBMS_PROFILER
Hope this helps. Regards Tim...