Dbms_Trace

DBMS_TRACE helps to start and stop plsql tracing in a session.

Steps involved are:

1.      Start tracing using dbms_trace.set_plsql_trace procedure

2.      Execute application code to be traced

3.      Stop plsql tracing using dbms_trace.clear_plsql_trace procedure

Before doing this, one need to do the initial setup of creating the database tables, using which we can analyze the trace data. tracetab.sql file creates the database tables required to run dbms_trace. This file can be found in rdbms/admin folder in oracle home path:

sqlplus /nolog

conn sys/password as sysdba

@/home/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/admin/tracetab.sql

This script create 2 tables and a sequence:

1. plsql_trace_runs

2. plsql_trace_events

3. plsql_trace_runnumber

To complete the initial setup, grant access to public or the user in which one needs to trace the units:

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;

Let’s explore how to use dbms_trace with the help of an example:

Procedure calc_days takes a date as input parameter and returns number of days between this date and current date, along with number of months and years.

Calc_days.sql:

create or replace procedure calc_days (v_date in date)

is

l_num_days number;

begin

l_num_days := trunc(sysdate- v_date  );

dbms_output.put_line('Number of days :' || l_num_days);

dbms_output.put_line('Number of months :' || l_num_days *12/365) ;

dbms_output.put_line('Number of years : ' || l_num_days /365);

end;

Now to make this procedure to be trace enabled, there are two ways:

1. set the session to trace enabled and create the procedure

alter session set set plsql_debug=TRUE;

@calc_days.sql

2. Alter and set debug for the procedure as below:

alter procedure calc_days compile debug;

Now, we can run the procedure and trace its execution. To do this the sets to follow is as mentioned above. First we need to start tracing session by calling dbms_trace.set_plsql_trace.

Set_plsql_trace procedure enables tracing in a session. It takes trace_level as input. There are various constants which can be passed for this procedure viz: trace_all_calls, trace_enabled_calls, trace_all_lines, trace_enabled_lines, trace_all_sql, trace_enabled_sql etc.

After calling set_plsql_trace, one need to run the application code to be traced after which tracing can be stopped by calling clear_plsql_trace procedure.

Example usage:

begin

dbms_trace.set_plsql_trace (dbms_trace.trace_all_lines);

dbms_output.put_line ('Trace started');

calc_days(sysdate-10000);

dbms_output.put_line('To stop trace');

dbms_trace.clear_plsql_trace;

dbms_trace.set_plsql_trace (dbms_trace.trace_enabled_lines);

dbms_output.put_line ('Trace started');

calc_days(sysdate-10000);

dbms_output.put_line('To stop trace');

dbms_trace.clear_plsql_trace;

end;

To analyze trace information, one need to take run_id of each run which can be found from below query:

select runid, run_owner , run_date from plsql_trace_runs order by runid;

Query the table plsql_trace_events table to find the difference among the two traces

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;

Tracing can be done for various levels -

ü  for all calls or only for enabled calls,

ü  for all exceptions or only for enabled exceptions

ü  for all sqls or for enabled sql

ü  for all lines or only for enabled lines.

Controlling Trace:

One can control the trace by pausing the trace when unwanted in a process execution and resume it when needed. This can be achieved by passing constants TRACE_PAUSE and TRACE_RESUME.

Reference:

1. Oracle 11g Advanced application developers guide

2. Oracle 11g PLSQL Packages and types reference 

3. www.oracle-base.com

Regards,

Vijay