Many applications connect to the database using a connection pool, and if you need to run a SQL trace on the application, it is slightly more tricky than a process that uses a single database session.

The problem is that the application can use any one of the many connections in its pool, and depending on the application, each database call could use a different connection. This means you need to trace many connections, which will produce many trace files, and the information you require could be scattered through a lot of them.

If you have the ability to change the application code, it would be possible to enable SQL trace from the application, but often that is not an option.

Marking Sessions To Trace

We can use a logon trigger to mark certain sessions as candidates for tracing. Imagine you have a large web application, with many application servers. Each server has its own connection pool, and you want to trace some web requests on the database. Ideally you would isolate one of the application servers for the testing and make a note of its IP address.

Then create a logon trigger on the database to set the Client ID of any connections on that database to a different value, eg:

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

  if v_ip_address = '192.168.0.123' then
    DBMS_SESSION.SET_IDENTIFIER('TRACE_ME');
  end if;
end;
/

We have not actually enabled tracing here, simply marked the sessions.

When you are ready to run the tests, enable tracing for all the sessions that were marked in the logon trigger:

exec dbms_monitor.CLIENT_ID_TRACE_ENABLE('TRACE_ME');

Disable tracing:

exec dbms_monitor.CLIENT_ID_TRACE_DISABLE('TRACE_ME');

Combining Trace Files

After tracing, it is likely there will be many trace files containing pieces of trace information. Luckily Oracle provides a tool to combine the relevant parts of the trace files, trcsess. In the directory containing the trace files, run:

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

Now you will have a single file containing all the trace information captured for the marked sessions, and can use tkprof as usual:

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