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

This post covers How to solve long running concurrent request in oracle apps 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

If you are new to Oracle AppsDBA or already working as Apps DBA but on version 11i or R12.1 then I would suggest you first go through below FREE videos from Oracle ACE, Author, and Oracle Apps Expert Atul Kumar

• [Video] Oracle Apps DBA (R12.2) Architecture
• [Video] Oracle Apps DBA (R12.2) Installation: FREE Training
• [Video] File System in Oracle E-Business Suite R12.2: Dual, APPL_TOP, INST_TOP, COMMON_TOP, FMW_HOME
• [Video] Oracle Apps DBA (R12.2) Services: Start/Stop
• [Video] Oracle Apps DBA (R12.2) Patching (ADOP): FREE Training

Troubleshoot long-running Concurrent Request in oracle apps

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

Next Task For You

We cover Oracle E-Business R12.2 Architecture & concepts in our Oracle Apps DBA For Beginners Training along with the Installation, Patching, Cloning, and Troubleshooting and also, Database upgrade to 19c  and much more including the hands-on labs you must perform to upgrade your skills and get a good job with a high package.

Begin your journey towards becoming an Apps DBA by joining our FREE Masterclass on How To Learn Oracle Apps DBA (R12) & It’s New Features.