Trace user(concurrent req) session in oracle(one of the ways)

1. Identify the SID from gv$session and fnd_concurrent req:

SELECT sid,serial# FROM gv$session WHERE paddr LIKE (SELECT addr FROM gv$process WHERE spid=(SELECT oracle_process_id FROM apps.fnd_concurrent_requests WHERE request_id = TO_NUMBER()));


2. Identify the PID of the SID(s) from previous step:

select p.spid from gv$session s, gv$process p where s.sid= and s.paddr=p.addr;


3. Enable Trace using Oradebug.

sqlplus / as sysdba
SQL> oradebug setospid

SQL> oradebug event 10046 trace name context forever, level 12
SQL> oradebug tracefile_name


Leave it for 15-20 (Depends on DBAs decision) mins to fill the trace file.

SQL> oradebug event 10046 trace name context off

4. Take the TKPROF of the trace file genereated in Step 3

tkprof TRACE_FILE_NAME OUTPUT_FILE explain=apps/pass
 
5. Analyze trace file