Troubleshoot Long Running Concurrent Request In Apps 11i/R12

Troubleshoot Long Running Concurrent Request In 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


In Detail:


Step 1 : Check Request ID from Find Concurrent request screen
system administrator--> concurrent request --> select phase=Running and status=Normal
uncheck on long running request

(In my case Request ID is 31487467) 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;


O/P:

REQUEST_ID    SID    SERIAL#    OSUSER    PROCESS    SPID   

31487467           249     47495             oracle       12922           20732


Step 3.1 : Check and confirm SPID on Database Node

oracle@testdb trace]$ ps -ef|grep 20732
oracle   20732     1 14 16:03 ?        00:09:29 oracleJUNCLN (LOCAL=NO)
oracle   23967  9608  0 17:10 pts/2    00:00:00 grep 20732


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


SQL> oradebug setospid 20732

Oracle pid: 58, Unix process pid: 20732, image: oracle@testdb.sapac.com.sa

SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.

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

/u01/oracle/JUNCLN/db/tech_st/11.1.0/admin/JUNCLN_testdb/diag/rdbms/juncln/JUNCLN/trace/JUNCLN_ora_20732.trc

Wait for 15-20 minutes


Step 4 : Disable trace

SQL> oradebug event 10046 trace name context off


Step 5: Create tkprof file like


[oracle@testdb trace]$ tkprof JUNCLN_ora_20732.trc JUNCLN_ora_20732_txt explain=apps/******* sort=(exeela,fchela) sys=no


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