DBMS_TRACE包來追蹤PLSQL運行
?
?
??? 最近對調優比較感興趣，接著來學習一下DBMS_TRACE包的用法。要注意的是這個包是為了用來TRACE PLSQL的，只能針對PLSQL使用。
?
??? 這個包總得來說算是比較簡單，主要是為了記錄PLSQL的一些運行狀況，懶得自己試驗了，轉一篇文章看看就可以了：
?
?
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 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;
?
??? 注意以上這一步是必須的，因為9i和10g默認都是沒有建立該表的。
?
?
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 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];
?
?
?
?
?
??? 轉一個自帶的說明，主要是看一下trace_level和event編號：
--------------------------------------------------------------------------
?create or replace package sys.dbms_trace is
? ------------
? --? OVERVIEW
? --
? --? This package provides routines to start and stop PL/SQL tracing
? --
?
? -------------
? --? CONSTANTS
? --
?
? -- Define constants to control which PL/SQL features are traced. For each
? -- feature, there are two constants:
? --??? one to trace all occurences of the feature
? --??? one to trace only those occurences in modules compiled debug
? -- To trace multiple features, simply add the constants.
? --
? trace_all_calls????????? constant integer := 1;? -- Trace calls/returns
? trace_enabled_calls????? constant integer := 2;
?
? trace_all_exceptions???? constant integer := 4;? -- trace exceptions
? trace_enabled_exceptions constant integer := 8;? -- (and handlers)
?
? trace_all_sql??????????? constant integer := 32; -- trace SQL statements
? trace_enabled_sql??????? constant integer := 64; -- at PL/SQL level (does
?????????????????????????????????????????????????? -- not invoke SQL trace)
?
? trace_all_lines????????? constant integer := 128; -- trace each line
? trace_enabled_lines????? constant integer := 256;
?
? -- There are also some constants to allow control of the trace package
? --
? trace_stop?????????????? constant integer := 16384;
?
? -- Pause/resume allow tracing to be paused and later resumed.
? --
? trace_pause????????????? constant integer := 4096;
? trace_resume???????????? constant integer := 8192;
?
? -- Save only the last few records. This allows tracing up to a problem
? -- area, without filling the database up with masses of irrelevant crud.
? -- If event 10940 is set, the limit is 1023*(the value of event 10940).
? -- This can be overridden by the routine limit_plsql_trace
? --
? trace_limit????????????? constant integer := 16;
?
? --
? -- version history:
? --?? 1.0 - creation
? --
? trace_major_version constant binary_integer := 1;
? trace_minor_version constant binary_integer := 0;
?
? -- CONSTANTS
? --
? -- The following constants are used in the "event_kind" column, to identify
? -- the various records in the database. All references to them should use
? -- the symbolic names
? --
? plsql_trace_start??????? constant integer := 38; -- Start tracing
? plsql_trace_stop???????? constant integer := 39; -- Finish tracing
? plsql_trace_set_flags??? constant integer := 40; -- Change trace options
? plsql_trace_pause??????? constant integer := 41; -- Tracing paused
? plsql_trace_resume?????? constant integer := 42; -- Tracing resumed
? plsql_trace_enter_vm???? constant integer := 43; -- New PL/SQL VM entered?????????????????????????????????????????? /* Entering the VM */
? plsql_trace_exit_vm????? constant integer := 44; -- PL/SQL VM? exited*
? plsql_trace_begin_call?? constant integer := 45; -- Calling normal routine
? plsql_trace_elab_spec??? constant integer := 46; -- Calling package spec???????????????????????????????????? /* Calling package spec*/
? plsql_trace_elab_body??? constant integer := 47; -- Calling package body
? plsql_trace_icd????????? constant integer := 48; -- Call to internal PL/SQL routine
? plsql_trace_rpc????????? constant integer := 49; -- Remote procedure call
? plsql_trace_end_call???? constant integer := 50; -- Returning from a call
? plsql_trace_new_line???? constant integer := 51; -- Line number changed
? plsql_trace_excp_raised? constant integer := 52; -- Exception raised
? plsql_trace_excp_handled constant integer := 53; -- Exception handler
? plsql_trace_sql????????? constant integer := 54; -- SQL statement
? plsql_trace_bind???????? constant integer := 55; -- Bind parameters
? plsql_trace_user???????? constant integer := 56; -- User requested record
? plsql_trace_nodebug????? constant integer := 57; -- Some events skipped
?????????????????????????????????????????????????? -- because module compiled
?????????????????????????????????????????????????????????????????????????????????????????????????? -- NODEBUG
?
? ----------------------------
? --? PROCEDURES AND FUNCTIONS
? --
?
? -- start trace data dumping in session
? -- the parameter is the sum of the above constants representing which
? -- events to trace
? procedure set_plsql_trace(trace_level in binary_integer);
?
? -- Return the run-number
? function get_plsql_trace_runnumber return binary_integer;
?
? -- stop trace data dumping in session
? procedure clear_plsql_trace;
?
? -- pause trace data dumping in session
? procedure pause_plsql_trace;
?
? -- pause trace data dumping in session
? procedure resume_plsql_trace;
?
? -- limit amount of trace data dumped
? -- the parameter is the approximate number of records to keep.
? -- (the most recent records are retained)
? procedure limit_plsql_trace(limit in binary_integer := 8192);
?
? -- Add user comment to trace table
? procedure comment_plsql_trace(comment in varchar2);
?
? -- This function verifies that this version of the dbms_trace package
? -- can work with the implementation in the database.
? --
? function internal_version_check return binary_integer;
?
? -- get version number of trace package
? procedure plsql_trace_version(major out binary_integer,
??????????????????????????????? minor out binary_integer);
?
end dbms_trace;
?
-----------------------------------------------------------------------------------------
?
?
??? 小小的補充：根據不同的trace級別，可以針對PLSQL中的每一個SQL或者僅僅是調用包級別，或者是精確到PLSQL的每一行。還是比較有用的，具體的使用方法等到以后慢慢測試吧。
?
?