Tracing PL/SQL using DBMS_TRACE – Oracle Database 11G release 2 (11.2)

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

dbms_trace_1

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

2 thoughts on “Tracing PL/SQL using DBMS_TRACE – Oracle Database 11G release 2 (11.2)

Leave a Reply

Your email address will not be published. Required fields are marked *