It’s no new feature in 11g but it’s worth to describe it. Package DBMS_TRACE enables to trace execution of PL/SQL code in database.Oracle collects the trace data as the program executes and writes it to database tables.
Configuration
You must create database tables into which the DBMS_TRACE package writes output. Otherwise, the data is not collected. To create these tables, run the script tracetab.sql from $ORACLE_HOME/rdbms/admin directory. The tables this script creates are owned by user SYS.
@?/rdbms/admin/tracetab.sql
Following objects should be created in schema SYS
select owner, object_name, object_type from dba_objects where object_name like 'PLSQL%' order by 2, 1; OWNER OBJECT_NAME OBJECT_TYPE ------ ---------------------- ------------- SYS PLSQL_TRACE_RUNNUMBER SEQUENCE SYS PLSQL_TRACE_EVENTS TABLE SYS PLSQL_TRACE_RUNS TABLE
Privileges
Grant execute on package DBMS_TRACE to a user and grant select on PLSQL* tables to review results later by the user.
In my case I just granted select on PLSQL* tables to PUBLIC
grant select on PLSQL_TRACE_EVENTS to public; grant select on PLSQL_TRACE_RUNS to public;
grant execute on DBMS_TRACE to <USER>
grant execute on DBMS_TRACE to TOMASZ;
Steps for tracing
1. Enable DEBUG option for specific subprograms (optional step)
Trace information can be collected for all called subprograms or only for subprograms with turned option DEBUG. It enables to decreases number of entries in table PLSQL_TRACE_EVENTS for later analyzes. If you are planing to trace all subprograms you can skip this step.
There are two methods to turn on DEBUG option for a subprogram.
- first option – enable debug option on session level and recompile objects in database
alter session set PLSQL_DEBUG=true;
create or replace ...
- second option – recompile specific subprogram with debug option
alter [ procedure | function | package ] subprogram-name compile debug [ body ];
2. Set tracing level
Procedure DBMS_TRACE.SET_PLSQL_TRACE defines which events and what will be collected in trace tables. It should be started in session which will be traced.
- specify tracing level
execute dbms_trace.set_plsql_trace ( tracel_calls + trace_sql + trace_exceptions + trace_lines )
There are four categories with two levels that can be specified as tracing level for subprograms. Each category is optional an can be defined only one level per category.
The settings defines what is written to table PLSQL_TRACE_EVENTS.
Trace calls
- TRACE_ALL_CALLS – all calls are collected
- TRACE_ENABLED_CALLS – only calls in subprograms with DEBUG option are collected
Trace sql
- TRACE_ALL_SQL – all executed SQLs are collected
- TRACE_ENABLED_SQL – only SQLs in subprograms with DEBUG option are collected
Trace exceptions
- TRACE_ALL_EXCEPTIONS – all exceptions are collected
- TRACE_ENABLED_EXCEPTIONS – only exceptions in subprograms with DEBUG option are collected
Trace lines
- TRACE_ALL_LINES – information about all lines are collected
- TRACE_ENABLED_LINES – only information about all lines in subprograms with DEBUG option are collected
Some examples
begin
dbms_trace.set_plsql_trace
( dbms_trace.TRACE_ALL_EXCEPTIONS +
dbms_trace.TRACE_ENABLED_LINES +
dbms_trace.TRACE_ENABLED_SQL );
end;
/
begin dbms_trace.set_plsql_trace(dbms_trace.TRACE_ALL_CALLS); end; /
3. Stop tracing
Following call stops tracing and writes output to trace tables
begin dbms_trace.clear_plsql_trace; end; /
Example
Let’s create some procedures for tests
create or replace procedure prc_1
is
begin
dbms_output.put_line('prc_1');
end;
/
create or replace procedure prc_2
is
begin
dbms_output.put_line('prc_2');
end;
/
create or replace procedure prc_3
is
begin
dbms_output.put_line('prc_3');
end;
/
create or replace procedure prc_all
is
begin
prc_1;
prc_2;
prc_3;
end;
/
Then turn on DEBUG option for 2 of them
alter procedure prc_1 compile debug; alter procedure prc_3 compile debug;
Start tests
begin dbms_trace.set_plsql_trace(dbms_trace.TRACE_ENABLED_CALLS); prc_all; dbms_trace.clear_plsql_trace; end; /
Review data
select runid, run_owner from sys.plsql_trace_runs; RUNID RUN_OWNER ---------- ------------------------------- 1 TOMASZ
Details are in SYS.PLSQL_TRACE_EVENTS. Only calls for DUBUG are collected.
select event_seq, event_unit, event_unit_kind, event_comment from sys.plsql_trace_events where runid=1 EVENT_SEQ EVENT_UNIT EVENT_UNIT_KIND EVENT_COMMENT --------- ------------- ---------------- ---------------------------- 1 PL/SQL Trace Tool started 2 Trace flags changed 3 Some NODEBUG events skipped 4 PRC_ALL PROCEDURE Procedure Call 5 PRC_1 PROCEDURE Procedure Call 6 DBMS_OUTPUT PACKAGE BODY Return from procedure call 7 PRC_1 PROCEDURE Return from procedure call 8 Some NODEBUG events skipped 9 PRC_ALL PROCEDURE Procedure Call 10 PRC_3 PROCEDURE Procedure Call 11 DBMS_OUTPUT PACKAGE BODY Return from procedure call 12 PRC_3 PROCEDURE Return from procedure call 13 Some NODEBUG events skipped 14 PL/SQL trace stopped 14 rows selected
Additional settings
There are more constants that can be used
- TRACE_PAUSE – pause tracing
- TRACE_RESUME – resume tracing
- TRACE_STOP – stop tracing
Above options enables to dynamically pause/resume or stop tracing inside called subprograms.
set serveroutput on begin dbms_trace.set_plsql_trace(dbms_trace.TRACE_ALL_CALLS); prc_1; dbms_trace.set_plsql_trace(dbms_trace.TRACE_PAUSE); prc_2; dbms_trace.set_plsql_trace(dbms_trace.TRACE_RESUME); prc_3; dbms_trace.set_plsql_trace(dbms_trace.TRACE_STOP); end; /
- TRACE_LIMIT. – It sets limit to keep only 8,192 trace events. When tracing stops, the last 8,192 records are saved. The 8,192 record limit can be changed. Setting event 10940 to level n changes the record limit to 1024 * n.
- NO_TRACE_ADMINISTRATIVE
-prevents the generation of administrative event records as- PL/SQL Trace Tool started
- Trace flags changed
- PL/SQL Virtual Machine started
- PL/SQL Virtual Machine stopped
Have a fun 🙂
Tomasz
very nice explanation..
Perfectly explained
Thanks for the wonderful explanations!!
Very nice and Good Information.
Helpfull
Very Good Information.