Trace a concurrent program

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 setospid 2987

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