Troubleshoot long running concurrent request in Oracle Apps 11i/R12

Step 1 : Check Concurrent Request ID of long running concurrent request from front end

Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)

Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)

Step 4 : Disable trace (once you are happy with trace size)

Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu

Step 6 : Check TKPROF out file to find root cause of slow concurrent request

Step 1 : Check Request ID from Find Concurrent request screen (In my case Request ID is 2355)

Step 2 : Run below command to find SPID, provide concurrent request ID (2355 in my case) when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , 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’;

   REQUEST_ID  SID      SERIAL#    OSUSER       PROCESS    SPID

   —————-

   2355        514         28      applmgr      17794      1633.

Step 3.1 : Check and confirm SPID on Database Node

oravis$ ps-ef | grep 1633

oravis  1633     1  0 13:30:43 ?        0:03 oraclevis11i (LOCAL=NO)

Step 3.2 : Set OSPID (1633 in my case) for ORADEBUG

SQL> oradebug setospid 1633

Oracle pid: 68, Unix process pid: 1633, image: oraclevis11i@onlineappsdba

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

/oracle/apps/vis11idb/10.2.0/admin/oravis_abc/udump/oravis_ora_1633.trc

Wait for 15-20 minutes

Step 4 : Disable trace

SQL> oradebug event 10046 trace name context off

Step 5: Create tkprof file like

tkprof  ‘/oracle/ apps/ vis11idb/ 10.2.0/ admin/ oravis_abc/ udump/ oravis_ora_1633.trc’   ’/oracle/ apps/ vis11idb/ 10.2.0/ admin/oravis_abc/ udump/ tkprof_1633.txt’ explain=apps/[apps_passwd]  fchela …

Step 6 : Check TKPROF file to find root cause of slow concurrent requet