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
