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:
Next we create a dummy procedure to trace: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 run our procedure three times with different tracing levels: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; /
With the tracing complete we can identify the available RUNIDs using the following query: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; /
We can then use the appropriate RUNID in the following query to look at the trace: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
The content of the trace record depends on the trace level being used. The available options are: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;
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: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;
or:ALTER SESSION SET PLSQL_DEBUG=TRUE; CREATE OR REPLACE [PROCEDURE | FUNCTION | PACKAGE BODY] ...
For further information see:ALTER [PROCEDURE | FUNCTION | PACKAGE] COMPILE DEBUG [BODY];
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/756652/viewspace-474963/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/756652/viewspace-474963/