We all are aware of tracing the concurrent requests in EBS. I want to share my knowledge on how to trace the Concurrent managers and the internal mechanism of its working
I have installed EBS 12.1.1 and by default it has 3 standard managers defined. Lets get the SID and other details for this Standard manager processes.
SQL> select sid,serial# from v$session where module like '%STANDARD%';
SID SERIAL#
---------- ----------
334 30
354 35
360 318
Now for the 3 respective SIDs, i'll start the trace by executing the below commands.
SQL> exec dbms_monitor.session_trace_enable(334,30);
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.session_trace_enable(354,35);
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.session_trace_enable(360,318);
PL/SQL procedure successfully completed.
Trace is now enabled and whatever action the Concurrent manager performs, it will be captured.
We submit a concurrent program and see how it behaves. Disable the trace once conc request completes.
SQL> exec dbms_monitor.session_trace_disable(334,30);
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.session_trace_disable(354,35);
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.session_trace_disable(360,318);
PL/SQL procedure successfully completed.
The flow will be like the below one.
a. ICM will request the FNDSM(service manager) to start up the CM Processes(FNDLIBR in this case).
b.The manager process connects to the database and reads the settings (e.g profile options, sleep seconds, cache size).
c.The process saves information about itself in FND_CONCURRENT_PROCESSES table (os process id, database name, instance name, DB session identifiers, logfile path and name, and others). It also updates FND_CONCURRENT_QUEUES by increasing the value of RUNNING_PROCESSES.
d.The concurrent manager will read the database to build the sql after reading the FND_CONCURRENT_REQUESTS table. This is the time when manager processes reads the specialization rule.
************************
PARSING IN CURSOR #11 len=1037 dep=0 uid=173 oct=3 lid=173 tim=1463234236389265 hv=776369707 ad='8fbcd350' sqlid='3sc6gm8r4cxjb'
Select R.Rowid From Fnd_Concurrent_Requests R Where R.Hold_Flag = 'N' And R.Status_Code = 'I' And R.Requested_Start_Date <= Sysdate And (R.Node_Name1 is null or (R.Node_Name1 is not null and FND_DCP.target_node_mgr_chk(R.request_id) = 1)) AND EXISTS ( Select Null From Fnd_Concurrent_Programs P Where P.Enabled_Flag = 'Y' And R.Program_Application_Id = P.Application_Id And R.Concurrent_Program_Id = P.Concurrent_Program_Id AND EXISTS ( Select Null From Fnd_Oracle_Userid O Where R.Oracle_Id = O.Oracle_Id AND EXISTS ( Select Null From Fnd_Conflicts_Domain C Where P.Run_Alone_Flag = C.RunAlone_Flag And R.CD_Id = C.CD_Id ) ) And (P.Execution_Method_Code != 'S' OR (R.PROGRAM_APPLICATION_ID,R.CONCURRENT_PROGRAM_ID) IN ((0,98),(0,100),(0,31721),(0,31722),(0,31757))) AND ((R.PROGRAM_APPLICATION_ID,R.CONCURRENT_PROGRAM_ID) NOT IN ((510,40112),(510,40113),(510,41497),(510,41498),(530,41859),(530,41860),(535,41492),(535,41493),(535,41494)))) ORDER BY NVL(R.priority, 999999999), R.Priority_Request_ID, R.Request_ID
END OF STMT
***************************
The SQL will be executed each time for checking the pending requests in the table FND_CONCURRENT_REQUESTS table.
e.Manager processes sleeps if there are no pending requests. The Sleep seconds settings used to determine the time the CM processes will sleep before checking for any pending requests in FND_CONCURRENT_REQUESTS.
Example-In my case sleep second is 30 and there are 3 standard manager processes.
In second 1, manager process 1 will check the fnd_concurrent_requests table.--sleep for 30 seconds
In second 2, manager process 2 will check the fnd_concurrent_requests table.--sleep for 30 seconds.
In second 3, manager process 3 will check the fnd_concurrent_requests table.--sleep for 30 seconds
In second 4-no manager processes
--------
idle time
--------
In second 31, manager process 1 will check the fnd_concurrent_requests table.--sleep for 30 seconds
In second 32, manager process 2 will check the fnd_concurrent_requests table.--sleep for 30 seconds
Idle time is unnecessary and it also proves that we can reduce the sleep seconds or increase the concurrent manager processes.(This comes as part of tuning concurrent managers. I'll take this in a separate post)
f. If it finds that there are any pending requests then the CM process will cache the rowid from FND_CONCURRENT_REQUESTS table. This setting is controlled by Cache Size
For example-
In my case cache size is set to-5
Manager process 1 caches 5 requests (1-5) and start executing request 1.
manager process 2 caches 5 requests(2-6) and start executing request 2
manager process 3 caches 5 requests(3-7) and start executing request 3
In the meantime request 1 is completed and manager process checks for next request in its cache
Manager process checks request 2 and see it is locked by manager process 2.
----------------------------------------------
idle time
----------------------------------------------------------
Manager process checks request 3 and see it is locked by manager process 3
Manager process checks request 4 and and start processing it.
To avoid idle time, we have to adjust the cache size.(Will be covered in the upcoming posts)
g. The cached list of rowids is checked to verify if there are any unprocessed concurrent requests (rows in FND_CONCURRENT_REQUESTS table) left. If none are left – the processing returns to step 5 and the FND_CONCURRENT_REQUESTS table is queried again.
h. From the table FND_CONCURRENT_REQUESTS, the next unprocessed rowid is processed again.
As we know that there can be multiple processes for one Concurrent Manager. Thus how a particular request gets assigned to a given Concurrent manager Processes?
The answer is below.
In our case, there are 3 Standard manager processes and thus internally each manager tries to acquire the resources to process the concurrent program.
One of the manager will pickup the requests and lock the status_code in FND_CONCURRENT_PROCESSES. This is to ensure that one program is executed only once and by only one manager.
*****************************
FOR UPDATE OF R.status_code NoWait
***************************
f. In the mean time other managers will not be able to acquire the resource.There always remains a contention among managers to pick up the requests. If they try to acquire then ORA-00054: resource busy and acquire with NOWAIT specified will be displayed..
References: https://me-dba.com/2016/04/12/internals-of-querying-the-concurrent-requests-queue-revisited-for-r122/
https://www.pythian.com/blog/the-internal-workflow-of-e-business-suite-concurrent-manager-process/
http://ermanarslan.blogspot.in/2016/04/ebs-r12-concurrent-manager-facts-cache.html
*********
Concurrent requests cycle.
Concurrent Request/Program can be under one of four Phases . Each phase has different Status .
Phase : Pending, Running , Completed, Inactive
Status :
Pending – Normal, Standby, Schedules, Waiting
Running – Normal, Paused, Resuming, Terminating
Completed – Normal, Error, Warning, Cancelled, Terminated, Disabled
Inactive – Disabled , On Hold, No Manager
A. Pending Standby – Phase Pending and Status Standby means Program to run request is incompatible with other program(s) currently running.
How to check Incompatible Program/Request for any Program/Request ?
Login with Application Developer responsibility
Click on Concurrent > Program
Query Program
Click on Incompatibilities button
B. Inactive – No Manager
i) No manager is defined to run the request
or
ii) All managers are locked by run-alone requests
or
iii) Concurrent Manager Service is down
or
iv) No concurrent manager process because of workshift
To check Work Shift for any Concurrent Manager From System Administrator responsibility > Concurrent > Manager > Define > Work Shifts > Processes
Phase
Status
Description
PENDING
Normal
Request is waiting for the next available manager.
Standby
Program to run request is incompatible with other program(s) currently running.
Scheduled Request is scheduled to start at a future time or date.
Waiting
A child request is waiting for its Parent request to mark it ready to run. For example, a request in a request set that runs sequentially must wait for a prior request to complete.
RUNNING
Normal
Request is running normally.
Paused
Parent request pauses for all its child requests to finish running. For example, a request set pauses for all requests in the set to complete.
Resuming
All requests submitted by the same parent request have completed running. The Parent request resumes running.
Terminating
Request is terminated by choosing the Cancel Request button in Requests window.
COMPLETED Normal
Request completed successfully.
Error
Request failed to complete successfully.
Warning
Request completed with warnings. For example, a request is generated successfully but fails to print.
Cancelled
Pending or Inactive request is cancelled by choosing the Cancel Request button in the Requests window.
Terminated
Request is terminated by choosing the Cancel Request button in the Requests window.
INACTIVE Disabled
Program to run request is not enabled. Contact your system administrator.
On Hold
Pending request is placed on hold by choosing the Hold Request button in the Requests window.
No Manager
No manager is defined to run the request. Check with your system administrator. A status of No Manager is also given when all managers are locked by run-alone requests.
