Step 1 :
=======

Get Concurrent Request ID of long running concurrent request from Application(fronted).

Navigation : Application >> System administrator>> concurrent>request>

Step 2 :
========

Find session associated with concurrent request .i.e SID

By using the above Query we can get sid,serial#,spid of the concurrent Request..

SELECT a.request_id, d.sid, d.serial# , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = ‘R’;

For Example:
————
Output of above query returned

REQUEST_ID SID SERIAL# SPID
———- — ——- —-
678901 1973 89 1100

Here Conurrent Request id is 678901 & Asoociated Session is 1973.

SPID is the process that Running on DB node Because of This Request..

oracle@acer $ ps -ef|grep -i 1100

oracle1100 1 0 3:30:43 0:03 oracle (LOCAL=NO)

Note : before Using Oradebug make sure that spid shoud exist on DB node..

Step 3 :
========

Enable event 10046 trace with level 12 using oradebug .

Syntax : oradebug setospid SPID

Here SPID is the process id that we are getting from step 2

Coming to My Case

SQL> oradebug setospid 1100
—
Oracle pid: 79, Unix process pid: 1100, image: oracle@Acer
—

Step 3.3 :
==========

Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12

Step 3.4 :
==========

Locate Trace file as
SQL>oradebug tracefile_name

/ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.trc

Wait for 30 Min to get the Trace file to find the Root Cause

Step 4 :
========

Disable trace if u r thinking that trace is enough to find the root cause..

SQL> oradebug event 10046 trace name context off

Step 5 :
========

Convert raw trace To Understandable tracefile By using tkprof/Traceanalyzer

tkprof ‘ /ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.trc’ ’ /ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.txt ’ explain=apps/[apps_passwd] prsela.

Step 6 :
========

Check TKPROF out file to find root cause of slow concurrent request.