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
No comments:
Post a Comment