Enable Trace for long running Concurrent Request in Apps 11i/R12

Enable Trace for long running Concurrent Request in Apps 11i/R12

This post covers overview of How to 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 1145)

Step 2 : Run below command to find SPID, provide concurrent request ID (1145 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;


REQUEST_ID   SID    SERIAL#             OSUSER                       PROCESS           SPID
—————----------------------------------------------------------------------------
1145              514     28                  applmgr                     17794           2987.
.
Step 3.1 : Check and confirm SPID on Database Node
$ ps-ef | grep 2987
proddb 2987 1 0 13:30:43 ?
0:03 oracledbr12 (LOCAL=NO)

Step 3.2 : Set OSPID (2987 in my case) for ORADEBUG
SQL> oradebug setospid 2987

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12
LEVEL 12 – Both Binds and Waits
LEVEL 8 – Only WAITS
LEVEL 4 – Only BIND Variables

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name
/oracle/apps/proddb/10.2.0/admin/prod_CSDoracle/udump/ prod _ora_2987.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 prod _ora_2987.trc prod _ora_2987.txt explain=apps/<****> sort=(exeela,fchela)
sys=no


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


We can follow the Metalink Note: 296559.1 to know more about tracing.