I’ve already blogged about tracing concurrent requests here: Tracing a single concurrent request, but I found myself using a bit different approach when needed. The thing is that concurrent managers are setting Module (which usually /not allways/ contains the short name of the concurrent program) and Action (which contains value “Concurrent Request”) information for database sessions executing concurrent requests, therefore it’s easy to switch on tracing from the very begining of execution of the requests for a particular concurrent program using DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (works on DB 10g+).
E.g. to set tracing for the “Workflow Background Process” do it like this:
begin dbms_monitor.serv_mod_act_trace_enable(service_name => 'VISION', module_name => 'FNDWFBG', action_name => 'Concurrent Request', waits => true, binds => true); end;
if you are not sure what are the right service, mdule and action values, you can check an exisiting DB session that runs the concurrent program you need like this:
select service_name, module, action from v$session where sid=&sid;
When this is done, the trace file will be generated for every session having the defined service, module and action names. (If the session changes any of these attributes, the tracing is switched off automatically, if another session sets those atributes, the tracing is started automatically). This behaviour is very usful as it allows to trace the execution from the beginning of the request, just enable tracing before starting the request.
It’s also very important to remember that this setting is not a session level setting, it’s persistant in database until it’s removed. It’s still there even if the database is restarted. Therefore, after you have gathered the information you need, disable the tracing using:
begin dbms_monitor.serv_mod_act_trace_disable(service_name => 'VISION', module_name => 'FNDWFBG', action_name => 'Concurrent Request'); end;
You can always check what kind of tracing is enabled by querying the view DBA_ENABLED_TRACES