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

https://dbaora.com/tracing-plsql-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 

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