How to enable tracing based on the type of clients connecting to database ?

Our applimation application was having performance issues. The application team engaged the DBAs to find out why the steps involving database operations were taking a long time. The 1st thing I had to do was enable tracing on these sessions to dig more into the problem. However, I noticed that in each step of its execution - the application spawned multiple database sessions. So the traditional

EXECUTE dbms_system.set_sql_trace_in_session (24,13,TRUE); would not be of much use.

I had two options in hand - a) to execute a logon trigger to enable tracing at user level (The application always connect to the database using the same user - lets say 'INFRA_USER').

b) Enable tracing based on
i)  Service, Module, and Action
ii) Client Identifier

I like the second approach.. than dealing with logon triggers.

So here is what I did..

1) First identify the Client ID, service, modules, action etc.


SQL> select sid, username, SERVICE_NAME,CLIENT_IDENTIFIER ,  module, action from v$session where username like 'INFRA_USER%'
  2  ;

   SID USERNAME     SERVICE_NA CLIENT_IDENTIFIER         MODULE             ACTION
------ ------------ ---------- ------------------------- ------------------ ----------
  2192 INFRA_USER   TEST       applmatn:14.296.88.155    JDBC Thin Client


SQL>


I can either enable tracing using the " Service, Module, and Action" or using "Client Identifier". There were several other sessions from different applications having module as "JDBC Thin Client" and SERVICE_NAME = TEST (The SID Name). So to uniquely identify my session - I used the CLIENT_IDENTIFIER instead which is "applmatn:14.296.88.155".


2) Enable tracing on the sessions having client ID applmatn:14.296.88.155

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => 'applmatn:144.226.90.35', waits => TRUE, binds => TRUE);


3) Disable tracing

Once you have sufficient information/or the problamatic query has been run, Disable tracing.

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => 'applmatn:144.226.90.35');


4) Use trcsess utility to consolidate all trace files generated and then use tkprof to analyze the trace file.
More info can be found here