Troubleshoot/Debug Long Running Concurrent Request in Oracle EBS (R12/11i)

This post covers How to troubleshoot long-running concurrent request in Oracle Apps 11i/R12. If you are a new to Concurrent Managers/Requests in EBS R12 then I highly recommend you to check previous post about Concurrent Manager basics here Architecture, Installation, Start/Stop, File System, and Patching in Oracle AppsDBA R 12.2 for AppsDBAs here

Troubleshoot long-running Concurrent Request

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 2355)

Step 2 : Run below command to find SPID, provide concurrent request ID (2355 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 AND a.phase_code = ‘R’;

REQUEST_ID        SID    SERIAL# OSUSER           PROCESS    SPID
—————-
2355        514         28 applmgr                        17794   1633.
. 

Step 3.1 : Check and confirm SPID on Database Node
oraclevis11i@onlineappsdba>ps-ef | grep 1633
 ovis11i  1633     1  0 13:30:43 ?        0:03 oraclevis11i (LOCAL=NO)

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

SQL> oradebug setospid 1633
—
Oracle pid: 68, Unix process pid: 1633, image: oraclevis11i@onlineappsdba
—

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name

/oracle/apps/vis11idb/11.2.0/admin/vis11i_onlineappsdba/udump/vis11i_ora_1633.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  ‘/oracle/ apps/ vis11idb/ 10.2.0/ admin/ vis11i_onlineappsdba/ udump/ vis11i_ora_1633.trc’   ‘/oracle/ apps/ vis11idb/ 10.2.0/ admin/ vis11i_onlineappsdba/ udump/ tkprof_1633.txt’ explain=apps/[apps_passwd]  fchela …

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

References

• Concurrent Processing – Best Practices for Performance for Concurrent Managers in E-Business Suite (Doc ID 1057802.1)
• EBS – Technology Area – Webcast Recording ‘E-Business Suite – Concurrent Manager Performance – Best Practices’ (Doc ID 1367676.1)
• Concurrent Processing – Troubleshooting Concurrent Manager Issues (Unix specific) (Doc ID 104452.1)
• Concurrent Processing – Concurrent Manager Generic Platform Questions and Answers (Doc ID 105133.1)
• EBS Concurrent Processing (CP) Analyzer (Doc ID 1411723.1)
• How to Determine Which Concurrent Manager Ran a Specific Concurrent Request? (Doc ID 343575.1)
• 39817.1  Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output
• 32951.1  Tkprof Interpretation
• ORADEBUG event tracing from dba-oracle
• dbaanswers.blogspot.com script for slow concurrent request

Did you get a chance to download 6 Concurrent Manager CM Must Read Docs For Oracle Apps DBAs EBS R12? If not, then get it now by clicking on the link below.

Are you having any queries or hitting any issues in R12.2 Concurrent Manager (CM)?