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的每一行。还是比较有用的，具体的使用方法等到以后慢慢测试吧。