ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DBMS_TRACE(zt)

DBMS_TRACE(zt)

原创 Linux操作系统 作者:chinagjj2008 时间:2009-04-14 09:13:45 0 删除 编辑

DBMS_TRACE

TheDBMS_TRACEpackage 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 theDBMS_PROFILERpackage 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 availableRUNIDs 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 appropriateRUNIDin 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 theDBMS_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:

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/464838/viewspace-588918/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle坏块(二)
下一篇: DBMS_PROFILER(zt)
请登录后发表评论 登录
全部评论

注册时间:2008-01-14

  • 博文量
    32
  • 访问量
    60022