Tracing a web application in Oracle

In a busy production environment with many active users, tracing a SQL session is time-consuming and complicated,
because processing SQL statements in any multitier system that uses a connection pool can span multiple processes,
or even different instances.

With Oracle Database 10g, Oracle rationalizes SQL tracing through a new built-in package, DBMS_MONITOR,
which encompasses the functionality of previously undocumented trace tools, such as the DBMS_SUPPORT package.
Now you can easily trace any user’s session from beginning to end—from client machine to middle tier to back end—and generate trace
files based on specific client ID, module, or action.

Now you can add your own reference to the trace file for your easy identification,

alter session set
tracefile_identifier =”webapp”;

Trace files generated by this command have the string value with “webapp”

The DBMS_MONITOR package has routines for enabling and disabling statistics aggregation as well as for tracing by session ID,
or tracing based upon a combination of service name, module name, and action name.

PL/SQL developers can embed calls into their applications by using the DBMS_APPLICATION_INFO package to set module and action names

The service name is determined by the connect string used to connect to a service. User sessions not associated with a specific service are handled by sys$users (sys$background is the default service for the background processes).
Since we have a service and a module name, we can turn on tracing for this module as follows:

SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>’test’, module_name=>’w3link’);

PL/SQL procedure successfully completed.

To Disable:

SQL>exec dbms_monitor.serv_mod_act_trace_disable(service_name=>’test’, module_name=>’w3link’);

PL/SQL procedure successfully completed.
Tracing For a particular session:
 
SQL> select sid, serial#, username
from v$session;

   SID     SERIAL#  USERNAME
     ——    ——-  ————
       103       4152  SYS
       107       2418  SYSMAN
       129         53  TEST
       130        561  HR
       141          4  DBSNMP
. . .
       168          1
       169          1
       170          1
15 rows selected.
SQL> exec dbms_monitor.session_trace_enable
(session_id=>129, serial_num=>53, waits=>true, binds=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.session_trace_disable(129);
PL/SQL procedure successfully completed.