SQL Trace usando Connection Pools

Many applications connect to the database using a connection pool, and if it is necessary to run an SQL trace on the application, it is a bit more difficult than a process that uses a single database session.

The problem is that the application can use any of the connections in the pool and depending on the application, each call to the database can use a different connection in the pool. This means that a trace must be generated for each of the connections, which can produce many trace files, causing the information to be analyzed to be generated in a sparse manner.

If you have the ability to change the application code, it may be possible to enable tracing from within the application, but most of the time this is not an option.

Tag the sessions to be trace

A logon trigger can be generated to mark the candidate sessions to generate the trace. Let’s imagine that we have a very large web application which is supported on several application servers. Each server has its own pool of connections and it is desired to generate the traces of the requests generated by the application servers at the database level.

A logon trigger is created in the database to label the customer’s identifier, which will be different

create or replace trigger al_trg_trace_sess
after logon
on database
declare
  v_ip varchar2(30);
begin
  v_ip := SYS_CONTEXT('USERENV','IP_ADDRESS');

  if v_ip = '192.168.0.123' then
    DBMS_SESSION.SET_IDENTIFIER('TRACE_ID');
  end if;
end;

We have not enabled the trace, we only mark the sessions.

When you are ready to run the test, the trace will be enabled for all sessions that were marked with the logon trigger.

exec dbms_monitor.CLIENT_ID_TRACE_ENABLE('TRACE_ID',TRUE, TRUE);

Disable trace

exec dbms_monitor.CLIENT_ID_TRACE_DISABLE('TRACE_ID');

Combine trace files

Once enabled the trace, it is likely to have generated many trace files have the tracking information. Oracle provides a tool to combine relevant information from trace files, trcsess. In the directory where the trace files were generated, run:

trcsess output=combined.trc clientid=TRACE_ID *.trc

Now you have a single file with the trace information for all trace files for sessions that were marked, you can use tkprof in the usual way:

tkprof combined.trc combined.trc.prf record=statements.txt