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


We can follow the Metalink Note: 296559.1 to know more about tracing.

Inventory Organization Access Control

Process:

For each responsibility that needs to access a subset of the available organizations:

1. Navigate to INV / Setup / Organizations / Organization Access
2. Enter the organization code, application name and responsibility name and save
3. Repeat for each organization that will be available to the responsibility.

Points to be remembered:

Organization access works on two parameters, organization and Responsibility.

1) When you add a row in organization access form with respect to an organization and a responsibility, then that organization will ONLY be accessed by that responsibility.
2) You can add multiple responsibilities to access the same organization, and all responsibilities will have access to that organization.
3) Any organization that is NOT assigned to at least one responsibility is available to ALL responsibilities.
4) So users will indirectly have access to organizations based on the responsibilities attached.